Pull XML Value From Web Page

[Schwimms]

Hi All,

I have the following code to open a webpage:

Sub Test()
    Dim IE As Object
    Dim x As Integer
    Dim e As Variant
    Dim d As Integer
    Dim Ar1 As Variant
    Ar1 = Array("food", "drinks")
 
    For d = 0 To 1
 
    x = 1
    Do Until x = e + 1
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "[URL]http://api.eventful.com/rest/events/search?...&c[/URL]=" & Ar1(d) & "&t=future&location=Chicago&page_number=" & x & "&page_size=100&app_key=xxxxx" ' should work for any URL"
        Do Until .ReadyState = 4: DoEvents: Loop
    End With
 
    IE.ExecWB 17, 0 '// SelectAll
    IE.ExecWB 12, 2 '// Copy selection
    ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
    Range("A1").Select
    x = x + 1
    IE.Quit
    Loop
 
    ActiveWorkbook.SaveAs Filename:="C:\Users\Schwimms\Desktop\Scraper\excel XML\" & Ar1(d) & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.ClearContents
    Selection.End(xlUp).Select
    Range("A1").Select
    Ar1(d) = Ar1(d + 1)
    Next d
End Sub

The page that opens is in xml and in that xml there is the "page_count" that I need to pick out so I can tell my macro when to stop looping. I have dimmed it as "e" in the code above. Following is the XML.

<SEARCH>
<TOTAL_ITEMS><SEARCH>

360 
100 
4

Somehow I need c = "4", which is the "page_count". Does anyone know how I can pick out the "4" from the xml on my page?

 

[JP2112]

The code I posted would pull the query result into Excel. The first block of code I posted is different and wouldn't be used with that code.

The XML import is very easy to do, but doesn't offer the degree of control as parsing the XML does. I'll see if I can come up with a better sample.

 

[Schwimms]

So....

I have been doing some research on msxml and I was able to append in the new xml although I am still having trouble with the pagecount. JP, for some reason I get an error when I set the pagecount. Was there something incorrectly coded when you sent over your example? Also the other question I had was how do I insert in a new xml column for the event description which is defined in the array?

Here is the new code:

Sub Scrape()
Dim doc1 As MSXML2.DOMDocument30
Dim doc2 As MSXML2.DOMDocument30
Dim doc2Node As MSXML2.IXMLDOMNode
Dim xmlDocRoot As Object
Dim search As Object
Dim x As Integer
Dim arint As Integer
Dim Ar1 As Variant
Dim pageCount As Long
x = 1
pageCount = 1

Set doc1 = New MSXML2.DOMDocument30
Set doc2 = New MSXML2.DOMDocument30
doc2.async = False
'doc1 is an xml document that has the basic xml structure
doc1.Load "C:\Documents and Settings\schwimms\My Documents\Website\base.xml"
Ar1 = Array("food", "music")
For arint = 0 To 1
Do Until x = pageCount + 1
doc2.Load "[URL]http://api.eventful.com/rest/events/search?...&c[/URL]=" & Ar1(arint) & "&t=future&location=Chicago&page_number=" & x & "&page_size=100&app_key=xxxxx"
For Each doc2Node In doc2.DocumentElement.ChildNodes
doc1.DocumentElement.appendChild doc2Node
Next
Set search = doc2.DocumentElement
'I get a compile error, object required when I try to run the code
Set pageCount = search.ChildNodes(2).nodeTypedValue
x = x + 1
Loop
pageCount = 1
x = 1
Ar1(arint) = Ar1(arint + 1)
Next arint

doc1.Save "C:\Documents and Settings\CB260E\My Documents\Website\AllXMLBooks.xml"

End Sub

 

[Schwimms]

JP, I found what I thought was suppose to be the code to catch the value in the Page_count...It worked once but when I tried to run it again it didn't work.

Set PageCount = doc2.SelectSingleNode("//search/page_count")

I also notice I don't want to import in the nodes for total_items through search_time....I'm starting to lose it...

 

[Schwimms]

Still struggleing with this.. any help?