Copy status value from web page

ssuresh711

New Member
Joined
Feb 6, 2019
Messages
11
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.

HTML:
<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.

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
.
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.


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

Code:
        Do While objIE.Busy Or objIE.readyState <> 4 'READYSTATE_COMPLETE
            DoEvents
        Loop
 
Upvote 0
Are you checking the Busy and readyState properties, something like this?

Code:
        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.
 
Upvote 0
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.
 
Upvote 0
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

Code:
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
 
Upvote 0
Is see that you have this line...

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

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

Code:
'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?

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

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top