Hi all,

I am using Ron de Bruin's code to extract cell values from workbooks via
ADO. It works perfectly apart from the fact that because I need to extract
several single, non-contigious cells, I need to call the GetData function 6
times for each work book. Is there any way to combine these 6 statements
into 1 or at least reduce them?

Any help would be appreciated.

FD


Call GetData(fname, SheetName, "A10:A10",
Sheets("Invoice Listing").Cells(eRow, 1), False)
Call GetData(fname, SheetName, "I11:I11",
Sheets("Invoice Listing").Cells(eRow, 2), False)
Call GetData(fname, SheetName, "I12:I12",
Sheets("Invoice Listing").Cells(eRow, 3), False)
Call GetData(fname, SheetName, "I13:I13",
Sheets("Invoice Listing").Cells(eRow, 4), False)
Call GetData(fname, SheetName, "I14:I14",
Sheets("Invoice Listing").Cells(eRow, 5), False)
Call GetData(fname, SheetName, "G65:G65",
Sheets("Invoice Listing").Cells(eRow, 6), False)

If you want fast, use linking formulas

Sub NewGetData(fName As String, SheetName As String, _
Rnge As String, Location As Range, bBool As Boolean)
Dim fName1 As String, fName2 As String
Dim sStr As String
fName1 = Left(fName, InStrRev(fName, "\"))
fName2 = "[" & Right(fName, Len(fName) - Len(fName1)) & "]"
sStr = "='" & fName1 & fName2 & SheetName & "'!" & Rnge
rng.Formula = sStr
rng.Formula = rng.Value
End Sub

Sub Test()
Dim fName As String
Dim SheetName As String
Dim Rnge As String
Dim rng As Range
fName = "C:\Myfolder\MyBook.xls"
SheetName = "Sheet 1"
Rnge = "A1:A1"
Set rng = Worksheets("Sheet2").Range("B9")
NewGetData fName, SheetName, Rnge, rng, False
End Sub

so you would just have to change your GetData call to NewGetData or rename
the sub.

I think you will find this significantly faster.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
Hi all,

I am using Ron de Bruin's code to extract cell values from workbooks via
ADO. It works perfectly apart from the fact that because I need to
extract
several single, non-contigious cells, I need to call the GetData function
6
times for each work book. Is there any way to combine these 6 statements
into 1 or at least reduce them?

Any help would be appreciated.

FD


Call GetData(fname, SheetName, "A10:A10",
Sheets("Invoice Listing").Cells(eRow, 1), False)
Call GetData(fname, SheetName, "I11:I11",
Sheets("Invoice Listing").Cells(eRow, 2), False)
Call GetData(fname, SheetName, "I12:I12",
Sheets("Invoice Listing").Cells(eRow, 3), False)
Call GetData(fname, SheetName, "I13:I13",
Sheets("Invoice Listing").Cells(eRow, 4), False)
Call GetData(fname, SheetName, "I14:I14",
Sheets("Invoice Listing").Cells(eRow, 5), False)
Call GetData(fname, SheetName, "G65:G65",
Sheets("Invoice Listing").Cells(eRow, 6), False)

Thanks for the response Tom.

I am trying to understand the code you gave me properly.
Is the following correct?:

I am linking cells in my master workbook to individual cells in each of the
workbooks in the specified folder.
So I am basically entering link formulas via VBA?
The source workbooks don't need to be opened to update the master workbook
as links are updated when the master workbook is opened?

Thanks for being so patient with me.
FD

"Tom Ogilvy" wrote in message
...
If you want fast, use linking formulas

Sub NewGetData(fName As String, SheetName As String, _
Rnge As String, Location As Range, bBool As Boolean)
Dim fName1 As String, fName2 As String
Dim sStr As String
fName1 = Left(fName, InStrRev(fName, "\"))
fName2 = "[" & Right(fName, Len(fName) - Len(fName1)) & "]"
sStr = "='" & fName1 & fName2 & SheetName & "'!" & Rnge
rng.Formula = sStr
rng.Formula = rng.Value
End Sub

Sub Test()
Dim fName As String
Dim SheetName As String
Dim Rnge As String
Dim rng As Range
fName = "C:\Myfolder\MyBook.xls"
SheetName = "Sheet 1"
Rnge = "A1:A1"
Set rng = Worksheets("Sheet2").Range("B9")
NewGetData fName, SheetName, Rnge, rng, False
End Sub

so you would just have to change your GetData call to NewGetData or rename
the sub.

I think you will find this significantly faster.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
Hi all,

I am using Ron de Bruin's code to extract cell values from workbooks via
ADO. It works perfectly apart from the fact that because I need to
extract
several single, non-contigious cells, I need to call the GetData function
6
times for each work book. Is there any way to combine these 6 statements
into 1 or at least reduce them?

Any help would be appreciated.

FD


Call GetData(fname, SheetName, "A10:A10",
Sheets("Invoice Listing").Cells(eRow, 1), False)
Call GetData(fname, SheetName, "I11:I11",
Sheets("Invoice Listing").Cells(eRow, 2), False)
Call GetData(fname, SheetName, "I12:I12",
Sheets("Invoice Listing").Cells(eRow, 3), False)
Call GetData(fname, SheetName, "I13:I13",
Sheets("Invoice Listing").Cells(eRow, 4), False)
Call GetData(fname, SheetName, "I14:I14",
Sheets("Invoice Listing").Cells(eRow, 5), False)
Call GetData(fname, SheetName, "G65:G65",
Sheets("Invoice Listing").Cells(eRow, 6), False)

What you say is correct. The formulas are then replaced with the values
they produced, so the link is then broken. (Obviously you could remove that
part if you want to retain the links).

--
Regards,
Tom Ogilvy




"FrigidDigit" wrote in message
...
Thanks for the response Tom.

I am trying to understand the code you gave me properly.
Is the following correct?:

I am linking cells in my master workbook to individual cells in each of
the
workbooks in the specified folder.
So I am basically entering link formulas via VBA?
The source workbooks don't need to be opened to update the master workbook
as links are updated when the master workbook is opened?

Thanks for being so patient with me.
FD

"Tom Ogilvy" wrote in message
...
If you want fast, use linking formulas

Sub NewGetData(fName As String, SheetName As String, _
Rnge As String, Location As Range, bBool As Boolean)
Dim fName1 As String, fName2 As String
Dim sStr As String
fName1 = Left(fName, InStrRev(fName, "\"))
fName2 = "[" & Right(fName, Len(fName) - Len(fName1)) & "]"
sStr = "='" & fName1 & fName2 & SheetName & "'!" & Rnge
rng.Formula = sStr
rng.Formula = rng.Value
End Sub

Sub Test()
Dim fName As String
Dim SheetName As String
Dim Rnge As String
Dim rng As Range
fName = "C:\Myfolder\MyBook.xls"
SheetName = "Sheet 1"
Rnge = "A1:A1"
Set rng = Worksheets("Sheet2").Range("B9")
NewGetData fName, SheetName, Rnge, rng, False
End Sub

so you would just have to change your GetData call to NewGetData or
rename
the sub.

I think you will find this significantly faster.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
Hi all,

I am using Ron de Bruin's code to extract cell values from workbooks
via
ADO. It works perfectly apart from the fact that because I need to
extract
several single, non-contigious cells, I need to call the GetData
function
6
times for each work book. Is there any way to combine these 6
statements
into 1 or at least reduce them?

Any help would be appreciated.

FD


Call GetData(fname, SheetName, "A10:A10",
Sheets("Invoice Listing").Cells(eRow, 1), False)
Call GetData(fname, SheetName, "I11:I11",
Sheets("Invoice Listing").Cells(eRow, 2), False)
Call GetData(fname, SheetName, "I12:I12",
Sheets("Invoice Listing").Cells(eRow, 3), False)
Call GetData(fname, SheetName, "I13:I13",
Sheets("Invoice Listing").Cells(eRow, 4), False)
Call GetData(fname, SheetName, "I14:I14",
Sheets("Invoice Listing").Cells(eRow, 5), False)
Call GetData(fname, SheetName, "G65:G65",
Sheets("Invoice Listing").Cells(eRow, 6), False)