Update links to closed spreadsheet

[carlossaltz]

I have an excel spreadsheet with formulas linked to data in another spreadsheet. The spreadsheet with the source data is not opened. When I press the Update Link button, the cells with the formulas linked to the closed spreadsheet returns a #VALUE# indicator. I have to open the source spreadsheet so that the Update Link will return the correct data. Why doesn't Excel extract the data from the source spreadsheet when it is closed?

[MDubbelboer]

too many links could exist


you could try to write a macro to open the related spreadsheet when the first spreadsheet is opened. once the links have been updated it can close the related spreadsheet

something like this in the "This workbook tab"
Private Sub Workbook_Open()
Workbooks.Open ("c:\temp1.xls")
Workbooks("temp1.xls").Close
End Sub


where c:\temp1.xls is the file you want to open automatically

[carlossaltz]

Am I supposed to include the source workbook reference within quotation marks or without the quotation marks?

[MDubbelboer]

with quotes. make sure you have the full path in the first instance, and make sure you have the .xls in the second instance.