Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access wab page with excel VBA
I am using excel vba to access our intranet site.
I am entering login id and password thru vba. Now I need to click a button on the same page but no matter what I do, I failed and need your help. I need to click (programetically by vba) on the view button which in turn launch a java function of the html source. here is the vba code I assambled. Sub IE_NCC_ICC() Dim MYURL As String 'Dim IE As SHDocVw.InternetExplorer Dim ie As InternetExplorer Dim ipf As Object ' Open Internet Explorer application Set ie = CreateObject("InternetExplorer.Application") With ie ..Visible = True ..Navigate ("http://10.10.10.10:8000/vms/jsp/ChangeBatchStatus.jsp") ' Loop until the page is fully loaded Do Until .ReadyState = 4 DoEvents Loop Do Until ie.ReadyState = READYSTATE_COMPLETE DoEvents Loop Do Until ie.ReadyState = READYSTATE_COMPLETE DoEvents If InStr(1, ie.StatusText, "Done") _ 0 Then Exit Do Loop '*** HTML SOURCE CODE 'RADIO BUTTON ALL VOUCHERS name="radRange" checked onClick="displayVouchers(); ' name="radRange" value="Selected" onClick="displayVouchers(); 'FROM name="txtFrom" 'TO name="txtTo" 'VIEW <INPUT TYPE="button" name="Submit" value=" View " onClick="nextPage();" 'UPDATE <INPUT TYPE="button" name="Submit" value=" Update " onClick="changeActive();" Dim els As Object, e As Object, opt As Object Set els = ie.Document.getElementsByName("radRange") For Each e In els If e.Type = "radio" And e.Value = "Selected" Then e.Checked = True Debug.Print "Checked option: '" & e.Value & "'" Exit For End If Next e Set ipf = ie.Document.all.Item("cboBatchNumber") ipf.Value = "52585" Set ipf = ie.Document.all.Item("cboStatus") ipf.Value = 1 '"ACTIVE" Set ipf = ie.Document.all.Item("txtFrom") ipf.Value = "300" Set ipf = ie.Document.all.Item("txtTo") ipf.Value = "49" ' *** THIS IS THE PROBLEM AREA '.Item("radRange").Value("Selected") = Checked 'ie.Document.Item("radRange").Value("Selected") = Checked 'Set ie.Document.Item("radRange").Value = "Selected" 'Set ipf = ie.Document.all.Item("radRange") ' 'ipf.Click '.JAVASCRIPT "displayVouchers()" 'ipf.Value = "Selected" 'ipf.Select ' = True 'MsgBox ipf.Type 'ipf.Checked = True Set ipf = ie.Document.all.Item(" View ") ipf.Value = " View " ipf.Click Button.Click ..Navigate ("http://10.10.10.10:8000/vms/jsp/ NextUpdateVoucherStatus.jsp") Set ipf = ie.Document.all.Item("Submit") ipf.Value = " View " ipf.Click ipf.SUBMIT 'Set IPF = IE.Document.all.Item("cboBatchNumber") 'IPF.Value = "50545" 'Set IPF = IE.Document.all.Item("cboBatchNumber") 'IPF.Value = "50545" 'http://10.10.10.10:8000/vms/jsp/NextUpdateVoucherStatus.jsp 'http://10.10.10.10:8000/vms/jsp/ChangeToActive.jsp End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access wab page with excel VBA
Hi Madiya
The code below might help you out. The first way would be to select the element by the ID or Name and then click which is great if you are working with a button or a set control that is always the same. You can also run a loop through the links on the page until you find the one you are looking for and then click it, this is a little more messy but it will work if you need to click on a link that is controlled by javascript etc. example 1 ie.document.all.Item("btnlogin").Click 'View the web source code to get the ID or Name of the control then put it inside the quotations in the brackets. example 2 For Each Lnk In ie.Document.Links 'Debug.Print Mid(Lnk.outerHTML, 10) If Mid(Lnk.outerHTML, 10, 6) = "Submit" Then Lnk.Click End If Next 'You can change the parameters of the Mid to use more or less of the links HTML and of course it doesn't have to be the word submit you look for, the debug line can be used to display the different HTML links in the immediate window. Hope this helps you out Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access wab page with excel VBA
On Dec 10, 10:28 pm, Incidental wrote:
Hi Madiya The code below might help you out. The first way would be to select the element by the ID or Name and then click which is great if you are working with a button or a set control that is always the same. You can also run a loop through the links on the page until you find the one you are looking for and then click it, this is a little more messy but it will work if you need to click on a link that is controlled by javascript etc. example 1 ie.document.all.Item("btnlogin").Click 'View the web source code to get the ID or Name of the control then put it inside the quotations in the brackets. example 2 For Each Lnk In ie.Document.Links 'Debug.Print Mid(Lnk.outerHTML, 10) If Mid(Lnk.outerHTML, 10, 6) = "Submit" Then Lnk.Click End If Next 'You can change the parameters of the Mid to use more or less of the links HTML and of course it doesn't have to be the word submit you look for, the debug line can be used to display the different HTML links in the immediate window. Hope this helps you out Steve Hi Steve, Thanks for your help. When I tried ie.document.all.Item("btnlogin").Click 1. with value " View ", I got "object variable or with block variable not set" error. 2. with name "Submit", I got "Object doesn't support this property or method" error. I substitute btnlogin with " View " and "Submit". Any thoughts? Regards, Madiya |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access wab page with excel VBA
Hi Madiya
If you view the source code for the web page you should be able to find the ID to the button, the HTML below is from an intranet site that holds a basic login form that requires a user name and password, then a button click to start the login with the data given or to reset the form. <form name="login" method="POST" action="/Basic/login.asp" <pUsername: <input name="txtUserName" type="text" id="txtUserName" size="10" maxlength="10" </p <pPassword: <input name="txtPassword" type="password" id="txtPassword" size="10" maxlength="10" </p <p <input type="reset" name="Reset" value="Reset" <input name="Submit" type="submit" id="Submit" value="Submit" in this case if i wanted to automatically control this page i would set an object for the things i need, one for the Username and password and one for the button, i can set the object to reference an item on the web page by setting the object to the ID of the webpage control ... Set MyObject = ie.document.all.Item("txtPassword") then i can use that control MyObject.value="drowssap" as for the buttons i don't need to set them to an object but i do however need to call them by the correct ID ie.document.all.Item("Submit").click 'Where Submit is the button ID taken from the HTML code. however you need to remember to clean up after yourself if you are creating and setting objects, when you are done with them set them back to nothing. I normally do this at the end of the function or sub so i always know where to look to see if they have been reset... Set NameCbo = Nothing Set PsWord = Nothing Set Ref = Nothing Set InstLnk = Nothing Set Lnk = Nothing I hope this clears things up for a you a bit but if not let me know and i shall see what i can come up with. Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access wab page with excel VBA
When trying to get a handle on objects on a web page, I've used the
Mouseover DOM Inspector at http://slayeroffice.com/tools/modi/v2.0/modi_help.html It's a bookmarklet that allows you to mouse over any object on a web page - frame, button, link, whatever - and will show you all the available attributes. You can often get the name, the ID number, the container frame, and other information. Ed On Dec 11, 5:55 am, Incidental wrote: Hi Madiya If you view the source code for the web page you should be able to find the ID to the button, the HTML below is from an intranet site that holds a basic login form that requires a user name and password, then a button click to start the login with the data given or to reset the form. <form name="login" method="POST" action="/Basic/login.asp" <pUsername: <input name="txtUserName" type="text" id="txtUserName" size="10" maxlength="10" </p <pPassword: <input name="txtPassword" type="password" id="txtPassword" size="10" maxlength="10" </p <p <input type="reset" name="Reset" value="Reset" <input name="Submit" type="submit" id="Submit" value="Submit" in this case if i wanted to automatically control this page i would set an object for the things i need, one for the Username and password and one for the button, i can set the object to reference an item on the web page by setting the object to the ID of the webpage control ... Set MyObject = ie.document.all.Item("txtPassword") then i can use that control MyObject.value="drowssap" as for the buttons i don't need to set them to an object but i do however need to call them by the correct ID ie.document.all.Item("Submit").click 'Where Submit is the button ID taken from the HTML code. however you need to remember to clean up after yourself if you are creating and setting objects, when you are done with them set them back to nothing. I normally do this at the end of the function or sub so i always know where to look to see if they have been reset... Set NameCbo = Nothing Set PsWord = Nothing Set Ref = Nothing Set InstLnk = Nothing Set Lnk = Nothing I hope this clears things up for a you a bit but if not let me know and i shall see what i can come up with. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to limit access to a page in Excel to one person? | New Users to Excel | |||
VBA From MS Access: Inserting Page Break? | Excel Programming | |||
create excel spreadsheet on a data access page | Excel Discussion (Misc queries) | |||
Why do Excel and Access freeze on the splash page? | Excel Discussion (Misc queries) | |||
is it possible for an html/asp page to access Excel directly | Excel Programming |