VBA to grab 9 specific pieces of data from a website

bastoe · May 20th 2019

Hello vba gurus, I have the VBA to launch IE, however am having an issue getting started on VBA for grabbing some specific data from a private website. I've searched a bunch of sites, and found some codes that are sort of remotely close, but usually looping through a table of same data, or it's just different enough to short circuit attempts. I have looked into definitions and am gaining some rudiment understanding, but you can probably smell the smoke from my brain after this week.

Anyhow I would appreciate any help with this. The html code for each item is listed below, if it was in a named item/class I included that. There's no bigger container item for the TD items (like a Div).

Here's the data that I need to pull in and put on a spreadsheet starting in A1. The items listed are in the order that they appear on the site with the text area box being at the bottom since it's big. There are page text items and there are field values needed.

HTML

Current Date:
<div class='headerDetails'><b>Friday, May 10, 2019</b></div>
 
Current User:
<br>User:<span class='headerDetailsValues'> mememe </span>




Item Status and ID#:
<div class='reportTitle2' style='width=100%;overflow: auto;'>
<span style='font-size:14px; font-weight:bold;padding-left:25px;float:left;'>ITEM Status: Pending</span>
<span style='font-size:14px; font-weight:bold;padding-right:10px;padding-top:3px;float:right;white-space: nowrap;'>ITEM ID# 123456</span></div>




Value of target Buy date:
<td><input type='text' name='Target_Buy_Date' class='textfield_ro' readonly='readonly' value=05/21/2019></td>




Value of Creator:
<td><input type='text' name='creator' value="The Creator" class='textfield_RO' readonly='readonly'></td>
 
Value of City:
<td><input type='text' name='city' value="The City" class='textfield_ro' readonly='readonly'>




Value of ZIP:
<td><input type='text' name='zip' value="12345" class='textfield_ro' readonly='readonly'></td> 




Value of the Assigned #:
 <td style='font-weight:bold; line-height:90%'>Assigned #:</td> 
 <td><input type='text' name='AssignedNumber'  class="textfield" id='theAssignedNumber_ro' value=''1234567890" maxlength='15'></td> 
                       
Value of the text area comments: 
 <tr><td colspan='5'>Comments:</td></tr> 
  <tr><td colspan='5'>
<textarea name='comments' class='textarea100_ro' readonly='readonly'>Big bunch of comments --has any character free form text - </textarea></td></tr> 
          </table>

Display More

Thanks for any assistance.

bastoe · May 21st 2019

Forgot to mention that this is a .asp site but it does show the page you are on as an id (domain.com/la/dir/page.asp?id=123456)

The part of the code that is failing is on the "Document" object?

sURL is the site, for testing though this will be changed to grab the url from a Cell on another page.

The "If" statement is the only way to get a wait working. The usual "While" method fails and gives error that clients have disconnected.

Code

Private Sub GetHTMLContents()


    'dimension - declare memory for variables
    Dim objIE As InternetExplorer
    Dim ele As Object
    Dim y As Integer
   
    'open IE and wait for it to be ready
    Set objIE = New InternetExplorer
    With objIE
         .Visible = True
         .Navigate sURL
         If objIE.READYSTATE <> 4 Then
           DoEvents
         End If
    End With
    
    'output data to excel starting at row 1
    y = 1
    
   'look at elements with id specified and evaluate each, one at a time, using 'ele' variable


    For Each ele In objIE.document.getElementByClassname("headerDetails").getElementsByTagName("b") '////////this part is the failing line///////////
        'show the text content of 'b' element being looked at
        Debug.Print ele.textContent
       
        'put text of 1st item in col A
        Sheets("Sheet1").Range("A" & y).Value = ele.Children(0).textContent
     
        'increment row counter by 1
        y = y + 1
    'repeat until last ele has been evaluated
    Next

Display More

Every time I reference the word Document or HTMLDocument or IHTMLDocument in that spot it fails.

I've tried with early binding on the internet explorer - as object as well without luck.

Thanks for any assistance!

John_w · May 21st 2019

The trouble with referencing multiple elements in one line, as your For Each line does, is that you can't tell which part is failing. (The error is probably caused by getElementbyClassName - it should be getElementsByClassName.)

Try this for the first four items - the others should be similar. Add a reference to MS HTML Object Library.

Code

Dim HTMLdoc As HTMLdocument


Set HTMLdoc = objIE.document


MsgBox HTMLdoc.getElementsByClassName("headerDetails")(0).innerText 'assuming it's in the first element with class 'headerDetails'
MsgBox HTMLdoc.getElementsByClassName("headerDetailsValues")(0).innerText 'ditto 'headerDetailsValues'


Dim v As Variant
v = Split(HTMLdoc.getElementsByClassName("reportTitle2")(0).innerText,vbCrLf)
MsgBox v(0) & " " v(1)


MsgBox HTMLdoc.getElementsByName("Target_Buy_Date")(0).value

Display More

bastoe · May 22nd 2019

omg ima no0b, hopefully it's that simple, this is what happens when you stop coding for a few years and try to jump back in....will let you know tomorrow, thanks John!!!

bastoe · May 22nd 2019

A few errors, First tried just adding the s i missed, but that gave a generic error. so next substituted the code below- made notes on the errors right below the lines. Thanks for your help John, this is a challenging task.

Code

Dim HTMLdoc As HTMLDocument
    Set HTMLdoc = objIE.document
    '///Error here: objIE.document = <the remote server machine does not exsist or is unavailable>


    MsgBox HTMLdoc.getElementsByClassName("headerDetails")(0).innerText 'assuming it's in the first element with class 'headerDetails'
    MsgBox HTMLdoc.getElementsByClassName("headerDetailsValues")(0).innerText 'ditto 'headerDetailsValues'
    Dim v As Variant
    v = Split(HTMLdoc.getElementsByClassName("reportTitle2")(0).innerText, vbCrLf)
    MsgBox v(0) & " "[B] v[/B](1)
    '///on the second[B] v[/B], getting compile error (as a popup), expected end of statement
    
    MsgBox HTMLdoc.getElementsByName("Target_Buy_Date")(0).Value

Display More

If this is impossible like this, i may have to try to open ie and save the page so i can get it into excel.

bastoe · May 24th 2019

Seems this runtime error 462 <the remote server machine does not exsist or is unavailable> is the underlying issue with all code trying to grab my site. The code works for other sites.

The site itself is using Javascript to pull in form data into its text boxes.

Even these below are giving the error, so solve the error, and all different types should work. Ug....watch it be something simple...please post any ideas, thanks!!!

Code

IE.ExecWB 17, 0 IE.ExecWB 12, 2  
 &     
 IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT  IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

bastoe · May 31st 2019

I had to resort to send keys to just copy the entire page. The site really is a userform where most of the data I wanted was in textboxes. Seems it is un-get-able any other way. Once it was in the spreadsheet, I just looped through the textboxes and copied the values to another sheet. From that point it was all formatting. Thanks for trying folks.

VBA to grab 9 specific pieces of data from a website

Participate now!

Share