Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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







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.


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?

Regards

Johnny


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?

Regards

Johnny


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?


Hi,

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!

Cheers,

Mike




hi
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?

Thanks.


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.

Best,

AD


....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
Amanda



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.


(1) I receive worksheet A by email. The person who sent it did not copy, paste, values and therefore it comes in with links to worksheet B to which I do not have access. In Excel 2000, I only had to say No to "Update Links" when I opened worksheet A. Even though, the cells would reference a non-existent worksheet, the values that it had stored when it was last saved would be displayed. This does not work with Excel 2007. All I can see is VALUE# errors.

(2) When I copy a file from, say, the J drive (with links to another file in J), to the C drive, all of the links to J files now get automatically changed to C, where there are no such paths and files. Excel 2000 never did this, unless I copied the entire folder filled with inter-connected worksheets.

Please help! I receive a lot of worksheets with problem 1 and it is becoming a big issue. I have been opening them in Excel 2000 and working from there which is like going back in the evolutionary process!

Thanks!




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 - http://support.microsoft.com/kb/188449
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,
SunOffice

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

Code:

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.

Titus


Greetings!

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?

Thanks,

Rob


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 ?

AdmirerDr


Hi,

I'm trying to manually update links to an external sheet (one that is closed).

This link

http://office.microsoft.com/en-us/ex...993801033.aspx

Shows how to do it in Excel 2003 (its in the bit called "Update a link to another program now")

Anyone know how to do this in 2007?


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!