Hi,

I'm trying to write a macro that determines which worksheet corresponds to which file in xl/worksheets. I found an example on the web, but the SelectNodes method doesn't seem to return any nodes as the for loop just skips without stopping. I can't really find a decent description of how to use the xPath syntax.

Any help much appreciated.


VBA

Public Sub GetSheetFileNameFromId()
Dim oXMLDoc As MSXML2.DOMDocument60
Dim oXMLNode As MSXML2.IXMLDOMNode
Dim oXMLNodeList As MSXML2.IXMLDOMNodeList
Set oXMLDoc = New MSXML2.DOMDocument60
oXMLDoc.async = False
oXMLDoc.Load "C:\Users\gapage\Documents\VBA and Tools\Unzipped Deprectiation.xlsx.zip\xl\_rels\workbook.xml.rels"

Set oXMLNodeList = oXMLDoc.SelectNodes("/Relationships/Relationship")
For Each oXMLNode In oXMLNodeList
Debug.Print oXMLNode.NodeValue

Next
End Sub

XML code

<?xml version="1.0" encoding="UTF-8" standalone="true"?

-<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"

<Relationship Target="worksheets/sheet3.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId3"/

<Relationship Target="calcChain.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/calcChain" Id="rId7"/

<Relationship Target="worksheets/sheet2.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId2"/

<Relationship Target="worksheets/sheet1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId1"/

<Relationship Target="sharedStrings.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Id="rId6"/

<Relationship Target="styles.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Id="rId5"/

<Relationship Target="theme/theme1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Id="rId4"/

</Relationships

I'm trying to write a macro that determines which worksheet
corresponds to which file in xl/worksheets.

Are you saying you want to list the sheetnames of all files in a
specific folder like so...

xl\worksheets\
File1.xls
Sheet1
Sheet2

File2.xls
Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6

...and so on?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus