External Reference Using Relative Paths

I was wondering if I could set up my external references to Microsoft Access using relative paths rather than absolute paths.

Basically, I'd like to be able to move my files around and have the links still work. Otherwise, I have to go to each PivotTable with external links and update the link. It's a pain.

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Absolute and Relative Cell References in Excel
In this tutorial I am going to cover the difference between Absolute and Relative Cell References in Excel and show ...
Reference Other Excel Files with Formulas and Functions
In Excel you can use formulas and functions to reference data that is stored in another Excel workbook.  This creat ...
Apply Conditional Formatting to Multiple Cells with a Single Formula
How to use a single formula to apply conditional formatting to multiple cells at once in Excel. This saves you the ...
Prevent Duplicate Values in Excel
I show you how to prevent duplicate values being entered into Excel using Data Validation. Let's say we have a lis ...

Helpful Excel Macros

Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Remove (Delete) Hyperlinks from Worksheets
- This macro will remove all hyperlinks from the active worksheet. It will delete the hyperlinks but it will not delete t
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe

Similar Topics


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?


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.


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?

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

='.\[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 bunch of excel files and they all link to 3 other excel files on sharepoint. The downside is we have maybe 4-5 people who access these files. For some reason and I dont know who/what is causing it, but the links to the external files sometimes get updated by excel. When excel changes these links, it changes them from the http sharepoint to C:\ instead which causes issues since that is not where the files exist.

I am guessing maybe someone is occasionally saving the files on their hard drive which is causing all the external links to swap to C:. Is there any way to stop these links from automatically changing like this? These files are linked in formulas and/or buttons linked to macros on another file.

I have an issue with hyperlinks in Excel 2000. I am trying
to create hyperlinks to documents/XLS files on a our network, and want to use
absolute paths. I can "point" the hyperlink to the document, click on the
link and it works fine, with the address showing as an absolute path
(\\server\folderA\folderB\doc.xls). When I save the document the link
changes from the absolute path to a relative path (../../../ doc.xls). This
creates a problem because when others try using the hyperlinks, they do not
work (office clients range from 2000 to 2003). Help.

I have tried several ways to make my external data path relative rather than absolute without success. Does anybody know a way to do this i.e. I have used the Data, Import External Data route but when I try to refrsh the data on another users PC (where he has access to the same drive but it is mapped to another drive letter) the refresh will not work.

I'm sure I'm misssing something very simple but it is driving me mad and I'm going on holiday in 1 hours time and would really like to fix this before I go!

Googling this question turned up a lot of people with the *opposite* problem, so
I'm convinced I must be overlooking something obvious. The issue arises on 2
different PCs, running Excel 2000 and 2003, both with WinXP.

I have several hundred files to be hyperlinked from a spreadsheet. When done
they're all destined for a **. So I figure we need all the hyperlink paths to
be relative, that is, the address field for each link should be in the form
\mystuff.txt and not E:\mystuff.txt (nor any other letter plus :\mystuff.txt)..

But no matter what I put in the 'Hyperlink Base' box in File-> Properties->
Summary, when I pick a cell in the sheet, click Insert-> Hyperlink, and BROWSE
to a file to be linked, that file's full ABSOLUTE path gets inserted.

Is there a way to disable that? I see tons of posts saying you can *type* the
file's relative path, but oddly enough, nobody mentions how convenient it would
be if you could make the Browse button automatically INSERT said relative path.
This is why I'm convinced something obvious has escaped me. Is there no way
to make it do that?

I know I can de-absolute all the links at once afterward with a little macro
that cycles through them & applies the VBA Replace function, but that seems like
sort of a back-assward way to "fix" it. Am I crazy? (Shhhhh!)

If the answer is bonehead simple, I promise to slink quietly back into my cave.


Mark Tangard
"Life is nothing if you're not obsessed." --John Waters

