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" wrote:

> 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

[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" wrote:

> This solution requires that the CSV file be open
>
> =INDIRECT("daily_data_"&R1&".cvs")
>
>
> "RichT" wrote:
>
> > 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]

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.


"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??
>
> Thanks
>
> "Duke Carey" wrote:
>
> > This solution requires that the CSV file be open
> >
> > =INDIRECT("daily_data_"&R1&".cvs")
> >
> >
> > "RichT" wrote:
> >
> > > 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

[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.

[RichT]

Thanks -- was afraid the .csv file format was the problem. Actually it is
356/6 files time number of locations (currently at 4 and growing) - hence the
desire not to have to open the files. Looks like it's back to the drawing
board. Again thanks for the assist.

"Harlan Grove" wrote:

> 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.
>
>