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

 

[John_w]

Do you think,putting the parsing part into the regular VBA code would speed up the code?

No it won't. My suggestion of using pure VBA to retrieve and parse the data is so that you can use the Excel VBA environment to develop and debug the code. Get it all working in Excel first and then adapt it to work with VBScript.

Your code is currently using VBA to create and run a VBScript file and without a VBScript development tool, debugging the VBScript program will be difficult; the best you could do is to put Wscript.Echo statements in the code to output the value of variables, etc., or log them to a file with FileSystemObject methods.

Also the URL http://npts2.apis.dhl.com:6010/npts/ShipmentDataFetchServlet?action=8&shipDetFromSumm= seems to be an intranet site, so I can't really help with retrieving or parsing the data from it.

 

[Lalit Das]

I'm working on some code that i found from:Multi-threaded VBA - Excel Hero Blog
(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

Thanks alot..able to perform action in tables...thankss