Linking to a lot of excel files

[idoav]

Hello all.
I have got some excel work to that will be really tough done manually, but should be really simple done by a macro. The problem is I have no experience working in VBA so even the simple commands aer tough for me. I'd appriciate any help possible.
The problem is that I have 13 different excel files, all in the same template. I want to build a main file, holding links to all the other files data. (e.g. 'c:\f\[1.xls]Sheet1'A* etc. since the data in the 13 files might change in the future.)
I need to take all the 13 files A columns and put a link to them in columns A-M of the new file(each column linking to A column of a different file). then, I need to take all the B columns of those 13 files and put them in N-Z columns of the new files, etc. I have 10 columns in each of the 13 files (columns A-J). All columns are numbers. each column has 20 rows.

Thank you very much for your help,
Ido.

[John_Mc]

Hi Idoav,

i'm sure there is a better way ....but if nothing else presents itself....

You could try simply entering the links for your first column into your master version as normal (hit =, then click in the cell a1 in the file you want to link to) . Then dragging the formulas down to complete the first column.

Copy and paste the first colmn into the next master sheet columns

Then, use the edit menu / find and replace to change the links (formulas) on a large scale.

So find "c/mydocs/file1" and run replace all for "c/mydocs/file2"

You may also change for the column destination (a to b, to c etc)

This will be a bit manual, but easier than phycially entering each spreadsheet, creating the link and copying the range down the column as normal.

As i said, there may be a better way, but this will work.

John Mc

[idoav]

Thank you.
That what I thought to do, but still I feel like there should have been a better simply-coded solution.

[OBP]

idoav, you can definitley do this with some fairly basic code.
It just requires a couple of For/Next loops to copy and paste the data with
ActiveSheet.Paste Link:=True

But you do need to know the exact locations to copy from and paste to and also the exact "paths" of the Excel sheets to open.
It would be best if the Excel sheet names and their locations were held in a sheet in the workbook that is to become your "Master".

[idoav]

It is a good idea to remember the excel names.
As I mentioned before, they are all found in c:\f\1.xls, 2.xls, 3.xls etc. until 13.xls. the interesting sheet in every file is Sheet1.
the main file to copy all the columns in the manner I explained before is called projects1.xls.
Still, I have not succeded in automating the process yet, and I have 3 more times to do it over and over again with manual find-and-replaces..

Ido.

[OBP]

When you 3 more times, is that 3 more worksheets to do or the whle lot 3 more times?
As it will take a while to create the code there is not much point in doing it if you can finish what you are doing in same time.

[idoav]

I have got the whole thing to do 3 more times. I will be very grateful for a code doing it, since I believe it will also come to me in the future.
Thank you for your time anyway!

Ido.

[OBP]

idoav, I will have a look at this and post a workbook, I will not be able to test it with your file path but that should not be a problem for you to change it to suit yours.

[OBP]

idoav, the attached workbook does what you want (I hope).
It currently works in a folder on Drive C called "Project".
So I would suggest for you to test it you copy this Workbook and your 13 workbooks in to a newly created Folder on your drive C: called "Project".
If it works Ok then if you need it I will instruct you on how to change the path to your current location.
The VBA is simple and in nice easy blocks for each Column.
It is not particulary fast, it takes about 46 seconds to do the transfers, I exect it could be made faster, but at least it will be quicker than doing it manually.
The VB is currently on a Command button but you can always copy it to a Macro.

Sorry, I have just realised that I used Paste instead of Paste Link, I will post a new version.

[OBP]

This version has the Paste Link.

[OBP]

I will post you a new Workbook

[Cyberdude]

I haven't examined what you are doing, but I do a LOT of linking and I've found it very advantageous to make use of defined names for the cells I am linking to. There doesn't seem to be any limit to how many names you create, but it takes some time to do the initial definitions. You can also defines names for arrays. Just a suggestion.

[OBP]

Hi Cyberdude, the Workbook that I posted for Idoav uses VBA to copy and paste the data.
This version is just an update with the filenames contained on the worksheet.

Idoav, here it is.

[idoav]

Hey OBP!
That's exactly what I wanted! It will be a very useful macro for me, and I now think I can also make all the neccessary adjustments in it for future uses. Thank you so very much for the all the help!!

Ido.