How to load .xml string into VBA

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
581
I'm attempting to "load" an .xml string into VBA to determine the properties of a cell. The code:
VBA Code:
?ActiveCell.Value(11)
provides an .xml output of various properties of the activecell. I have attempted to use the following code, but it fails to load
VBA Code:
Sub TestXML()
' https://analystcave.com/vba-xml-working-xml-files/
' https://excel-macro.tutorialhorizon.com/vba-excel-read-xml-by-looping-through-nodes/

Dim xmlSrc As Object, root As Object

  Set xmlSrc = CreateObject("MSXML2.DOMDocument")
  xmlSrc.Async = False: xmlSrc.validateOnParse = False
  xmlSrc.Load (ActiveCell.Value(11)) '("C:\Desktop\test.xml")  ' <<--- Fails here.  The test file is found here:[B] https://msdn.microsoft.com/en-us/library/ms762271%28v=vs.85%29.aspx[/B]

  Set root = xmlSrc.DocumentElement

   'Get Document Elements
   Set Lists = xmlSrc.DocumentElement
   
   'Get first child ( same as ChildNodes(0) )
   Set getFirstChild = Lists.FirstChild
   'Print first child XML
   Debug.Print getFirstChild.XML
   'Print first child Text
   Debug.Print getFirstChild.text


  Set Books = xmlSrc.SelectNodes(" / catalog / book")
  For i = 0 To Books.Length - 1
        For j = 0 To Books(i).ChildNodes.Length - 1
           Debug.Print Books(i).ChildNodes(j).tagName
           Debug.Print Books(i).ChildNodes(j).text
        Next
    Next

   Set xmlSrc = Nothing

'Select the tag from the XML file using SelectNodes or SelectSingleNode. _
             SelectNodes – Selects a list of nodes matches the Xpath pattern.

''             Set Books = oXMLFile.SelectNodes(“ / catalog / book”)
End Sub

I know I could actually download the .xml file to my computer, but my work settings prohibit me from opening the file again, so I've got to load the xml data directly into VBA. I know this code works on other files, but I'm stuck getting it to work for the .Value(11) output.

I realize this code would fail in Reading the .xml file (it's specific to the test file listed above), but I'm more concerned with getting the data loaded at this point.

Thanks y'all for your help.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
xmlSrc.Loadxml ActiveCell.Value(11)
 

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
581
You gotta be kidding me, Fluff! That's all it took?! Were all questions such softballs ;)

Thanks, man. I appreciate it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top