Getting specific cell in web table and paste in excel

[TaskyFuji]

Good morningy, everyone!

I need another help in vba code, please. I need to get a specific cell content from a web table and paste it into excel. Been trying different things but nothing is working. The VBA code i wrote below was a code i got for another table scraping but it's to get the whole table (if getting the whole table were to work, it would still be acceptable) and I don't know how to specify the cell. However, the code still didn't get the correct table that i need (this page has multiple tables that has the same class name ("listTable). I tried the table class "tr0" but i got a runtime error

Run-time error '91':

Object variable or With block variable not set


I only need the content within the "zip" (in this example, i need 11542). Here is the html code, i hope it's enough. Also, please see VBA code so far. Thank you!

<div class="divPageSectionContent" id="HSCProteins">   
<table id="Proteins" class="listTable" style="null">
    <tbody><tr class="tr1">
       
        <th nowrap="" style="width: 10%;">Row #</th>
        <th nowrap="" style="width: 10%;">Protein Role</th>
       
            <th nowrap="">MPIN</th>
       
        <th nowrap="" style="width: 10%;">Serv Protein/Status
        </th>
        <th nowrap="" style="width: 10%;">XIN</th>
        <th nowrap="" style="width: 10%;">Address 1</th>
        <th nowrap="" style="width: 10%;">City</th>
        <th nowrap="" style="width: 10%;">State</th>
        <th nowrap="" style="width: 10%;">Zip</th>
        <th nowrap="" style="width: 10%;">Specialty</th>
       
    </tr>

        <tr class="tr0">
               
            <td>
              
                1
            </td>
            <td>Fac, Requesting</td>
            <td>
               
                000123456
               
            </td>
           
            <td>GLENDALE/INN</td>
           
            <td id="Fet ID">12345678</td>
            <td id="address1">Sample Address</td>
            <td id="city">GLENDALE</td>
            <td align="center" id="state">NY</td>
            <td align="center" id="zip">11542</td>
           
            <td id="specialtyType">&nbsp;</td>
           
        </tr>
        <tr id="medNec0" style="display: none" class="tr1">
            <td colspan="100%">
                <table>
                    <tbody>

Private Sub CommandButton3_Click()
'Pull Zip
'dimension (set aside memory for) our variables
    Dim mySh As Worksheet, table As Object, tr, td, trCounter%, _
tdCounter%, IE As Object, doc As HTMLDocument
Dim objLink As Object


'Launch IE and wait until fully loaded
    Set IE = New InternetExplorerMedium
    IE.Visible = True
    IE.navigate "https://icue.uhc.com/icue/hsr.uhg"
   
    Do While IE.Busy Or IE.readyState <> 4
        Application.Wait DateAdd("s", 1, Now)
    Loop

'Copy Ref# from Audit Sheet
    Worksheets("Audit Sheet").Activate
    Worksheets("Audit Sheet").Range("B2").Select
    Selection.Copy

'Search Ref# in Site
    Set doc = IE.document
   
    doc.getElementById("4").Click
   
        Do While IE.Busy Or IE.readyState <> 4
        Application.Wait DateAdd("s", 1, Now)
       
      Loop
     
    doc.getElementById("searchID").Value = ActiveCell.Value
    doc.getElementById("filterButton").Click

        Do While IE.Busy Or IE.readyState <> 4
        Application.Wait DateAdd("s", 1, Now)
       
      Loop
     
      Set mySh = ThisWorkbook.Sheets("Search Sheet")
      mySh.Activate
     
    Set table = doc.getElementsByClassName("tr0")
    For trCounter = 0 To table(0).Rows.Length - 1
    For tdCounter = 0 To table(0).Rows(trCounter).Cells.Length - 1
        mySh.Cells(trCounter + 1, tdCounter + 1) = _
        table(0).Rows(trCounter).Cells(tdCounter).innerText
    Next
Next
   
End Sub

 

[Saurabhj]

Hi, I am unable to open the site mentioned but trying to modify VBA code using information provided.

Do you need only first zip code or all zip codes available in site ?

 

[TaskyFuji]

Hi, I am unable to open the site mentioned but trying to modify VBA code using information provided.

Do you need only first zip code or all zip codes available in site ?

Oh sorry, it's actually a protected site. forgot to mention and/or mask. ? I just need the zip in that line provided and not all zip in site. Thanks!

 

[Saurabhj]

Hi,

Please check below code (unable to check)

Sub siteZip()
    On Error Resume Next
    
    Dim HTMLDoc As New HTMLDocument
    Dim ieBrowser As New InternetExplorer
      
    'To open and show Internet Explorer
    ieBrowser.Visible = True
    
    'To Open website in Internet Explorer
    ieBrowser.navigate "https://icue.uhc.com/icue/hsr.uhg"
    
    Do
    ' Wait till the Browser is loaded
    Loop Until ieBrowser.readyState = READYSTATE_COMPLETE
    
    Set HTMLDoc = ieBrowser.document
    
    MsgBox HTMLDoc.getElementById("zip")(0).innerText
    
    Set ieBrowser = Nothing
    Set HTMLDoc = Nothing
End Sub

 

[TaskyFuji]

Hi,

Please check below code (unable to check)

Sub siteZip()
    On Error Resume Next
   
    Dim HTMLDoc As New HTMLDocument
    Dim ieBrowser As New InternetExplorer
     
    'To open and show Internet Explorer
    ieBrowser.Visible = True
   
    'To Open website in Internet Explorer
    ieBrowser.navigate "https://icue.uhc.com/icue/hsr.uhg"
   
    Do
    ' Wait till the Browser is loaded
    Loop Until ieBrowser.readyState = READYSTATE_COMPLETE
   
    Set HTMLDoc = ieBrowser.document
   
    MsgBox HTMLDoc.getElementById("zip")(0).innerText
   
    Set ieBrowser = Nothing
    Set HTMLDoc = Nothing
End Sub

Hi,

Please check below code (unable to check)

Sub siteZip()
    On Error Resume Next
   
    Dim HTMLDoc As New HTMLDocument
    Dim ieBrowser As New InternetExplorer
     
    'To open and show Internet Explorer
    ieBrowser.Visible = True
   
    'To Open website in Internet Explorer
    ieBrowser.navigate "https://icue.uhc.com/icue/hsr.uhg"
   
    Do
    ' Wait till the Browser is loaded
    Loop Until ieBrowser.readyState = READYSTATE_COMPLETE
   
    Set HTMLDoc = ieBrowser.document
   
    MsgBox HTMLDoc.getElementById("zip")(0).innerText
   
    Set ieBrowser = Nothing
    Set HTMLDoc = Nothing
End Sub

Sorry, been very busy and forgot to respond and I was pulled to other projects. Anyway, thanks! It did pull into the msgbox. I needed it to paste to a cell and still worked. Thanks so much!

 

[Saurabhj]

Thanks for the feedback.