Linking files using cell contents as filenames...

**tangcla** · 06-12-2008, 12:21 AM

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?

**tangcla** · 06-12-2008, 12:38 AM

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

Thanks.

**rylo** · 06-12-2008, 01:10 AM

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

**tangcla** · 06-12-2008, 01:21 AM

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

**rylo** · 06-12-2008, 01:37 AM

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

**tangcla** · 06-12-2008, 01:43 AM

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

**rylo** · 06-12-2008, 01:47 AM

Hi

Can you give us some examples? Opening the files, performing the filter, grabbing the data and putting to the output file is sure doable (don't ya just luv the gramma) as long as the output isn't likely to exceed excel's size. If so, then it may have to be put to subsequent sheet(s) but I don't know what that would do to your further processing.

Alternatively, if you want to use access, you could open the files, do the filter then append to the database, all from excel.

rylo

**tangcla** · 06-12-2008, 01:52 AM

Appending the data via Access would be the preferred method, but the output reports from SAP have a blank header row, so it takes all files as numbers, and that doesn't work, because NOT all files are numbers!

Each of the spreadsheets I need to open would probably be about 8MB each. Based on the current manual method, of what the user has run, it shouldn't be too hard to just use the macro method based on static information in a fixed folder.

Oh - I forgot to mention - the report is currently already being used, but manually processed. I'm attempting to automate it as much as possible!

**rylo** · 06-12-2008, 02:04 AM

and some examples are......

**tangcla** · 06-12-2008, 02:07 AM

Originally Posted by rylo

and some examples are......

Something like this attachment. This is a relatively simple example, but Access can't parse these unless I tamper with the source file - which I don't really want to do.

**rylo** · 06-12-2008, 06:43 PM

Hi

Here goes

Create an access database c:\temp\test.mdb
In that database create a table called tbl_test which has the fields
LN: number
SuperiorLevel: text
STORE: number
Description: text
Actual: number

Now open your example file and in a general module enter the code

Please Login or Register  to view this content.

Run this code, and check that the data has been entered into your access table.

If this is running OK, then it can be expanded to cycle through all the spreadsheets in a folder, and append the data to the table.

If you want to clear out the table to start, then this can be done.

See how that goes.

rylo