What is [1]! in an Excel formula

[David Thielen]

Hi;

When we use a global macro we defined, we get the following for the
macro:

<c r="A1" t="e">
<f ca="1">[1]!autotag("&lt;wrut
select='/ROOT/Categories/CategoryID'/&gt;")</f>
<v>#NAME?</v>
</c>

I assume the [1] is telling it where/how to go find the macro. But how
does that work? There is an externalLink1.xml in the xlsx file but I
have no idea what that means.

And should this be changed in case the XLSX file goes to a user who
does not have our macro installed?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Colbert Zhou [MSFT]]

Hi Dave,

[1]! targets to the externalLink1.xml in the xl\externalLinks folder. It is
same that [2]! targets to the externalLink2.xml. So when Excel reads the
OpenXML part [1]!autotag, it will try to find the externalLink1.xml in
xl\externalLinks folder. And in the xl\externalLinks\_rels folder, there is
another correponding file externalLink1.xml.rels. Its content directs where
to find the addin used after [1]!. In my test, it looks like,

<?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/ex
ternalLinkPath"
Target="/Users/v-jzho/AppData/Roaming/Microsoft/AddIns/Book1.xlam"
TargetMode="External" />
</Relationships>

So Excel will try to resolve the external link to
"/Users/v-jzho/AppData/Roaming/Microsoft/AddIns/Book1.xlam". Once the AddIn
file is found there, Excel can call [1]!autotag correctly. Actually, if the
addin book1.xlam does not exist in the original place, Excel will pop up a
dialog to ask the user to edit links. Once we correct the links in that
dialog, Excel forumla can correctly calls into our AddIn's VBA codes.(This
Edit Links dialog can be launched by clicking Data Tab->Connections
Group->Edit Links)



Best regards,
Colbert Zhou
Microsoft Newsgroup Support Team

 

[David Thielen]

Hi Dave,

[1]! targets to the externalLink1.xml in the xl\externalLinks folder. It is
same that [2]! targets to the externalLink2.xml. So when Excel reads the
OpenXML part [1]!autotag, it will try to find the externalLink1.xml in
xl\externalLinks folder. And in the xl\externalLinks\_rels folder, there is
another correponding file externalLink1.xml.rels. Its content directs where
to find the addin used after [1]!. In my test, it looks like,

<?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/ex
ternalLinkPath"
Target="/Users/v-jzho/AppData/Roaming/Microsoft/AddIns/Book1.xlam"
TargetMode="External" />
</Relationships>

So Excel will try to resolve the external link to
"/Users/v-jzho/AppData/Roaming/Microsoft/AddIns/Book1.xlam". Once the AddIn
file is found there, Excel can call [1]!autotag correctly. Actually, if the
addin book1.xlam does not exist in the original place, Excel will pop up a
dialog to ask the user to edit links. Once we correct the links in that
dialog, Excel forumla can correctly calls into our AddIn's VBA codes.(This
Edit Links dialog can be launched by clicking Data Tab->Connections
Group->Edit Links)

Thank you. It did not occur to me the 1 in [1] was part of the
filename. Now it all makes sense.

thanks - dave


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm