I am using two different workbooks the first one [Excel Data only] created as a databse and the second one [Invoice Template] created to create an invoice.

When creating an invoice i am using offset to read for example the list of locations of the clients.

=OFFSET('[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

And it is working correctly.

The problem is when i want to create a copy of the workbook [Invoice Template] that will have a different path for example on the desktop the offset changes to

=OFFSET('C:\Users\anthony\Desktop\[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

So automatically the path of the [Excel Data Only] workbook changed and is not reachable anymore since i just changed the path of the [Invoice Template] not the other one.

So how can i manage this formula so it doesn't not affect the path of the [Excel Data only] when changing the path of the [Invoice Template].

Thanks for helping!

  • Have you tried using the edit links-button in the Data-Ribbon? – Vincent Aug 10 at 12:45
  • Generally, excel will display the full path to an external source, when the source is currently closed. When the source is opened in excel as well, only the name of the file will be displayed. What I do not understand in your example: What is the correct path to Excel Data Only.xlsx, Invoice Template and copy of Invoice Template? – Vincent Aug 10 at 13:04
  • 'C:\Users\anthony\Desktop\channels (BE VERY VERY PRECAUTIOUS WITH THIS FILE)!!!!\0000 TEMPLATES\INVOICE template\New template this is the path of the two files [Excel Data Only] and [Invoice Template] i need to copy the invoice template to another path and then use the same data from [Excel data only], while using the template i keep [Excel Data Only] Opened but the problem still the same – Anthoz Aug 13 at 10:29

When you open an excel-file with external references (and allow it to perform Updates, when you get the security warning), Excel seems to perform some automatism to resolve the references. It seems to be optimised for cases where you move both source and target, especially for changing of drive letters (maybe a relict of floppy times?).

Unfortunately this might brake things, if you move the target file (Invoice Template.xlsx in your example) alone.

Probably the fastest way to fix things, is via the edit links dialogue that you can find in the Data-Ribbon. There you have a table of all external files, and you can change where those are pointing to (change source). When you correct the path to Excel Data Only.xlsx there, it should update all references to this file in your spreadsheet at once.

  • How can i edit the links automatically by using vba? – Anthoz Aug 13 at 12:30
  • can this solve my issue? 'Sub Sample() Dim Conn As Variant Dim ConString As String Dim oldPath As String, NewPath As String NewPath = "C:\MyPath.Csv" Set Conn = ActiveWorkbook.Connections.Item(1) Debug.Print Conn.TextConnection.Connection '==> TEXT;C:\Users\Siddharth\Desktop\Delete Later\Output.csv ConString = Conn.TextConnection.Connection oldPath = Split(ConString, ";")(1) ConString = Replace(ConString, oldPath, NewPath) Conn.TextConnection.Connection = ConString End Sub' – Anthoz Aug 13 at 12:32
  • Sure you can use VBA for that. Simple Search and Replace Ctrl + h will also work. – Vincent Aug 13 at 14:02

If you are encountering the problem on a huge trove of files that need to be updated in batch (or you are just feeling adventurous, oh, and do not forget to backup first!), you can try to edit the raw *.xlsx file. It is actually a zip archive.

If you open it with an archive explorer (7-zip for example), you will find the external references in the subdirectory \xl\externalLinks\_rels\. There is probably a file named externalLink1.xml.rels (number might vary). It is an XML-File that contains a (relative) file path and name. You can update it there. It will be typed as .../externalLinkPath marking it as relative to the location of your file. Changing that type to http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing might also solve the problem.

Your externalLink1.xml.rels might look like that I have not tested that path specifically:

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
      <Relationship 
          Id="rId1" 
          Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing"
          Target="'C:\Users\anthony\Desktop\channels%20(BE%20VERY%20VERY%20PRECAUTIOUS%20WITH%20THIS%20FILE)!!!!\0000%20TEMPLATES\INVOICE%20template\New%20template\Excel%20Data%20Only.xlsx "
          TargetMode="External"
      />
 </Relationships>

PS: I would discourage the use of Special Characters (Brackets, Exclamation Marks etc.) in Excel Files or Paths, that might cause trouble as well.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.