VBScript Extracting HTML Tables

[preludeofme]

I'm working on some code that i found from:http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html
(which for those that haven't read the article, it is pretty cool stuff)

but what i'm trying to do is get the vbscript "bees" to read the sourcecode for the website i'm pulling from by using the MSHTML objects. below is what i've got so far, but it keeps erroring out at the "HTMLBody.innerHTML = oXML.responseText".

i was trying to avoid having to parse out the sourcecode line by line
and just use the html objects but i'm guessing vbscript doesn't have
that functionality... any ideas?

Set oXL = GetObject(, "Excel.Application")
Set oXML = Wscript.CreateObject("MSXML2.<WBR>XMLHTTP")
Set HtmlDoc = Wscript.CreateObject("<WBR>htmlfile")
Set HTMLBody = HtmlDoc.body
 
 
' Navigate to property page
oXML.Open "GET", URL,False
Wscript.Sleep 50
oXML.send
Wscript.Sleep 50
 
' Get html ////ERROR HAPPENS HERE////
HTMLBody.innerHTML = oXML.responseText
 
 
Set Ach = HTMLBody.document.<WBR>getElementById("<WBR>InformationGrid2")
 
 
              If Ach Is Nothing Then
              Else
 
                  For Each Row In Ach.Rows
                          If
Ach.Rows(Row.RowIndex).Cells(<WBR>0).innerText <> "Type" Then
                              If
Ach.Rows(Row.RowIndex).Cells(<WBR>0).innerText <> "No Requests found for
this Account " Then
                                  Debug.Print
Ach.Rows(Row.RowIndex).Cells(<WBR>9).innerText
                                  If
Ach.Rows(Row.RowIndex).Cells(<WBR>9).innerText = "Edit " Then
                                      vResults(0) = Acct
                                      vResults(1) =
Ach.Rows(Row.RowIndex).Cells(<WBR>0).innerText
                                      vResults(2) =
Ach.Rows(Row.RowIndex).Cells(<WBR>1).innerText
                                      vResults(3) =
Ach.Rows(Row.RowIndex).Cells(<WBR>2).innerText
                                      vResults(4) =
Ach.Rows(Row.RowIndex).Cells(<WBR>3).innerText
                                      vResults(5) =
Ach.Rows(Row.RowIndex).Cells(<WBR>4).innerText
                                      vResults(6) =
Ach.Rows(Row.RowIndex).Cells(<WBR>5).innerText
                                      vResults(7) =
Ach.Rows(Row.RowIndex).Cells(<WBR>6).innerText
                                      vResults(8) =
Ach.Rows(Row.RowIndex).Cells(<WBR>7).innerText
                                      vResults(9) =
Ach.Rows(Row.RowIndex).Cells(<WBR>8).innerText
                                      vResults(10) =
Ach.Rows(Row.RowIndex).Cells(<WBR>9).innerText

 

[preludeofme]

am i just stuck with trying to parse out the html by hand?

 

[Norie]

Does it not depend on the page and how it's structured?

That's one of the comments in the link that seem to be made regarding using this method.

Anyway, how does it error out?

 

[preludeofme]

no i don't think it has to do with the site itself because if i basically took an existing VBA script i use to screenscrape and just dropped it in the code from the site.

the error i get is:

Line: 31
Char: 1
Error: Object Required
Code: 800A01A8
Source: Microsoft VBScript Runtime Error

and it's pointing to this line: "HTMLBody.innerHTML = oXML.responseText" and it looks like the HTMLBody object is not being identified as an MSHTML object

 

[Norie]

Have you taken a look at HTMLBody, HTMLDoc in the Watch window?

 

[preludeofme]

Well i'm building it out of excel's VB editor and it doesn't debug the VBScript code. so no way to check the watch window (i wish it did though because that would be hella helpful )

 

[Norie]

So where does Excel come into this?

 

[preludeofme]

I only have access to excel to build this tool, so I'm having excel build the code and run the vbscript via a shell.

 

[Norie]

So is this code in the VBA Editor or are you using other code to create the VB script in a file and then running the script?

 

[John_w]

Well i'm building it out of excel's VB editor and it doesn't debug the VBScript code. so no way to check the watch window (i wish it did though because that would be hella helpful )

Develop it in Excel VBA then 'convert' it to VBScript. The only change required for it to work from VBScript is the line at the top:

Set oXL = GetObject(, "Excel.Application")

which references the currently running Excel application. You don't need this line in VBA. You will also need to reference an open workbook if you are writing values to cells.

For the error, try

Set HTMLdoc = CreateObject("HTMLFile")
HTMLdoc.body.innerHTML = .responseText
Set Ach = HTMLdoc.getElementById("InformationGrid2")