How to use ComboBox to extract information form XML file?

Question

I have a XML file that contain a lot of information. So, I would like to create a macro in VBA Excel that allow me to filter the information based on the accountID (available form an Userform - ComboBox)

It's the first time I work with XML and userform.

I've tried to adjust multiple code that I found on the net, but i understand better this one so I would like to continue with something similar (if possible):

Private Sub ComboBox1_Click()
    Dim wks As Worksheet
    Set wks = Sheet2

    ' Load the XML document
    Dim XDoc As Object, root As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load ("C:\Users\isabelle\Google Drive\IB API Integration\Flexqueries\FlexDay.xml") 

    Dim singleNode As Object
    Set singleNode = XDoc.SelectSingleNode("//FlexQueryResponse/FlexStatements/FlexStatement[@accountId='U2396623']")

End Sub

The accountId 'U2396623' is only an example. This will correspond to the entry in the combobox.

My two problems are :

First, I don't know how to link the entry in the combobox to the code (based on the accountID)
Second, code above does not work. I think the path (XDoc.SelectSingleNode) is not the right one... So, I tied several combination and it never worked. Or maybe it's the output that does not work correctly (The output should be in the Sheet2 )

My XML file looks like it :

<FlexQueryResponse queryName="Sample_1" type="AF">
   <FlexStatements count="10">
      <FlexStatement accountId="" fromDate="2019-04-22" toDate="2019-05-21" period="Last30CalendarDays" whenGenerated="2019-05-22;13:49:30">
         <AccountInformation accountId="" acctAlias="" currency="CAD" accountType="Advisor Client" dateOpened="2018-02-08" dateFunded="2018-03-01" dateClosed="" street="" street2="" city="" state="" country="" postalCode="" primaryEmail="" />
         <ChangeInNAV accountId="" acctAlias="" startingValue="" endingValue="" realized="0" changeInUnrealized="0" depositsWithdrawals="0" twr="" mtm="" dividends="" changeInDividendAccruals="" interest="" changeInInterestAccruals="" advisorFees="" clientFees="0" otherFees="0" />
         <CashReport>
            <CashReportCurrency accountId="" acctAlias="" clientFees="0" commissions="" deposits="0" withdrawals="0" accountTransfers="0" dividends="" advisorFees="" otherFees="0" currency="BASE_SUMMARY" startingCash="" endingCash="" endingSettledCash="" />
            /&gt;
         </CashReport>
         <OpenPositions>
            <OpenPosition accountId="" acctAlias="" symbol="" position="" costBasisPrice="" strike="" expiry="" putCall="" positionValue="" percentOfNAV="" fifoPnlUnrealized="" currency="CAD" fxRateToBase="1" assetCategory="STK" description="BOMBARDIER INC PFD SER 2" securityID="CA0977515075" cusip="" isin="CA0977515075" />
         </OpenPositions>
         <FxPositions>
            <FxPosition accountId="" fxCurrency="CAD" quantity="" costPrice="" unrealizedPL="0" />
         </FxPositions>
         <OptionEAE>
            <OptionEAE accountId="" acctAlias="" currency="USD" assetCategory="" symbol="" description="" securityID="" cusip="" isin="" listingExchange="" underlyingConid="" underlyingSymbol="FCAU" underlyingSecurityID="NL0010877643" underlyingListingExchange="NYSE" issuer="" strike="16" expiry="2019-05-03" putCall="C" date="2019-04-22" transactionType="Assignment" quantity="2" tradePrice="0.0000" markPrice="0.3500" realizedPnl="0.00" />
         </OptionEAE>
         <PendingExcercises />
         <ClientFees />
         <OpenDividendAccruals>
            <OpenDividendAccrual accountId="" acctAlias="" currency="" assetCategory="" symbol="" description="" securityID="" cusip="" isin="" exDate="2019-05-03" payDate="2019-06-24" quantity="400" grossRate="0.23" grossAmount="92" netAmount="92" />
         </OpenDividendAccruals>
      </FlexStatement>
   </FlexStatements>
</FlexQueryResponse>

If you have any question don't hesitate !

Thank you for your help

Just an advise, instead of redact a screen clip, replace with some generic text and paste here (or share from cloud storage) so people can test with it (just 1 example is enough). Don't expect people to type those XML! Also why is it Click event of ComboBox? — PatricK, May 21 '19 at 23:58
If you wanted to output specific values from the FlexStatement then you would need to select the values you want by calling `selectSingleNode` or `selectNodes` on `singleNode` and then outputting those values to a range on the worksheet. Also, `Set XDoc = CreateObject("MSXML2.DOMDocument")` should be `Set XDoc = CreateObject("MSXML2.DOMDocument.6.0")` to ensure you are using the most up to date version — barrowc, May 22 '19 at 00:39
a) Even if your set SingleNode `Not Is Nothing` (i.e. a `FlexStatement` node exists), it's necessary to describe which subnode informations you want to write to sheet showing us a minimal structure or XPath and some example contents. - Maybe you would like to study how to [display XML structures including attributes](https://stackoverflow.com/questions/51887820/obtain-attribute-names-from-xml-using-vba/51919182#51919182) first. b) It's easy to get the combobox value via its zero-based `.ListIndex` property and to integrate it into your **XPath** via the `&` operator to connect string parts. — T.M., May 22 '19 at 17:05

score 0 · Accepted Answer · answered May 22 '19 at 21:32

Consider several adjustments:

Use the AfterUpdate trigger event of combobox.

Private Sub ComboBox1_AfterUpdate()
    ...
End Sub

Simply concatenate the combobox value Me.ComboBox1 to XPath expression.

XDoc.SelectSingleNode("/FlexQueryResponse/FlexStatements/FlexStatement[@accountId='" & Me.ComboBox1 & "']")

Select the needed attribute value or loop through multiple by node for extraction to sheet.

Private Sub ComboBox1_AfterUpdate()
    ' Load the XML document
    Dim XDoc As Object, root As Object, singleNode As Object
    Dim i As Integer

    Set XDoc = CreateObject("MSXML2.DOMDocument")

    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load ("C:\Users\isabelle\Google Drive\IB API Integration\Flexqueries\FlexDay.xml")

    ' OUTPUT ONE ATTRIBUTE OF NODE
    Set singleNode = XDoc.SelectSingleNode("/FlexQueryResponse/FlexStatements/FlexStatement[@accountId='" & Me.ComboBox1 & "']")
    ThisWorkbook.Worksheets("Sheet1").Range("A1") =  singleNode.Attributes.getNamedItem("accountId").Text

    ' OUTPUT ALL ATTRIBUTES OF NODE
    For i = 0 To singleNode.Attributes.Length - 1
         ThisWorkbook.Worksheets("Sheet1").Range("A" & i + 1) = singleNode.Attributes(i).Name
         ThisWorkbook.Worksheets("Sheet1").Range("B" & i + 1) = singleNode.Attributes(i).Text
    Next i

    Set XDoc = Nothing
End Sub

How to use ComboBox to extract information form XML file?

1 Answers1