I have a spreadsheet that contains external links to other workbooks. Using the [Edit][Links] I'm able to identify the link, however I am unable to locate that link within the workbook. What is/are the preferred methods for locating links within a workbook.
It might be nice if the Links window would state the cell that contains the link.

I want to put a button on my sheet which will update all external links.

The links are files EX1 to EX10 and will not change. All i need is the code which updates these links via vba.



I have a few workbooks that are linked to a workbook that I recieve from an external source. I save the external workbook daily as the same file, in effect saving over it. The workbook I recieve apparently has some sort of macros because it asks whether to allow them, but I can see nothing in the vbe.
Whenever I open one of the workbooks that are linked to this external workbook, I choose to update links. However, a dialog pops up saying links could not be updated, Continue or Edit Links. When I choose Edit Links, the external book is listed but under Status it says Warning: Source not recalculated. If I hit Update Values, another pop up comes up saying: Links to ...xls were not updated because ...xls was not recalculated before it was last saved.

The only way to update my links is to open the external workbook.
What is causing this problem, and how do I get around it?



I've seen similar issues to this one, but haven't come across an exact answer yet. I have a workbook on a share. Let's say it's called \\myshare\myfolder. In many formulas, there are links to a couple of other files, \\myshare\anotherfolder\anotherfile, for example.

If I use Explorer to copy the Excel file from the share and place on my desktop, and then open the file, all the formulas change from \\myshare\anotherfolder\another file to c:\anotherfolder\anotherfile.

The issue is that I would like to preserve the links to the fileshare even though I am copying the primary file to my desktop.

I've searched the forums and others have said that Microsoft keeps relative references, by design, to other files.

So, my question is: is there a way to override this? There are only several external files that my main file is linked to, but these links are in many cells. I have tried to use the Edit Links dialogue box to change the source file from "the bad one" on my desktop to the one on the server. But, since there are many links, Excel hangs, for all practical purposes.

My ideal solution would be to avoid having the links updated in the first place. I figured that if this was successfully solved, it wold be in this forum.


I have a macro that reads in an array of Excel files then processes each file before moving onto the next with the following code to select the array and open it:

Pth = Application.GetOpenFilename("excel files, *.xls", MultiSelect:=True)

For i = LBound(Pth) To UBound(Pth)
On Error Resume Next
If Pth "False" Then Workbooks.Open Pth(i)

Acct = ActiveWorkbook.Name

The problem is that many of the files contained in the Pth(i) array have links to external data sources which pauses the macro with the pop-up "update links" message. Is it possible to insert code that will tell the macro to automatically NOT update links in these external files? I know that Application.DisplayAlerts = False does this, but this seems to only work for the workbook running the macro and not these external files being read in. Any help is appreciated.

I have an Excel document that links to several others. I want some links to
update automatically but some to be manual. (I will still have the prompt to
update links on opening).

I don't have the option in Edit Links to select Manual - it is greyed out.
I have tried various combinations of Update Remote References and Save
External Link Values with Calculation Manual and Automatic.

What am I doing wrong? I do not know where else to look.


I have a file on excel 2007 that is linked to 3 external files , I did not make any reference to any of these files and know i cannot not break the links.
This is causing huge problems because when my co-workers open the file there excel crashes. How can i break these links going to edit links break link does not get rid of them.

any help is appreciated.

My excel file has a very large number of external links to a large number of different files. Frequently while working on this file, the cells with the external links will lose their data and return a #value error, a #ref error, or a zero in place of the correct values. I can fix the problem by opening the source files, but once all the source files are open my system runs very slow. Do you know why I'm losing this data in cells with external links and how I can fix it? Thanks for any help you can give.

I've noticed that when doing a paste link of a single cell, the resulting formula has absolute references. However, when doing a paste link of a range of cells the resulting formulas have relative references. I would like to maintain the abosolute references or avoid the automatic formula update of the relative reference so that my original formula's reference remains intact.

I have seen some solutions involving a brute force method of changing the original formulas to use absolute references but I'd like to avoid this. Are there any other ideas out there?

Thanks much for your help,

Using VBA I need to open a number of files, do what ever I need to do (i.e. update a formula) and close it again.

This all works well, but I would like to be able to decide if the workbook I am opening should update external links or not. This should be done without being shown the dialogue box.

The files are opened using:

Workbooks.Open Filename:=arrFiles(i)

I expect that I can add an other argument but am uncertain how.


Within Excel there is an issue with copying data with forumulas where the referenced cell has more than 255 characters. The following link has a solution (http://support.microsoft.com/kb/213841/), but I need to copy the data from an external reference that has more than 255 characters. I would like to incorporate this when the Update message appears at startup, but I cannot find how to link into that update.

Example: Workbook A references Workbook B via formula and external reference. The cell in Workbook B has more than 255 characters. Excel will truncate this to 255 in Workbook A (not desired).


Every time I move one sheet to a new book, the check boxes of "Save external links values" and "Update links to other documents" in the new book are checked. How can I make the default of a new book for these values to be unchecked. The size of the files differs very much, 72 kb when not checked and 1.2 mb when checked.

I am working on a report that requires data from several queries in an associated Access database. I have no trouble importing the external data and am very happy with the results. The problem lies in the fact that I can't remove the links to the external data. I am still in the process of formatting the report and find the need to remove an import query link from one sheet to another or just move it to a different place on the same sheet but cannot seem to get rid of it. I have tried 'Clear Contents', deleting rows and shifting up empty ones, pasting on top of it. Nothing seems to work. I hope that I am just overlooking something simple. Can anyone help?


I am using this formula instead of a linked external spreadsheet:

=INDIRECT("'[" & $A$1 &" "& $A9&".xlsx]Cost Summary'!" & C$1 & "$" &$B$1)

The values in A1 and A9 contain values that change that help build the name of the spreadsheet. If the external reference is open, it auto-updates. However if it is not open, the formula evaluates to "#REF!". Is there a way to have the formula NOT calculate unless it returns no error? Thanks for the help!

The reason I am doing this is to avoid having to update the external links each month as the file names do change monthly.

I have a formula that I would like move external references from a shared drive to SharePoint. Can external references located on a SharePoint site be called within an Excel formula?



I want to set up a workbook with several values from linked workbooks. I
would like to enter a value in column A and then use that value as the
filename in the rest of the cells that link to the external workbook.
For example:

I want to enter A122 into colum A - and have the cell in column B pick that
value up and link to a cell in an external workbook with that name

Obviously, the following function links correctly to the external file and
returns the correct value:
='G:\Local files\Sample Tracking\[A122.xls]Sample Request'!$E$2

But, I do not want to have to change the syntax in a large number of cells
everytime I add to the list. I only want to enter the value (A122) in the
first cell and have the others retrieve linked values correctly.
In other words, I want a dynamic, external link.

Thanx in advance for any help.

i am trying to find or write VBA for excel in the case that macro is activated in the time before close.
i work on the same file as my collegues but sometimes they put links in that file, which driwe me crazy because we used to lost that sources.

i was wondering what macro would be to check workbook whatever there is or not any of the external links to other sources (other then that workbook).
I know that procedure for this kind of problem would be before_close, but i do not konow any furhter

any tips

Is there a way to paste a range of cells as links, but have the links use the locked (I don't know the proper term) address of the target cell). I would have said "absolute" reference but the paste link doc already says it uses absolute references.

For example, if I copy cell A2 from sheet1, and I paste as a link, i get the reference sheet1!A2 in the target cell.

Is there any way to get it to paste sheet1!$A$2? I'd like to be able to recopy and paste the link and have it refer to the original A2 but as it is now, the A2 is adjusted to be relative to where I copy it.

Thanks, first time poster here.
( I searched for paste and link but couldn't wade through the 1700+ results.)