Excel Macro to Update Links to Another Spreadsheet

jghal

Hi Excel Gurus

Your help would be much appreciated

I currently have two spreadsheets: one which links to data contained on the second spreadsheet.

I often have to send copies of these two spreadsheets to colleagues, but when they save them into their 'My Documents' folder it screws up the links between them both (becuase of username issues etc).

I reckon the way round this is to have a macro run when the first spreadsheet is opened which opens an explorer type window and prompts the user for the location of the 'data' spreadsheet.

Any idea if this is possible - or any better solutions (it is not always the case the the two files will live in the same folder, so changing absolute references to relative references in the links is no good)

Many thanks in advance

:beer:

geewhiz

Is there any reason why they can't be on the same sheet but just different tabs ?

If it has to be on another sheet then add this into the macro of the main sheet:

Filename = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")

This will force the user to open the other sheet that you are linked to and record the path in 'Filename'

jghal

Hi geewhiz

thanks for your reply - the data sheet has lots more information on it that lots more people update. The first (destination) sheet only refers to a small portion of the source so I need to keep them as seperate files.

Whoops your edit beat my post. Thanks again and I'll try out your code

cheers

kevsan

sFileName = Application.GetOpenFilename

This will show a standard file open dialog and put whatever the user selects in the variable sFileName. If they click cancel, sFileName gets filled with "False".

You can then add this variable into the macro to reference the external data.

Is that what you were after?

kevsan

or what geewiz said...

jghal

Hi thanks both,

I can see where these peices of code would be useful, but unfortunately I am no VBA code expert.

Could I impose for a full piece of code to do away with the excel prompts which say "This workbook contains one or more links which cannot be updated..." and which frighten all the other users.

I'm really looking for some code which kicks in before the 'update links' dialogue box, asks where the data is, and updates the file location of the links.

I don't even know if this is possible

:beer:

geewhiz

Don't you have a shared server which everyone has access to so you can leave the files in the same place ?

kevsan

I am not sure that you can disable the prompts...

What i have done in the past is not to link the files, but instead use a macro to open the data file, read the appropriate data, and paste special as the values into the main spreadsheet. This is usually linked to a big red button stating click here to update the data!!

That should give the result, but is not as elegant.

jamalfatty

Assuming your user saves both files in same directory,open the frontend spreadsheet, open the VBA editor, double click on 'ThisWorkbook' and paste the following...

Private Sub Workbook_Open()
DataFilepath = CurDir() & "\data.xls"
Workbooks.Open DataFilepath, UpdateLinks:=1
End Sub

Change "data.xls" to the filename of your data workbook and then save everything.

Your frontend should now get updated with the correct path every time it is opened.

I've thunk this up without opening excel to try it but should work fine.

PhilCo_2

If the files being sent are just copies can't you just copy and paste special values the formulas out of them. That way you'll also know that they've got the correct data rather than hoping the links are working correctly.

rmg1

If you can leave the data file on a shared server that anyone can access, but just protect the file, you can leave the references alone once they have been adjusted to take account of the new data-file location.