Linking to external workbook

[Steve]

Sorry if you've read this. However, I didn't see the question appear in my
reader.

I have a project that includes parsing a .txt file into Excel. Included in
the fields imported is an EmplID. I need to retrieve data from separate
workbooks based on this. My experiment with Index and Match produced slow
results when linked to one other workbook (about a minute for 4000 records).
I am currently looping using i to increment the row number in the lookup
formula.

Is there a faster method (either not using Index/Match or not looping)?

Steve




Steve

[Tom Ogilvy]

If you open the other workbook, things will probably go a lot faster.

--
Regards,
Tom Ogilvy


"Steve" <No Spam> wrote in message news:[email protected]...
> Sorry if you've read this. However, I didn't see the question appear in my
> reader.
>
> I have a project that includes parsing a .txt file into Excel. Included in
> the fields imported is an EmplID. I need to retrieve data from separate
> workbooks based on this. My experiment with Index and Match produced slow
> results when linked to one other workbook (about a minute for 4000
records).
> I am currently looping using i to increment the row number in the lookup
> formula.
>
> Is there a faster method (either not using Index/Match or not looping)?
>
> Steve
>
>
>
>
> Steve
>
>

[Tom Ogilvy]

Also, you can enter a row oriented formula template in one command

Range("B2:B100").formula =
"=Index([Book1.xls]Sheet1!$A$1:$A$1000,Match(A2,[Book1.xls]Sheet1!$F$1:$F$10
0,0),1)"

as an example

--
Regards,
Tom Ogilvy


"Steve" <No Spam> wrote in message news:[email protected]...
> Sorry if you've read this. However, I didn't see the question appear in my
> reader.
>
> I have a project that includes parsing a .txt file into Excel. Included in
> the fields imported is an EmplID. I need to retrieve data from separate
> workbooks based on this. My experiment with Index and Match produced slow
> results when linked to one other workbook (about a minute for 4000
records).
> I am currently looping using i to increment the row number in the lookup
> formula.
>
> Is there a faster method (either not using Index/Match or not looping)?
>
> Steve
>
>
>
>
> Steve
>
>

[Steve]

Tom
Thank you ... for this and the tip to open the other workbook.

When testing, I noted that writing a loop within a workbook was far quicker
than the code I have looping to an external workbook. Would I be correct in
thinking each time the formula is written during a loop, Excel look for the
external connection, despite calculations and screen updating being off?

Steve


"Tom Ogilvy" <[email protected]> wrote in message
news:Ofq%[email protected]...
> Also, you can enter a row oriented formula template in one command
>
> Range("B2:B100").formula =
> "=Index([Book1.xls]Sheet1!$A$1:$A$1000,Match(A2,[Book1.xls]Sheet1!$F$1:$F$10
> 0,0),1)"
>
> as an example
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Steve" <No Spam> wrote in message news:[email protected]...
>> Sorry if you've read this. However, I didn't see the question appear in
>> my
>> reader.
>>
>> I have a project that includes parsing a .txt file into Excel. Included
>> in
>> the fields imported is an EmplID. I need to retrieve data from separate
>> workbooks based on this. My experiment with Index and Match produced slow
>> results when linked to one other workbook (about a minute for 4000
> records).
>> I am currently looping using i to increment the row number in the lookup
>> formula.
>>
>> Is there a faster method (either not using Index/Match or not looping)?
>>
>> Steve
>>
>>
>>
>>
>> Steve
>>
>>
>
>