Copy status value from web page

[ssuresh711]

Hello Everyone,

I am not expert in VBA. Requesting your support to complete my macro. In the webpage I want to copy the status "Awaiting Business Review" in my excel sheet.

Below is my HTML Code.. I cannot disclose the website address. Apologize for that.

<div title="" class="" id="scr_as_142209" systemid="sf_status_71128" screentype="FIELD"<!-- we always display cf's main div (div above)so we can re-enable disabled cf after auto-saved form reloads; we hide field if it's disabled --><table class ="cfHolder"><tbody><tr><td class="cfLabel" styel="vertical-align: top;"><span cflabel="Status" labelsystemid="sf_status">Status</span><div style="font-size: 70%;"></div></td><td class="cfValue" id="scr_as_142209_value" style="width: 70%; vertical-align: top;">Awaiting Business Review********> removeableFormElement.push('customFields[5].simpleValue');*********></td></tr></tbody></table></div>

</div>

Below is my VBA Code.

With objIE.document

        Set element = .getElementsByTagName("td")
        For Each f In element
        If (f.getAttribute("id") = "scr_as_142209_value") Then
            Sheet1.Cells(2, 4) = f.innerText
            Exit For
        End If


        Next f


        End With

.

 

[Domenic]

Can you tell us what the problem is?

 

[ssuresh711]

Can you tell us what the problem is?

Hi Domenic


I didn't get any error actually. But no data pasted in the excel sheet. Actually the webpage having status as "Awaiting Business Review". I need copy that status and pasted into my excel sheet.


​

 

[Domenic]

Are you checking the Busy and readyState properties, something like this?

        Do While objIE.Busy Or objIE.readyState <> 4 'READYSTATE_COMPLETE
            DoEvents
        Loop

 

[ssuresh711]

Are you checking the Busy and readyState properties, something like this?

        Do While objIE.Busy Or objIE.readyState <> 4 'READYSTATE_COMPLETE
            DoEvents
        Loop

I think I was not define my question correctly. Sorry for that. In that website, there is a field named "Status". Everytime the status may change depend upon the input. I have input in my excel. When I run a macro, the followings things are happening
1. Application (Website) is opening automatically
2. Data (From excel) is copying and paste into that website in a search field and click search button
3. Now, the macro should copy the data from the status field and paste into my excel sheet.

But the 3rd point is not happening. I didn't even get any error.

Hope you understand.

 

[Domenic]

Since I don't have access to the website, I'm not sure that I can help. In any case, I would suggest that post the code that you have and I'll have a look, maybe someone else here might be able to help as well.

 

[ssuresh711]

Since I don't have access to the website, I'm not sure that I can help. In any case, I would suggest that post the code that you have and I'll have a look, maybe someone else here might be able to help as well.

Here is the code. Sorry. Unable to share the website address as it has limited access

Option Explicit
Sub Pending_Track()
Dim HTML As HTMLDocument
Dim objIE, elements, doc, colTR, colTD, td, tr As Object
Dim y As Integer
Dim result As String
Dim ie, elems, e, ele, f As Object
Dim op, x, h As Integer
Dim Var, var2 As String
Dim element As Object










Set objIE = New InternetExplorer


objIE.Visible = True


objIE.navigate "https://dummy.html"


Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop




Application.Wait (Now + TimeValue("0:00:02"))
x = 2
h = 2


Workbooks("Pending Tracker.xlsm").Worksheets("Sheet1").Activate
'For op = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
     
    Var = Cells(2, 1).Value
        
    objIE.document.getElementById("quickSearchTextField").Value = Var
            
    'wait for page to load


Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
        
    With objIE.document


        Set elems = .getElementsByTagName("span")
        For Each e In elems
        If (e.getAttribute("id") = "searchDataIcon") Then
            e.Click
            Exit For
        End If


        Next e


        End With
        
   Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop


Application.Wait (Now + TimeValue("0:00:02"))


Application.Wait (Now + TimeValue("0:00:02"))




        
Set HTML = objIE.document


Set elements = HTML.getElementsByTagName("tr")(0).getElementsByTagName("td")


For Each element In elements
If (element.getAttribute("id") = "scr_as_142209_value") Then
Workbooks("Pending Tracker.xlsm").Worksheets("Sheet1").Activate
Sheet1.Cells(h, 4) = element.innerHTML
Exit For
End If


Next element

Next op


End Sub

 

[Domenic]

Is see that you have this line...

objIE.document.getElementById("quickSearchTextField").Value = Var

...that enters the search term on the page. And then you have this line...

'wait for page to load

...which says to wait for the page to load, but I don't see where you've clicked on a button. Or is it this section of code that you're referring to?

[COLOR=#574123]        If (e.getAttribute("id") = "searchDataIcon") Then
[/COLOR]            e.Click
            Exit For
 [COLOR=#574123]        End If[/COLOR]