0

I've been building a little bot for my business and it is essential that multiple XML files are uploaded at once into Excel to perform the remainder of the bot. I've successfully been able to upload multiple XML's using a loop however, as all the XMLs are not in the same format, it is necessary that only the required information is uploaded into Excel. I've looked up different sources but looks like I am doing it incorrectly. The relevant VBA code I have so far to upload multiple XMLs is this,

Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    With Sheets("Working Notes")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).row
        For i = 1 To lastrow
            strTargetFile = .Cells(i, "A")
            Set Wb = Workbooks.OpenXML(FileName:=strTargetFile, LoadOption:=xlXmlLoadImportToList)
            'wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Working Notes").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            Wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Working Notes").Range("B" & i)
            Wb.Close False
        Next i
    End With

The XML page I am looking at is this,

<?xml version="1.0" encoding="utf-8"?>
<abcd_omn_gatca:abcd_omn xmlns:abcd="urn:lu:etat:acd:abcd_omn:v2.0" xmlns:ftc="urn:lu:etat:acd:gatca:v2.0" xmlns:sfa="urn:oecd:ties:stfgatcatypes:v2" xmlns:abcd_omn_gatca="urn:lu:etat:acd:abcd_gatca:v2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="urn:lu:etat:acd:abcd_gatca:v2.0 abcd_gatca_V2.0.xsd">
    <abcd_omn_gatca:abcd_gatca>
        <abcd_omn_gatca:abcd_RefId>XXXXX</abcd_omn_gatca:abcd_RefId>
        <abcd_omn_gatca:abcd_Depositor>
            <abcd:NameDepositor>XXXXX</abcd:NameDepositor>
            <abcd:PersonalIdentificationNumberDepositor>XXXXX</abcd:PersonalIdentificationNumberDepositor>
            <abcd:AddressDepositor>
                <abcd:StreetPhysical>XXXXX</abcd:StreetPhysical>
                <abcd:NumberPhysical>XXXXX</abcd:NumberPhysical>
                <abcd:PostalCodePhysical>XXXXX</abcd:PostalCodePhysical>
                <abcd:CityPhysical>XXXXX</abcd:CityPhysical>
                <abcd:CountryPhysical>XXXXX</abcd:CountryPhysical>
            </abcd:AddressDepositor>
            <abcd:PersonDepositor>
                <abcd:Name>XXXXX</abcd:Name>
                <abcd:FirstName>XXXXX</abcd:FirstName>
                <abcd:EmailPersonal>XXXXX</abcd:EmailPersonal>
                <abcd:TelephoneDirect>XXXXX</abcd:TelephoneDirect>
            </abcd:PersonDepositor>
        </abcd_omn_gatca:abcd_Depositor>
        <abcd_omn_gatca:abcd_Declarer>
            <abcd:NameDeclarer>XXXXX</abcd:NameDeclarer>
            <abcd:PersonalIdentificationNumberDeclarer>XXXXX</abcd:PersonalIdentificationNumberDeclarer>
            <abcd:AddressDeclarer>
                <abcd:StreetPhysical>XXXXX</abcd:StreetPhysical>
                <abcd:NumberPhysical>XXXXX</abcd:NumberPhysical>
                <abcd:PostalCodePhysical>XXXXX</abcd:PostalCodePhysical>
                <abcd:CityPhysical>XXXXX</abcd:CityPhysical>
                <abcd:CountryPhysical>XXXXX</abcd:CountryPhysical>
            </abcd:AddressDeclarer>
            <abcd:PersonDeclarer>
                <abcd:Name>XXXXX</abcd:Name>
                <abcd:FirstName>XXXXX</abcd:FirstName>
                <abcd:EmailPersonal>XXXXX</abcd:EmailPersonal>
                <abcd:EmailOrganisation>XXXXX</abcd:EmailOrganisation>
                <abcd:TelephoneDirect>XXXXX</abcd:TelephoneDirect>
            </abcd:PersonDeclarer>
        </abcd_omn_gatca:abcd_Declarer>
        <abcd_omn_gatca:abcd_ReportingPerson>
            ***<abcd:NameReportingPerson>RENIN III S.A R.L.</abcd:NameReportingPerson***>
            <abcd:IdentificationNumber>XXXXX</abcd:IdentificationNumber>
            <abcd:IdentificationNumberExtension>XXXXX</abcd:IdentificationNumberExtension>
            <***abcd:AddressReportingPerson>
                <abcd:StreetPhysical>Avenue M.F. Brady</abcd:StreetPhysical>
                <abcd:NumberPhysical>45</abcd:NumberPhysical>
                <abcd:PostalCodePhysical>1234</abcd:PostalCodePhysical>
                <abcd:CityPhysical>Somethingburg</abcd:CityPhysical>
                <abcd:CountryPhysical>SB</abcd:CountryPhysical>
            </abcd:AddressReportingPerson>***
            <abcd:PersonContactReportingPerson>
                <abcd:Name>XXXXX</abcd:Name>
                <abcd:FirstName>XXXXX</abcd:FirstName>
                <abcd:EmailPersonal>XXXXX</abcd:EmailPersonal>
                <abcd:EmailOrganisation>XXXXX</abcd:EmailOrganisation>
                <abcd:TelephoneDirect>XXXXX</abcd:TelephoneDirect>
            </abcd:PersonContactReportingPerson>
        </abcd_omn_gatca:abcd_ReportingPerson>
        <abcd_omn_gatca:RFI_Identifier>XXXXX</abcd_omn_gatca:RFI_Identifier>
        <abcd_omn_gatca:ReportContent>
            <abcd_omn_gatca:ReportingPeriod>XXXXX</abcd_omn_gatca:ReportingPeriod>
            <abcd_omn_gatca:ZeroReporting>XXXXX</abcd_omn_gatca:ZeroReporting>
        </abcd_omn_gatca:ReportContent>
    </abcd_omn_gatca:abcd_gatca>
