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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.