External Reference Using Relative Paths

Hi,

I have a problem with using the Microsoft Linked objects from within MS Word referencing source data from various MS Excel work books where link paths are broken when the file is either moved or resaved. Perhaps one of you have had similar experiences and could help to resolve my situation.

My scenario works like this:

- I create a MS Word reporting document
- I reference many graphs and tables from MS Excel by using the Paste Special option
- The MS Excel objects are linked, allowing automatic updates and immediate roundtrip editing

This is how I intended the setup to function.

But I am finding that when I move either of the files, the links then break, turning the MS Excel embedded objects into images. Their reference to the source MS Excel file is broken.

Also if I move the files together to another directory, again the links are broken.

Is there an automated process where I can repair these links in such circumstances?

Or

Is there an option in MS Word where I can use relative link paths to MS Excel objects instead of the absolute link paths that it seems to use?

Your comments appreciated.

I am using Excel and Word 2002.

Thanks.

I know how to import external data from a .csv, but how can I have the path
be relative so I can move the directory around without having to update the
..csv file location manually?

Hello,

From what I've read so far, it's not possible to make an external reference relative. What I would like is for a cell in [WorkBook1]Sheet1 to have the same value as a cell in [WorkBook2]Sheet1, and for any modification in [WorkBook1]Sheet1 to be reflected automatically in [WorkBook2]Sheet1. This is easy enough to do as long as you never (EVER!) move the files, using external references (a.k.a. links). I need to move the files around, although I would always keep the folder structure around my cross-referenced files the same. Relative paths would be great, but MS doesn't seem to think that's important.

Here's what I tried to do to outsmart Excel. I made cell A1 of the the sheet with all the x-refs contain the path of the file it is in (result: 'M:\Research\Thesis\DCB Tests\DCB Data\DataReduction\). I then made a few cells that contained the names of the x-referenced files in the appropriate format (e.g. in cell A2: [C02-04_AC_DataReduction_IIWN.xlsx]Report'!). Then, in the spot that I wanted the x-ref, I used the INDIRECT function: =INDIRECT(A1&A2&"F34") to refer to in 'M:\Research\Thesis\DCB Tests\DCB Data\DataReduction\C02-04_AC_DataReduction_IIWN.xlsx]Report'!F34.

For some reason, this only works when the file I'm referring to is open, even though it now has a full-path reference which shouldn't need it to be open. Any ideas (I know I could write a macro that would do this for me, but didn't think I would need one)?

I am creating a set of workbooks that have links to one another, which
together represent one month's data. There are 2 'levels' of active
workbooks that change rather often and a few reference workbooks of data used
by the other active spreedsheets, data which does not change often but if it
does the changes need to be reflected by all the active books. A final
summary Workbook containing only formulas and links pulls data from the
active books and shows the current combined efforts of the organization for
the month. The active books are updated daily and have many links to the
reference workbook, and some links to one another, while the summary workbook
with locked formulas and references is extensively linked to the active books.

All of these are in the same project folder, for example August 06. I would
like, when done, to create another complete set for the next project in
September. All of the workbooks will have the exact same names. but will be
in a new subfolder, called September 06. Because of this I was hoping to
have all path references in the external links use variable references such
as:

='.\[North Section.xls]Collected Data'!C10:C25

Instead of

='C:\Reports\August 06\[North Section.xls]Collected Data'!C10:C25

Unfortunately you cannot seem to put .\ in the path of an external link,
only absolute paths seem to be accepted. Is this correct or am I missing
something? IF this is correct, is there some way to include a fuction that
will build the path to the currect folder and then use that path for the
external links?

Any help welcomed!

I have a spreadsheet that has enternal links and prompts me to update it upon opening it, but I can't find what the links are.

I have already done the following:
Ctrl + F and deleted all external links in formulas.
Searched for external links in the Define Name, in the 'Refers To' section, to see if any of the name ranges refer to another external source.

Is there another way that I can get rid of these external links, or at least find where are these links?

thanks in advance.