Edit External Link (to Change Link Target)

[bradfriedlander]

I'm trying to find a way to edit an external link to point to a new target. The referencing formula has segments like "[0]" or "[1]" for the external links, but I haven't found a place in the PowerShell object where the link is defined (and can then be edited).

Thanks, Brad

[dfinke]

Please could you say a bit more? Post a pic of the sheet, and code for what you are trying to achieve.

[bradfriedlander]

Doug,

I apologize for the delay in responding. Here is some additional information.

• I have three spreadsheets. One of the three has external links that reference the other two.
• The attached picture shows the external links.
• The formula (in Excel) is "=GETPIVOTDATA("Amount",'C:\Pers\XYZZY\Financial\SP-3[SP-3 2020 Worksheet.xlsx]Pivot'!$A$3,"Item","Taxes - property","Property",VLOOKUP($B4,Table1[#Data],3))".
• The content of the formula when using ImportExcel is "GETPIVOTDATA("Amount",[1]Pivot!$A$3,"Item","Taxes - property","Property",VLOOKUP($B4,Table1[#Data],3))".

Notice that "[1]" appears to be a reference to the first external link. Other formulas correctly point to the second external link.

The question is how, using ImportExcel, can I change the value of the links. What I'm trying to do is change the year so that, for example, reference 2 "SP-3 2020 Worksheet.xlsx" would become "SP-3 2021 Worksheet.xlsx".

If I open the xslx file that contains the references as an archive, the path to the files containing the external link targets is:

SP-3 Schedule D.xlsx\xl\externalLinks\_rels\externalLink*.xml.rels

Where the content looks like this.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath" Target="SP-3/SP-3%202020%20Worksheet.xlsx" TargetMode="External"/>
</Relationships>

I've looked through the exposed information in ImportExcel and I can't find this information.

Thanks, Brad

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.