PDA

View Full Version : Excel VBA Internet Explorer Java Automation



joshhazel
12-18-2009, 02:45 PM
I explored for hours to locate some sample code of how to user Excel VBA to interact with a webpage that includes Java... it was full of fail, as were my posts on other DB.

h ttps://edmv-lts.dot.state.nc.us/lts/wfm?BF=LTS_COM&FT=NONE&CP=TRANS_EXPRESS&PC=COMPLETE&AC=SUBMIT

Here is an example of a site that uses java (take out the space after the h in the URL). Its obviously very easy to create a new IE window and navigate to the page... but nobody can seem to tell me how to be able to click the "Submit" button on this page without using SendKeys.

In addition, the second page after clicking Submit has an option button that would also need to be selected.

If anyone knows how to use VBA to interact with IE site that uses java, ANY information would be appreciated!

Here is what i have thus far...


Dim ie As InternetExplorer
Set ie = New InternetExplorer
With ie
.Navigate "SameAsAbove"
Do While .Busy
DoEvents
Loop
.Width = 800
.Height = 640
.Visible = True
Do Until .ReadyState = 4
DoEvents
Loop

joshhazel
12-29-2009, 08:00 PM
bump?

joshhazel
01-04-2010, 07:46 PM
I guess there is no solution =(

joshhazel
01-11-2010, 07:57 PM
Long forgotten post

markmrw
01-12-2010, 06:02 AM
i am no java expert (in the slightest)

but as an idead, could you use the submit button's url
looks like to me it is:
javascript:submitForm('/lts/wfm;jsessionid=5c309bffc6742334053053e5a1921132e6c3?CODE=1263301132111&AC=SUBMIT','images/');


maybe possibly, i dunno, just a thought

Mark

Shred Dude
04-23-2010, 12:38 PM
This works...gets you to the next screen...


ie.document.getelementbyid("submitButton").click

joshhazel
04-28-2010, 11:24 AM
This works...gets you to the next screen...


ie.document.getelementbyid("submitButton").click

Thats incredible it worked ... now i just got to see if I can automate the option buttons on the next page and such ... will post back if i have any trouble!

joshhazel
04-28-2010, 12:06 PM
Okay so I have unsuccessfully attempted to select a "radio" option button on the second page, after the Submit button is clicked on the first page ... any suggestions?

Here is what I tried so far...



ie.Document.getElementById("loginType").Value = "Y"
ie.Document.getElementById("loginType").Value = True
ie.Document.getElementById("loginRadioButton").Value = "Y"
ie.Document.getElementById("loginRadioButton").Value = True
ie.Document.getElementsByName("loginType").Item(0).Value = "Y"
ie.Document.getElementsByName("loginType").Item(1).Value = "Y"
ie.Document.getElementsByName("loginType").Item(0).Value = True
ie.Document.getElementsByName("loginType").Item(1).Value = True

ie.Document.getElementsByName("loginRadioButton").Item(0).Value = "Y"
ie.Document.getElementsByName("loginRadioButton").Item(1).Value = "Y"
ie.Document.getElementsByName("loginRadioButton").Item(0).Value = True
ie.Document.getElementsByName("loginRadioButton").Item(1).Value = True

Shred Dude
04-28-2010, 12:59 PM
Tricky set up on this site. the methods of waiting for the page to be ready before proceeding were not consistently effective for me so I resorted to a different method of confirming the existence of a page's object before proceeding.

The following routine works for me all the way through login. Just substitute you userID and password and you should be in.



Public Sub ie()
Dim ieapp As Object
Dim strURL As String
Dim x As Object


strURL = "https://edmv-lts.dot.state.nc.us/lts/wfm?BF=LTS_COM&FT=NONE&CP=TRANS_EXPRESS&PC=COMPLETE&AC=SUBMIT"

Set ieapp = CreateObject("internetexplorer.Application")

With ieapp
.Visible = True
.navigate strURL

While .busy Or .readystate <> 4
DoEvents
Wend

.document.getelementbyid("submitButton").Click

'wait for loging button
On Error Resume Next
Do Until Not x Is Nothing
Set x = .document.getelementbyid("loginradioButton")
Loop
Set x = Nothing
On Error GoTo 0

'Click the appropriate Radio Button, andthen Submit
.document.getelementbyid("loginRadioButton").Click
.document.getelementbyid("submitButton").Click

'Coming to Login Screen...Wait for it...

On Error Resume Next
Do Until Not x Is Nothing
Set x = .document.getelementbyid("userId")
Loop
Set x = Nothing
On Error GoTo 0

'Proceed with populating Userid and password
.document.getelementbyid("userId").Value = "myusername"
.document.getelementbyid("password").Value = "mypassword"

'Click Submit
.document.getelementbyid("submitButton").Click

'should now be at the next page


End With
ieapp.Quit
Set ieapp = Nothing

End Sub

joshhazel
04-29-2010, 11:30 AM
It seems to work well ... Do you have any suggestions on where I can learn some of this stuff online websites perhaps, as you see I made some educated guesses but perhaps you have saved some neat websites with IE automation info?

Crocus Crow
04-29-2010, 12:52 PM
This code:


On Error Resume Next
Do Until Not x Is Nothing
Set x = .document.getelementbyid("loginradioButton")
Loop
Set x = Nothing
On Error GoTo 0and the similar code further down can be replaced by:

While .busy Or .readystate <> 4: DoEvents: Wend
While .document.readystate <> "complete": DoEvents: Wend
As for writing IE automation code, it's easier if you use the objects in the MS Internet Controls library and the HTML Object Library rather than late binding with generic Object variables. I would post some links if the forum allowed me, but my post count is less than 5 and I can't. Search the Google index instead.

Shred Dude
04-29-2010, 09:05 PM
I would agree that your code would often be the preferable route.

This particular web site however, employs several redirects that foil monitoring the document object's readystate, and the browser's busy status. Doing so results in attempting to address an object that's not yet there. The browser flags busy=false when the first query is completed, well before the final document is rendered, and the first document delivered is not the one the user sees/needs.

That is why I suggested the approach of monitoring for the existence of the needed object, for this particular web site.

harshil042
01-30-2014, 07:32 AM
Hi all,

I am using VBA to navigate through the website - this is the coding

Sub newportcarreg()

Dim lot As String

Set objie = CreateObject("internetExplorer.application")

With objie
.Visible = False
.navigate (not able to post the link but the code to the button is at the bottom - also you could type in newport auction in gogle and you will get the link)

Do While .busy Or _
.readystate <> 4
DoEvents
Loop

Do Until ActiveCell.Value = ""
lot = ActiveCell.Value
Sheet1.Range("a1").End(xlDown).Offset(1, 0).Value = .document.getElementById(lot).innertext
ActiveCell.Offset(1, 0).Select
Loop

End With
'
End Sub


With this code I am able to go to the website and pull the information from the 1st page. But I am unable to navigate to page 2 and pull the data from page 2 because the button on the website is not exactly a button that I can use a click command in VBA. Its a href tag with java scritp - <a href="javascript:__doPostBack('GridView1','Page$2')">2</a>

Can any1 help me and let me how I can navigate to page 2 through VBA?


Thank you
Harshil

westconn1
01-30-2014, 01:41 PM
you should have started a new thread for this, not post on multiple old threads

please use code tags when posting code

you could try the fireevent "onclick" of the button element, but i can not say if it will work without testing, as the javascript is not actually in the onclick event

harshil042
01-31-2014, 01:43 AM
hello westconn1,

I am sorry, i am new to forums. I dont know what are code tags and i dont even know how to start a new thread. I have tried the "onclick" event and it dosnt work as you mentioned the javascript is not actually in the onclick event. Do you have any clue as to how i could go about this?

westconn1
01-31-2014, 02:56 AM
you can try the execscript method of the documents parentwindow


wb.document.parentwindow.execscript "loadmax('tree')"

where wb is a webbrowser or instance of IE, and loadmax is the script name, with a parameter of tree
i believe in the above case the script name would be __dopostback, but some experimentation may be necessary


I am sorry, i am new to forums.if you can write code to automate webpages, you should be able to figure out forum posting

harshil042
01-31-2014, 03:16 AM
ok thanks