| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace file name many times
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to replace file name many times
See this page
http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "msdrolf" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to replace file name many times
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "msdrolf" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to replace file name many times
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" wrote in message ... See this page http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "msdrolf" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to replace file name many times
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to replace file name many times
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A macro that counts the number of times a file is opened | Excel Discussion (Misc queries) | |||
| SaveAs macro: How do I specify "Replace existing file?" | Excel Programming | |||
| within a macro how can I suppress the warning pop "A file named xxxx.xls already exists in this location. Do you want to replace it?" | Excel Programming | |||
| macro to export files but not replace an exsisting file name | Excel Programming | |||
| Search and replace 9000 times? | Excel Programming | |||
Linear Mode
