Web Browser Control and Javascript

A

Arry Potter

Hi,

I have a Javascript code inside a webpage in a webbrowser control on a
excel sheet. There is some value in the javascript which I wish to pass to
the Excel sheet.How can I do this

Excel sheet -> webrowser control
webbrowser control -> opened a webpage
webpage has javascript code -> run code and returns a result
result from javascript -> back to excel sheet cell

Configuration :
WinXP,IE6,Office 2003

Regards,
Arry
 
J

joel

You need to find the value in the IE.Document object. I usally perform a
dump of the IE object like in the code below and try to find the value in the
innertext. Another method is to search for the TAG or ID using these two
statements

Set Form = IE.document.getElementsByTagname("Form")
Set zip5 = IE.document.getElementById("zip5")

Tags in the HTML start with forward slasshes /Form
ID in html look like id="zip5"

You can view the HTML from the IE web browser by going to View - Source.




Sub GetZipCodes()

ZIPCODE = InputBox("Enter 5 digit zipcode : ")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.Readystate <> 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE

Form(0).submit
Do While IE.busy = True And IE.Readystate <> 4
DoEvents
Loop

'----------------------------------------------------------------------
'dump the document to the worksheet
RowCount = 1
For Each itm In IE.document.all
Range("A" & RowCount) = itm.tagname
Range("B" & RowCount) = itm.classname
Range("C" & RowCount) = Left(itm.innertext, 1024)
RowCount = RowCount + 1
Next itm
'---------------------------------------------------------------------------

Set Table = IE.document.getElementsByTagname("Table")
If Table(0).Rows(0).innertext = "" Then
MsgBox ("Invalid Zip code")
Else
Location = Table(0).Rows(2).innertext
End If
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub
 
A

Arry Potter

Dear All,
I have found the solution.Here is a detailed explanaition

Problem : Call VBA routine from Javascript or Pass data from Webpage to
Excel Sheet or passing data from javascript to web browser control

Solution:
To do this add a webbrowser control in excel
OPen the website from which you want to pass the data to the excel
Ask the javascript on this custom page to pass values to the STATUS BAR
From the status bar you can pick it using webbrowser_1 text changed()

Here is an example - sorry cant post the original due to copyright issues

<html>
<body>
<script type="text/javascript">
var a;
a = 10 ;
window.status = a;
</script>
</body>
</html>

Here the javascript is returning value 10 . I pass this value to the status
bar

In excel I write the following -
Private Sub WebBrowser1_StatusTextChange(ByVal Text As String)
If Text <> "" Then
RowCount = 1
If Text <> "done" Then
Range("A" & RowCount) = Text

End If


End If
End Sub

I first check if there is any text changing in status bar once I realize
that text has changed. I caputre the changed text to a row in excel.

Hope this helps others in need

Regards
Arvind
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top