Open worksheet and update links

[Dave]

I use a workbook (main.xls) that has a named range on a worksheet in another
workbook (parts.xls). If I do not open parts.xls first then main.xls it
needs to update the links, I need to click on "open source". Is there a
macro that will run when I open main.xls and automatically open parts.xls
and update the links?

Thanks
Dave

 

[Arvi Laanemets]

Hi

Create a mirror of source table in main.xls (it can be on hidden sheet):
1. Create a sheet Parts in main.xls
2. Into sell A1 on sheet Parts enter formula like
=IF('C:\My Documents\[Parts.xls]Sheet1'!A1="","",'C:\My
Documents\[Parts.xls]Sheet1'!A1)
3. Copy the formula in A1 into range with same width as parts source table,
and at least same height. But it'll be wise to have some (hundreds,
thousands?) spare rows at bottom.

Now you can define your named range based on sheet Parts in main.xls.

By such design, when you open main.xls, you are asked also do you want to
update links, but you have only click Yes or No. Nou need to open source
anymore, as links like you used on sheet Parts don't need the source to be
opened at all.