Any way of automating monthly updates?

Hi.

I have file A that have permanent links to multiple other files. However, the locations of file A and these other files will change from month to month.

For example:
Month 1:
Location of file A: 200801\close\PL\FIle A
Location of file B: 200801\close\PL\File B
Location of file C: 200801\close\PL\File C


MOnth 2:
Location of file A: 200802\close\PL\FIle A
Location of file B: 200802\close\PL\File B
Location of file B: 200802\close\PL\File C


Is there any way I could automate this without having to use the edit>link function every month to update the linkages?

Thanks.

Have you considered writing VBA to do the link changes for you? Do you know how to do anything in VBA?

Alternatively you could do Edit/Replace on all of the formulae that link the sheets.

Hi.

Do you have any VBA that I could use? I don't know how to write one but know how to apply one.

Hi.

Every month, I spend a lot of time renaming files stored in directory (example):

W:\200801\Directory1\200801File1.xls
W:\200801\Directory2\200801File2.xls
W:\200801\Directory3\200801File3.xls
and the list goes on.
There are a total of approximately 30 files to rename every month.

Is there a faster way of doing this month on month?

Thanks.

HI.

Anyone?

Thanks.

You could use a find and replace throughout the workbook.

Ctrl+F - type in Directory1\
Select the replace tab - type in Directory2\

And then replace all.

Alternatively, you could edit links.
SELECT Edit - LInks
It brings up a list. Find the file and then Change Source. Find your updated file and it select.

Originally Posted by friend11_6

Hi.

I have file A that have permanent links to multiple other files. However, the locations of file A and these other files will change from month to month.

For example:
Month 1:
Location of file A: 200801\close\PL\FIle A
Location of file B: 200801\close\PL\File B
Location of file C: 200801\close\PL\File C


MOnth 2:
Location of file A: 200802\close\PL\FIle A
Location of file B: 200802\close\PL\File B
Location of file B: 200802\close\PL\File C


Is there any way I could automate this without having to use the edit>link function every month to update the linkages?

Thanks.

If the naming convention is the same and only the month name changes, then as an example, if you store your file (which is linked to the source files) in the directory 200801,
you can copy and paste the file in 200802 directory and Excel automatically changes the links to 200802 (as long as the rest of the directories are exactly the same).

I have mine setup in the exact same way.

R-A