Dustin Stanley
asked on
MS Access VBA How To Collect and Total All The Values In a XML Files Tags and Return Them In a Message Box
I am making an API call and there is a response XML file that I receive back. The XML file shows me Fees. Here is an Example:
How can I total all the Values in <Fee currencyID="USD">0.0</Fee> and return that back in a message Box? The above example would return "$1.10" in the message box.
Thanks for the help.
<?xml version="1.0" encoding="utf-8"?>
<BulkDataExchangeResponses xmlns="urn:ebay:apis:eBLBaseComponents">
<VerifyAddFixedPriceItemResponse xmlns="urn:ebay:apis:eBLBaseComponents">
<Timestamp>2017-05-03T15:50:41.794Z</Timestamp>
<Ack>Success</Ack>
<Version>1011</Version>
<Build>E1011_UNI_API5_18405133_R1</Build>
<ItemID>0</ItemID>
<SKU>sdgxgsdgyser</SKU>
<Fees>
<Fee>
<Name>AuctionLengthFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>BoldFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>BuyItNowFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>CategoryFeaturedFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>FeaturedFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>GalleryPlusFee</Name>
<Fee currencyID="USD">1.0</Fee>
</Fee>
<Fee>
<Name>FeaturedGalleryFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>FixedPriceDurationFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>GalleryFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>GiftIconFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>HighLightFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>InsertionFee</Name>
<Fee currencyID="USD">0.1</Fee>
</Fee>
<Fee>
<Name>InternationalInsertionFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>ListingDesignerFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>ListingFee</Name>
<Fee currencyID="USD">0.1</Fee>
</Fee>
<Fee>
<Name>PhotoDisplayFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>PhotoFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>ReserveFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>SchedulingFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>SubtitleFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>BorderFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>ProPackBundleFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>BasicUpgradePackBundleFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>ValuePackBundleFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>PrivateListingFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>ProPackPlusBundleFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
<Fee>
<Name>MotorsGermanySearchFee</Name>
<Fee currencyID="USD">0.0</Fee>
</Fee>
</Fees>
<DiscountReason>SpecialOffer</DiscountReason>
</VerifyAddFixedPriceItemResponse>
</BulkDataExchangeResponses>
How can I total all the Values in <Fee currencyID="USD">0.0</Fee>
Thanks for the help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a ton! it worked great!
Option Compare Database
Option Explicit
Public Sub EbayFeesTotal()
Dim xdDoc As New MSXML2.DOMDocument
Dim feeNodes As MSXML2.IXMLDOMNodeList
Dim feeNode As MSXML2.IXMLDOMNode
Dim totalFee As String
Dim FeeDivided As String
Dim JobIdValue As String
JobIdValue = DLookup("JobID", "createUploadJobResponse", "")
Set xdDoc = CreateObject("MSXML2.DOMDocument")
xdDoc.Load ("C:\Ebay Database\Responses\Ebay Results Unzipped\" & JobIdValue & "_responses.xml")
If xdDoc.parseError.ErrorCode <> 0 Then
MsgBox "error: " & xdDoc.parseError.reason & " in " & xdDoc.parseError.Line & ":" & xdDoc.parseError.linepos
Else
xdDoc.SetProperty "SelectionNamespaces", "xmlns:e='urn:ebay:apis:eBLBaseComponents'"
totalFee = 0#
Set feeNodes = xdDoc.SelectNodes("//e:Fees/e:Fee/e:Fee")
For Each feeNode In feeNodes
totalFee = totalFee + CDbl(feeNode.Text)
FeeDivided = totalFee / 2
Next
MsgBox "The eBay fees total is" & vbNewLine & "$" & FeeDivided & "0", vbInformation
End If
DoCmd.DeleteObject acTable, "createUploadJobResponse" 'This Deletes the createUploadJobResponse Table.
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
End Sub
ASKER
Perfect Thanks!
ASKER