Importing specific cells from multiple workbooks in daily folders

[jasonmcasey]

Hi

I have got 9 spreadsheets which are created each day by someone else that detail how many types of each consignment we have for delivery that day for a specific customer.

I have to go in each day and total up the figures from all 9 spreadsheets. I would like to try and automate this process as much as possible.

The spreadsheet layout is always the same and so is the file name. The only bit that changes is the folder.

Is it possible that something can be written in VBA where a user can specify the folder name and then insert the rest as standard? E.g. insert a function like ='\\Network location\(user specified folder name)\[Filename.xls]Sheet1'!$B$17

Apologies for any typos, I've sent this from my phone

Regards
Jason

[nathansav]

I'd take a look at the links, so link the cells into one file you know, but recording a macro, then pick the code produced apart, and change to allow the file location to come from a cell.

[jasonmcasey]

Hi

Thanks for your hint on a potential solution. I have found quite a user friendly solution which has worked well for me.

All of the cells that I need to link are set up as \\network location\(daily folder name)\[File names 1-9.xls]Sheet1!$A$1 for example.

I found that I could specify the current folder name in one cell, add the new folder name in another cell, and run a vba macro to find and replace the old folder name with the new folder name. That then updates all of the references in all of the cells and helpfully updates the old folder name cell as well. All I have to do is put the folder name in that I want to update to, run the macro and it gets all the data, then keep adding folder names that I want to update etc.