Script Center >
Scripting Forums
>
The Official Scripting Guys Forum!
>
Excel executes keystrokes in IE
Excel executes keystrokes in IE
- I am a fair VB / VBA programmer, but I know all too little about coding web pages. I need Excel to interact with a web page frame generated by a Java script that also uses Java to generate a report. I believe I know how to get Excel to launch the web page. The frame has two cells for entry of query parameters. The page source code does not give identifiers for the cells. I can access them with by repeatedly tabbing, so I'd like to get code to execute multiple Tab keystrokes on the web page, I know how to transfer the data to the cells. One more tab moves the focus to a button to submit the report request. This would require executing the equivalent of a Return (I assume it's the same script but with a different ASCII character code) .I'm hoping someone can provide sample scripting.
Answers
- It can be done using the Sendkeys method (see Excel VBA help documentation), but I wouldn't recommend that approach. Rather I'd use the more direct DHTML approach illustrated in this little code example ...
sURL = "www.somewhere.com/somefolder/some.html"
with CreateObject("InternetExplorer.Application")
.Navigate("http://" & sURL)
Do until .ReadyState = 4 : WScript.Sleep 100 : Loop
With .document
set cControls = .all.tags("input")
nControls = cControls.length
nIdx = 0
for each control in cControls
s = s & nIdx & ", " & control.type & vbNewLine
next
wsh.echo "Number of Controls:", nControls, vbNewline, s
End With ' document
End With ' IE
This is written as a standalone WSH script, but will also work, as written, as part of an Excel macro. In that environment, the late binding to IE could be converted to an early binding (Dim statement).
In your case, the control type can be matched and/or the control's index in the array be used to determine which control was which. Once this is know it is a simple matter to use that index to apply the correct input to the correct control. For example, say the two query inputs are found to be indexed as 0 and 1 respectively and the submit button is element 2, then this would do the job ...
cControls(0).value = "First input text"
cControls(1).value = "Second input text"
cControls(2).click
HTH,
Tom Lavedas- Edited byTom Lavedas Friday, November 13, 2009 1:25 PMfix minor type
- Edited byTom Lavedas Friday, November 13, 2009 1:28 PMfix the code
- Marked As Answer byIamMredMSFT, OwnerMonday, January 11, 2010 4:54 AM
All Replies
- It can be done using the Sendkeys method (see Excel VBA help documentation), but I wouldn't recommend that approach. Rather I'd use the more direct DHTML approach illustrated in this little code example ...
sURL = "www.somewhere.com/somefolder/some.html"
with CreateObject("InternetExplorer.Application")
.Navigate("http://" & sURL)
Do until .ReadyState = 4 : WScript.Sleep 100 : Loop
With .document
set cControls = .all.tags("input")
nControls = cControls.length
nIdx = 0
for each control in cControls
s = s & nIdx & ", " & control.type & vbNewLine
next
wsh.echo "Number of Controls:", nControls, vbNewline, s
End With ' document
End With ' IE
This is written as a standalone WSH script, but will also work, as written, as part of an Excel macro. In that environment, the late binding to IE could be converted to an early binding (Dim statement).
In your case, the control type can be matched and/or the control's index in the array be used to determine which control was which. Once this is know it is a simple matter to use that index to apply the correct input to the correct control. For example, say the two query inputs are found to be indexed as 0 and 1 respectively and the submit button is element 2, then this would do the job ...
cControls(0).value = "First input text"
cControls(1).value = "Second input text"
cControls(2).click
HTH,
Tom Lavedas- Edited byTom Lavedas Friday, November 13, 2009 1:25 PMfix minor type
- Edited byTom Lavedas Friday, November 13, 2009 1:28 PMfix the code
- Marked As Answer byIamMredMSFT, OwnerMonday, January 11, 2010 4:54 AM
