☰

Excel 從兩個不同的工作簿偏移，建立一個工作簿的副本而不影響另一個

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007
Excel 從兩個不同的工作簿偏移，建立一個工作簿的副本而不影響另一個

我使用兩個不同的工作簿，第一個工作簿 [僅限 Excel 資料] 建立為資料庫，第二個工作簿 [發票範本] 會建立用於建立發票。

建立發票時，我使用偏移量來讀取例如客戶位置清單。

=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)

並且它工作正常。

問題是，當我想建立工作簿 [發票範本] 的副本時，該副本將具有不同的路徑，例如在桌面上，偏移量變更為

=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)

因此，[僅限 Excel 資料] 工作簿的路徑會自動更改，並且不再可訪問，因為我剛剛更改了 [發票範本] 的路徑，而不是另一個路徑。

那麼，我該如何管理這個公式，以便在更改[發票範本]的路徑時不影響[僅限Excel資料]的路徑。

感謝您的幫忙！

答案1

當您開啟具有外部參考的 Excel 檔案時（並允許它執行更新，當您收到安全警告時），Excel 似乎會執行一些自動操作來解析參考。它似乎針對移動來源和目標的情況進行了優化，尤其是更改驅動器號（可能是軟碟時代的遺留物？）。

不幸的是，如果您單獨移動目標檔案（Invoice Template.xlsx在您的範例中），這可能會破壞事情。

解決問題最快的方法可能是透過功能區edit links中的對話Data。那裡有一個包含所有外部文件的表，您可以更改這些文件指向的位置 ( change source)。當您更正那裡的路徑時Excel Data Only.xlsx，它應該立即更新電子表格中對此文件的所有引用。

答案2

如果您遇到需要批量更新的大量文件的問題（或者您只是想冒險，哦，不要忘記先備份！），您可以嘗試編輯原始文件*.xlsx。它實際上是一個 zip 檔案。

如果您使用存檔瀏覽器打開它（7-拉鍊例如），您將在子目錄中找到外部參考\xl\externalLinks\_rels\。可能有一個名為externalLink1.xml.rels（數字可能有所不同）的檔案。它是一個包含（相對）檔案路徑和名稱的 XML 檔案。您可以在那裡更新它。它將被輸入為.../externalLinkPath將其標記為相對於文件的位置。將該類型變更為http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing也可能解決問題。

您的 externalLink1.xml.rels 可能看起來像我沒有專門測試該路徑：

 <?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：我不鼓勵在 Excel 檔案或路徑中使用特殊字元（括號、驚嘆號等），這也可能會造成麻煩。

相關內容