VBA Put the array into the specified cell-CodePudding

I have an array obtained from xpath, I want to put it from C~**, because the number of items in the array is different, the following is the solution I wrote, but he can't execute

Sub main2()
Dim myURL As String
myURL = "https://exsample.com"

Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.SetProperty "SelectionLanguage", "XPath"
xmlDoc.async = False

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", myURL, False
    .send
    xmlDoc.LoadXML .responseText
End With

Set nodeXML = xmlDoc.getElementsByTagName("Business_Item_Desc")
For i = 0 To nodeXML.Length - 1
    Sheet4.Range("C1:Z1").Value = nodeXML
Next

End Sub

CodePudding user response：

I think the problem is that you are trying to put a whole XML tag into a cell. "xmlDoc.getElementsByTagName" returns a collection of elements, so you need to refer to the item in the collection and to a property of the item as follows:

Sheet4.Range("C1:Z1").Value = nodeXML.Item(i).Text

It also looks as though you are trying to write the results into different cells, so we need to correct this line to write to one cell at a time as follows:

Sheet4.Range("C1").Offset(0, 1).Value = nodeXML.Item(i).Text

I've commented out the parts of your code that fetch the document and hard-coded a document for example purposes so you can see a working example in context:

Sub main2()
    'Dim myURL As String
    'myURL = "https://exsample.com"
    '
    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    'xmlDoc.SetProperty "SelectionLanguage", "XPath"
    'xmlDoc.async = False
    '
    'With CreateObject("MSXML2.XMLHTTP")
    '    .Open "GET", myURL, False
    '    .send
    '    xmlDoc.LoadXML .responseText
    'End With
    
    Dim document As String
    document = "<CATALOG>"
    document = document & "  <PLANT>"
    document = document & "    <COMMON>Bloodroot</COMMON>"
    document = document & "    <Business_Item_Desc>Sanguinaria canadensis</Business_Item_Desc>"
    document = document & "    <PRICE>$2.44</PRICE>"
    document = document & "    <AVAILABILITY>031599</AVAILABILITY>"
    document = document & "  </PLANT>"
    document = document & "    <PLANT>"
    document = document & "    <COMMON>Columbine</COMMON>"
    document = document & "    <Business_Item_Desc>Aquilegia canadensis</Business_Item_Desc>"
    document = document & "    <PRICE>$9.37</PRICE>"
    document = document & "    <AVAILABILITY>030699</AVAILABILITY>"
    document = document & "  </PLANT>"
    document = document & "</CATALOG>"

    xmlDoc.LoadXML document
    
    Set nodeXML = xmlDoc.getElementsByTagName("Business_Item_Desc")
    For i = 0 To nodeXML.Length - 1
        Sheet4.Range("C1").Offset(0, i).Value = nodeXML.Item(i).Text
    Next

End Sub