Creating Linked Workbook file name using cell variables

[RichT]

Anyone know of a way to create a linked workbook file name using cell
variables.

I have a workbook that will pull in data from a .cvs file generated daily.
The date (yyyymmdd) included in the file name changes each day. Was looking
for a way to grab the date from the master spreadsheet header and build the
..csv file name. Was hoping to not have to manually create a new file name
for each day of the month. Something along the lines "daily_data_(cell
R1).cvs" == daily_data_20050101.cvs

Thanks

RichT

 

[Duke Carey]

This solution requires that the CSV file be open

=INDIRECT("daily_data_"&R1&".cvs")

 

[RichT]

Looked into using INDIRECT, but with 365 .csv files (1 for each day) that is
not going to work. Is there any way to do this without having to open the
..csv file first??

Thanks

 

[Duke Carey]

Search this newsgroup for INDIRECT.EXT, a function written by one of the MVPs
here, I think. I know little about it or whether it will do the trick, but
understand that it's supposed to work on closed files. There may also be one
called PULL, and those are my only other ideas for you.

 

[Harlan Grove]

RichT wrote...

Looked into using INDIRECT, but with 365 .csv files (1 for each day) that is
not going to work. Is there any way to do this without having to open the
.csv file first??

Is it really so difficult to test?

It's not possible to use explicit/hardcoded external reference links
into closed CSV files, e.g., formulas like

='x:\y\[z.csv]z'!AB321

If explicit references won't work, derived references have no chance at
all.

Your *ONLY* choices are:
1. open all 365 (366 in leap years) CSV files, like it or not;
2. write a macro to read through these CSV files in sequence, storing
desired values in worksheet cells;
3. use something other than Excel to do the data extraction into a
summary CSV
file, and open/import that in/into Excel.

Actually, there's another option: convert all these CSV files to XLS
files. There are techniques for extracting data from closed *XLS* files.