Macro to replace file name many times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a formula which calls data from another spreadsheet:

='[Vac 0309.xls]Sheet1'!$D$2

The formula is repeated many times starting at cell B1. Is there a macro
which will replace the file name with the file name in cell A1 then A2, A3
etc. until there are no more files names listed in column A. The purpose is
to pull data from about 200 separate spreadsheets. The data is always in the
same sheet and cell.
 
No need for a macro; simple formula works
With A1 having the text: Vac 0309
The formula =INDIRECT("'["&A1&".XLS]Sheet1'!E9") will work in B1
But if you are copying it then use
=INDIRECT("'["&A1&".XLS]Sheet1'!$E$1")

Note after the open parenthesis we have <double-quote><single-quote>

best wishes
 
etc. until there are no more files names listed in column A
Sorry I not read your question good

But maybe the macro is useful

--
Regards Ron de Bruin
http://www.rondebruin.nl



Ron de Bruin said:
See this page
http://www.rondebruin.nl/summary2.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



msdrolf said:
I have a formula which calls data from another spreadsheet:

='[Vac 0309.xls]Sheet1'!$D$2

The formula is repeated many times starting at cell B1. Is there a macro
which will replace the file name with the file name in cell A1 then A2, A3
etc. until there are no more files names listed in column A. The purpose is
to pull data from about 200 separate spreadsheets. The data is always in the
same sheet and cell.
Click to expand...
Click to expand...
 
Ron,
Thanks for the link. My macro knowledge is still developing but it looks
like your macro will do what I want to do and then some.
 
IT WORKS! Many thanks, Bernard

One amendment. I found that when I saved/closed the input worksheet the
formula returned an error. However, if the path is entered between the
apostrophe and the first [ bracket then it works even after closing the input
worksheet.

Rolf
 
Back
Top