Help with optimising code

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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)
> >>
> >>
> >
> >
>
>