</abcd_omn_gatca:abcd_omn>

The only piece of information I require is the name of the reporting person "RENIN III S.A R.L." and the Address of the Reporting Person on the Excel sheet. Forgive me for my very immature knowledge of VBA. I will be much obliged for any help. Many thanks!

Renin Mathew
  • 47
  • 8
  • Why not use the Microsoft XML Library to parse? [How to parse XML using vba](https://stackoverflow.com/a/11406/9912714) – TinMan Jun 27 '18 at 14:12
  • 1
    It would probably be even easier and more flexible to setup an `ADODB.Connection` to the files and query them. [Connect an ADO Recordset to an XML file located at a URL](http://www.vb-helper.com/howto_url_recordset.html) – TinMan Jun 27 '18 at 14:15
  • @TinMan - I've already been through this post and attempted the same. I guess I'm unable to enter the name of the node correctly. Can you advise how the same code can be implemented in this piece? – Renin Mathew Jun 27 '18 at 14:27
  • @TinMan - Reading the XML is not really the problem, I've been able to get that bit for uploading multiple XMLs into Excel. My problem is more along the lines of how the amount of information that is uploaded into Excel can be limited to just the nodes I require. – Renin Mathew Jun 27 '18 at 14:29
  • 1
    That is precisely my point. Parse or Query the XML and load the results. You can even Query the files with an `ADODB.Connection` and it create a new XML File containing only the relevant data. – TinMan Jun 27 '18 at 14:59
  • @TinMan My apologies, I'm lost and my brain's fried :( Would you be able to replicate it here? – Renin Mathew Jun 27 '18 at 15:31
  • I'm working on it. Your XML snippet is invalid. I am assuming that you added the `***`. I also think that it is an incomplete snippet which means that I will have to close the tags before it will work. – TinMan Jun 27 '18 at 15:38
  • The tags seem correct to me. Can you teset this [parse xml.txt](https://drive.google.com/open?id=1mFtvfGkOzoFnMee1Iico955kQu3fh4vV) code for me? I am refactoring [Connect an ADO Recordset to an XML file located at a URL](http://www.vb-helper.com/howto_url_recordset.html). – TinMan Jun 27 '18 at 15:43
  • I get a runtime error 3001. Is a reference to be added? – Renin Mathew Jun 27 '18 at 22:11
  • It looks like you have duplicate constant values. Comment out the consrants – TinMan Jun 27 '18 at 22:14

1 Answers1

1

After removing the "***", I then loaded your XML from file (test.xml). Provided you include the reference to the required namespace you can access as follows:

Option Explicit
Public Sub testing()
    Dim xmlDoc As New MSXML2.DOMDocument60
    xmlDoc.validateOnParse = True
    xmlDoc.setProperty "SelectionNamespaces", "xmlns:abcd=""urn:lu:etat:acd:abcd_omn:v2.0"""
    If xmlDoc.Load("C:\Users\User\Desktop\test.xml") Then
        Dim node As Object
        Debug.Print xmlDoc.SelectNodes("//abcd:AddressReportingPerson").Length
        Debug.Print xmlDoc.SelectNodes("//abcd:NameReportingPerson").Length
        For Each node In xmlDoc.SelectNodes("//abcd:AddressReportingPerson")
            Debug.Print node.Text
        Next node
        For Each node In xmlDoc.SelectNodes("//abcd:NameReportingPerson")
            Debug.Print node.Text
        Next node
    Else
        Debug.Print "Houston, we have a problem!"
    End If
End Sub
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • I'm sorry I never responded, you've been a help and mentor throughout my VBA journey and I'm grateful. Your code here was not exactly what I was looking for, but got me started in the right direction. Many thanks again! – Renin Mathew Apr 27 '20 at 22:34
  • No worries. You can always add your solution remember! :-) – QHarr Apr 28 '20 at 07:30