Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

External Reference Using Relative Paths

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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.

View Answers     

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?


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

='.\[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.

Hello Guys!

How would you solve the following problem:

I have a few houndreds of .xls files on a certain computer in the net,
many of them having links in formulas to external .xls files. They now
all should move to a different folder. This means the links are not
valid anymore. I have to find all .xls files and update the links. How
could I do that?

best regards, tequilajoe

I have a workbook that, once upon a time, linked to five external workbooks. I have since cleaned up the workbook so that it only links to one external workbook. However, whenever I open the sheet and am prompted to update the links, it tells me that one of my links won't update. When I click into the list of links (which now only has the link to the one external workbook that I am still linking to), it says the status is ok, and if I update them through that box, I get no errors. Any idea how I can get it to stop telling me there is an error if there is not?

Hi all.

We are shortly to move away from a Novell Directory Structure to a Windows one.

One of the tasks involved will be to update ALL external links in spreadsheets.

I need to know how many spreadsheets contain external links, so we can guage how much time to alot this task. I assume internal links will not be affected.

So my questions is simply (although I expect the answer to be a little more complex), are there any programs (freeware/licenceware) available that can determine the number of external links a spreadsheet has without opening each spreadsheet individually?



I get a spreadsheet emailed to me that contains external links. When I open the Read-Only file it prompts me to update or do not update the external links. Either selection and the links are auto updated and my sheet gets screwed up with #VALUE!.

Frustration is that other people on the distribution list open the same file with no issues. The file opens with the values stored from last save. Is there some configuration that I have set that would cause this? I tried turning off update external links, but nothing changes.

I have formulas that use external links to find data. The external links work fine when all of the files are open but if one of them is closed i get a reference error how can I fix this so I can just have one window open?


I have a workbook called Control.xls which has external links to other workbooks. The external links do not update automatically or when prompted at open and the cells remain as #Value. Opening the links workbooks doesn't work either. The linked formula's are just simple SUMIF arguments.

Additionally, when I go to add a new external link in Control.xls I can't select any cell ranges in other workbooks! I have a feeling that if we focus on this issue we will resolve the other issues.

I created the workbooks in 2003 and usually work in 2003 but I find that the external links work fine when Control.xls is opened in 2010.

Any help with this is greatly appreciated as it is slowly driving me insane!



i have a pendrive with various folders and workbooks. i have create external reference between this workbooks, but excel 2010 saves the absolute path and the letter of pendrive change broken the link.

anyone know how to create an external reference with relative path?

THANKS and sorry for my english.

Hi there...

I have an old excel file with external links, one thing is that I do not know which cells contains those external link, is there a way to tell me all the external links?


Hello all, thanks for any help you can provide.

I should mention I am using both Excel 2003 and 2007, as I have established a file that is being used by about 8-10 users within the office, about half still have 2003.

My problem is the following: I have an excel file that keeps a number of links to internally saved documents, as it is part of a way to keep track of files. For some reason, the links automatically update without my knowledge, switching from an absolute link (anchored to a shared drive) to a relative link (anchored to the person using the computer at the time).

Of course when switched to a relative link, all others that need to use the file lose access, which I am trying to avoid. Does anyone know why this might be happening? It seems to even happen on links that I have not selected, and no warning messages pop up.

Thanks to anyone that can help.



....I have a spreadsheet with links to several external files. When I open it,
I am prompted to open/update the links to the external files. Is it possible
to temporarily disable or swtich this prompt off, as sometimes I just need to
reference my destination file quickly with updating.

Thanks in advance

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.

Hi All,

I want to know how can I completely remove all the external links from a workbook. In Excel 2010 and 2013, Menu bar >> Data tab >> Edit Links, here I can see "Break Links" option to disconnect external links/files, and this method can also be done by VBA program:

Removing external links in Excel:

Please Login or Register  to view this content.

Now what I am actually looking at is... something like this - XL: Delete Links Wizard Available -
I have checked this add-in tool, but it does not work for "*.xlsx" and ".xlsm" files.

Break Links method only disconnect from the links, and I also want to remove the displayed broken links, so the "Edit Links" window/information box should be blank.

Please help me!

Thanks & Regards,

Hi, I have the following question:

How can I run a DOS .bat (batch) file from within a Macro?

This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.

With absolute paths, this is what I have


Call Shell(Environ$("COMSPEC") & " /c  C:\tmp\web_hotel_check.bat", vbNormalFocus)

The thing is I would need to run the web_hotel_check.bat file using a relative path...

Any ideas?
Thanks to all of your for your time.



I'm using INDIRECT and CONCATENATE to dynamically build links to external workbooks. When all of the external workbooks are opened, my master spreadsheet looks fine. If the external workbooks are closed, then I end up with #REF! errors.

I checked the online help about unchecking the "Ask to update automatic links" option on the "Edit" tab of the Tools->Options... dialog. I'm assuming that this would work if I have explicit external workbook links in my master workbook. However, because I'm dynamically building the external links with INDIRECT and CONCATENATE, that doesn't seem to work.

Any suggestions that will allow me to not have to open every workbook just to view the one?



Need urgent help / possible solutions from experts and willing to compensate suitably.

A work book has spread sheets and these contain external links. External Links file are not available. Is it possible to create link files ( in xl) i.e. source files in xl in same work book in order to these linked files get updated.

In other words :
There is a workbook A which has values on "sheet1"
Another workbook B has links(formulas) to workbook A
I want to reconstruct file A "sheet1" by looking to workbook B.

Anyone there to help ?


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!

Hi All,

First time poster, thanks for what ever help you can give:

I want it to show the whole file path to the link, even though it is referring to a location within the current file. Excel cuts out the file path for internal links automatically. So when I have a file open (let's call it "example.xls" , in the folder "files") and enter

='c:/files/[example.xls]sheet 1'!$A$1

it automatically switches to ...


because I already am in the file example.xls . It's switching an external link to an internal link.

I'm trying to stop it from doing that.

Hi Everyone

I have a problem with updating links, which I hope someone might be able to help me with. I have a large workbook with automatic external links to about 30 files. This main workbook has a "data sheet" which shows the months of the year in 12 columns. I have written a macro that will update the data as soon as the macro is run (which is done when the data is available in the 30 external link files.)

Basically, I run the macro e.g. for June. This takes the formulas and external links and other data in the column for May and copies it to the column for June. The data that needs to be linked is all stored in monthly folders. E.g., for June, all the data will be saved in the June folder etc. When the macro finished copying and pasting the formulas (and changed the words "May" to June" in the formulas), it should update the links.

At the moment, when I do this, it shows the Update Values: window, expecting me to navigate to each file.

Since each time I do this, there could be about 500 links to these 30-odd files, this is absolutely impossible to do it each time the macro runs. It defeats the object of why I'm doing the macro in the first place - to save time. Has anyone got any ideas that might help me with this please?

I've been struggling with this for months now, and trawled the internet for information. Although I always find lots of info on external links, breaking links, updating links etc, I have not yet found anything that will solve my specific problem. Any help would be greatly appreciated, as I am just about to jump out of the window from frustration. Thanks in advance guys.

PS: Congratulations to Smitty and Kirsty on their MVPs.

Hi all

I have written some code that loops through the links in a workbook and
updates them to different paths. To be more specific, it updates the
paths from drive letters to UNC paths.

Generally, this works, but fails where the new path forces formulae in
the workbook to exceed Excel's 1024 character limit. Interestingly,
the link update fails, but it raises no error, so there's not an
automatic means of determining that one or more links has failed to

I would like to know after the code has completed whether any updates
have failed. Does anyone have a fix or workaround for this?

Thanks in advance

Paul Martin
Melbourne, Australia