Linking files using cell contents as filenames...

I was wondering whether it's possible to use a formula or script to use items in cells as references.

For example:
I want to link to a file which is located in:
\\file03\F2008\P6\Region 4\something.xls

However, I will need to change the years, regions and periods to reflect updated details (F2008, P8, Region 1 for example).

I was hoping I could change just one cell (i.e. cell B5 which will say 2008; cell B6 which has 6, and B7 which has 4) so that everythin can remain automated?

hmm this thread might better belong in another forum - if mods see fit, could you please move it? Thanks.

Hi

A couple of thoughts:

1) If the source files can be opened, then you could use INDIRECT to link to the file. However, once the support files are closed, the INDIRECT function does not work.

2) If the items in these cells are unique in the spreadsheet, then you could probably use code to do a find/replace of the 3 items. This would update the existing links. You would also have to supply the existing values for the links to allow this to work.

3) If you have a fixed range of cells for the formulas, and workbooks they have to link to, it would probably be possible to rebuild the formulas based on the data in the source cells. How many link formulas do you have?

4) Where do you think this post should live???

rylo

Hi rylo,
I was intending on using it in a macro to open the files at that location, sort and filter the data, then copy the sheet to another worksheet in the original workbook.

I was thinking something along the lines of 2), but I need that find/replace to edit the macro's links...

Essentially the macro will have fixed filenames, but the path will change.


Otherwise, the I-don't-care method would just be to get the end-user to copy the source files to a fixed folder, then write the macro to open files in that fixed folder...

Hi

How much data are you extracting?
Is is the same small range of cells from a variable number of workbooks/worksheets?
If putting the source workbooks in the same folder as the output file is an option, then opening them all isn't really a problem. It is more getting the data that is the issue.

Can you give more specifics on what you need to extract and where it has to be output?

rylo

How much data? Fair amount that's why your option 1) wouldn't work, I think the machines wouldn't be able to open all of the linked files in one go without running out of memory, or causing Excel to crash

I need to do, multiple times, to grab excerpts of the whole spreadsheet by using filters (an AutoFilter will do fine) out of a SAP generated report. The data will be quite large, and depending on whether I decide to create one report (the big file I'm working within for all the reporting) or split it into the four regions, this could be anything up to 30 or 40 source files. (Probably easier to do it all in one file, because once all the data is loaded, reports can be run at any time for any store)

I also looked into an option of importing into Access and then re-processing and spitting out, but the SAP output spits it out in a poor format which means I can't import or link in Access...