How to use Java in Excel VBA?

[Sirin]

Hi,
On our intranet, we have a wab page where we can type a
doc number and click on submit button. When we click on
submit button the doc no is sent to the server and server
in return sends the most recent version of that document
to the wab page.
I want to get the latest version no for series of doc nos
listed in excel file.
I succeded in opening the pageand typing the doc number
but unable to click on the submit button
programatically.The error is runtime error no 91 " object
variable or with block variable not set"
So far I have this.
Sub NEW_DOC_NO()
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://docser.gis.com/webapp/jsp/nochang
e/YourNewNumber.jsp"
Do Until Not .Busy
DoEvents
Loop
Set ipf = ie.document.all.Item("OLD_MDN")
ipf.Value = "02232601093"
Set ipf = ie.document.all.Item("button")
ipf.Value = "Submit" <<<<ERROR LINE >>>>
ipf.Click

Do Until Not .Busy
DoEvents
Loop
End With

ie.ExecWB 17, 2
ie.ExecWB 12, 0

Range("A1").Select
ActiveSheet.Paste
End Sub

The submit button uses the following two java codes.
code1

<input type="button" name="Submit" class="buttonskeen"
value=" Submit " onclick="javascript:validateData()">

code 2

{

document.Mainform.action"http://docser.gis.com/webapp/jsp/n
ochange/YourNewNumber.jsp";
document.Mainform.submit();
}

May I know whats wrong with my code?
Any help is much appreciated.
Thanks.
Sirin

 

[Joe Fawcett]

Hi,
On our intranet, we have a wab page where we can type a
doc number and click on submit button. When we click on
submit button the doc no is sent to the server and server
in return sends the most recent version of that document
to the wab page.
I want to get the latest version no for series of doc nos
listed in excel file.
I succeded in opening the pageand typing the doc number
but unable to click on the submit button
programatically.The error is runtime error no 91 " object
variable or with block variable not set"
So far I have this.
Sub NEW_DOC_NO()
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://docser.gis.com/webapp/jsp/nochang
e/YourNewNumber.jsp"
Do Until Not .Busy
DoEvents
Loop
Set ipf = ie.document.all.Item("OLD_MDN")
ipf.Value = "02232601093"
Set ipf = ie.document.all.Item("button")
ipf.Value = "Submit" <<<<ERROR LINE >>>>
ipf.Click

Do Until Not .Busy
DoEvents
Loop
End With

ie.ExecWB 17, 2
ie.ExecWB 12, 0

Range("A1").Select
ActiveSheet.Paste
End Sub

The submit button uses the following two java codes.
code1

<input type="button" name="Submit" class="buttonskeen"
value=" Submit " onclick="javascript:validateData()">

code 2

{

document.Mainform.action"http://docser.gis.com/webapp/jsp/n
ochange/YourNewNumber.jsp";
document.Mainform.submit();
}

May I know whats wrong with my code?
Any help is much appreciated.
Thanks.
Sirin

Unless your form does something very complicated I wouldn't do it this way
with IE automation but use xmlHttp request. There are lots of posts in
Google groups about this. However in your example you have two choices,
either call the button's click event or submit the form yourself:

With ie
.Visible = True
.Navigate
"http://docser.gis.com/webapp/jsp/nochange/YourNewNumber.jsp"
'Do Until Not .Busy
'DoEvents
'Loop
Do Until .readyState = 4 'Complete 'I've found this more reliable
than busy property
DoEvents
Loop
Set ipf = ie.document.all.Item("OLD_MDN")
ipf.Value = "02232601093"
Set ipf = ie.document.all.Item("button")
Call ipf.Click()
'OR instead of the above TWO lines
Call ipf.form.submit()
Do Until .readyState = 4 'Complete 'I've found this more reliable
than busy property
DoEvents
Loop
End With

ie.ExecWB 17, 2
ie.ExecWB 12, 0

Range("A1").Select
ActiveSheet.Paste
End Sub