Help with optimising code

[FrigidDigit]

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)

 

[Tom Ogilvy]

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.

 

[FrigidDigit]

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

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

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)

 

[Tom Ogilvy]

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

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

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

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)