extract text from internet explorer using VBA

[spq24]

Hello,

I navigate to a site using vba and get to where I need to be using a form. I need the vba to grab something from the HTML and put it into an excel sheet.

<span id="lblCustSiDt" style="text-decoration:underline;">10/20/2011</span>

Above is the html of what I am trying to grab (I'd preferably just want the date)

And this is what I've written in VBA that's not working:


Set textiwant = ie.document.All("lblCustSiDt").innerHTML

Windows("Order Assignment Template").Activate
Sheets("OA").Range("L2") = textiwant


any idea?

 

[John_w]

In what way isn't it working? Do you get any errors? Try debugging using F8 etc.

Try instead:

Dim myText As String
myText = ie.document.All("lblCustSiDt").innerText

Note that innerText and innerHTML are string types and your code is assigning it to an object, which is wrong.

 

[spq24]

with both innerHTML and innerText I'm getting a Run-time error '91': Object variable or With block variable not set

any ideas?

 

[John_w]

Read my post again! My suspicion was correct and I gave the answer.

 

[spq24]

I put what you said in my code and it's not working still getting the same error. How do I get rid of this error?

 

[John_w]

I put what you said in my code and it's not working still getting the same error. How do I get rid of this error?

Please post your entire code, or enough of it for me to reproduce the problem. Put the code between [ code] [ /code] tags, without the space in each tag.

 

[spq24]

<code>

Sub ccomAccess()
Dim str As String
Dim IElocation As String
Dim Acell As Range
Dim myText As String

Sheets("OA").Range("A2").Select

Set Rng = Range(Selection, Selection.End(xlDown))


Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://ccom/Default.aspx"
.Visible = True
End With


Do While ie.Busy
Loop

For Each Acell In Rng
With ie
.navigate "http://ccom/Default.aspx"

ie.document.All("ctl00_PagePlaceholder_lnkbtnSetCri").Click

ie.document.getElementById("ctl00_PagePlaceholder_wndOpenFile_C_txtCustName").Value = Acell

ie.document.getElementById("ctl00_PagePlaceholder_wndOpenFile_C_txtTskSta").Value = "All"

ie.document.All("ctl00_PagePlaceholder_wndOpenFile_C_btnSearch").Click
Do While ie.Busy
Loop

ie.document.All("ctl00_PagePlaceholder_gvCktMap_ctl02_lnkSiteName").Click
Do While ie.Busy
Loop


myText = ie.document.All("lblCustSiDt").innerHTML

Windows("Order Assignment Template").Activate
Sheets("OA").Range("L2") = textiwant

End With
Next Acell

End Sub
</code>

 

[spq24]

I know that code says innerhtml but I tried it with innertext as well and got the same result

 

[John_w]

I can't reproduce the error. The line

.navigate "http://ccom/Default.aspx"

opens IE, but it says 'Internet Explorer cannot display the web page'. When I change it to:

.navigate "http://www.ccom.com/Default.aspx"

IE opens that page, but the code fails at:

IE.document.All("ctl00_PagePlaceholder_lnkbtnSetCri").Click

with the error:
Run-time error 91. Object variable or With Block variable not set

Which means the id or name "ctl00_PagePlaceholder_lnkbtnSetCri" doesn't exist. I confirmed this by looking at the HTML source and that id and any ids like ct100 don't exist.

Since I can't reproduce your exact problem, the only suggestion I can give is to change this part of your code:

ie.document.All("ctl00_PagePlaceholder_wndOpenFile_C_btnSearch").Click
Do While ie.Busy
Loop

ie.document.All("ctl00_PagePlaceholder_gvCktMap_ctl02_lnkSiteName").Click
Do While ie.Busy
Loop

to:

IE.document.All("ctl00_PagePlaceholder_wndOpenFile_C_btnSearch").Click
Do While IE.Busy Or IE.document.readystate <> "complete"
    DoEvents
Loop

IE.document.All("ctl00_PagePlaceholder_gvCktMap_ctl02_lnkSiteName").Click
Do While IE.Busy Or IE.document.readystate <> "complete"
    DoEvents
Loop

myText = IE.document.All("lblCustSiDt").innerText

 

[ujjwal]

How can i fill up the text box automatically of a navigated page using Macros after entering all the fields of the first page and logging in?

For Example (As your code says):

"ie.document.getElementById("ctl00_PagePlaceholder_wndOpenFile_C_txtCustName").Value = Acell

ie.document.getElementById("ctl00_PagePlaceholder_wndOpenFile_C_txtTskSta").Value = "All"

ie.document.All("ctl00_PagePlaceholder_wndOpenFile_C_btnSearch").Click"

This would automatically enter values to the text fields and after clicking on button, I will be directed to a new page.

Now, how do I fill the text box of this new page which I am directed to using Macros.

Please Help.