View Full Version : SendKeys With A URL in VBA

02-01-2012, 12:48 PM

I've written some code from Excel using SendKeys to send a URL to a browser and then enter some search terms in relevant boxes. It works 95% of the time and is very happy. However should there be a delay in the webpage opening up, then it's still putting the searchterms in the URL and giving me an error message.
Is there a way of sending a URL and then not continuing the code until that page has fully loaded? I've tried a few seconds delay. But is there something I could do to send something back to the Excel sheet that will trigger off the rest of the Macro (or a new subroutine).

The bit of code I have for this bit is...

Application.Wait (Now + TimeValue("0:00:01"))
SendKeys ("http://MYURL.aspx"), True
Application.Wait (Now + TimeValue("0:00:01"))

SendKeys ("{ENTER}"), True
Application.Wait (Now + TimeValue("0:00:04"))

Other than a longer wait time, how can I improve this bit?


02-01-2012, 02:59 PM
I have written some pages that select "other pages" to be viewed in an iframe- doing this calles my DB and then posts the other page so my page always loads after my display page does... maybe this is more clear...
*note pseudo code*

page "Demo"
selection box
item A
item B
item C
<iframe> source of iframe is page "LoadMe"</iframe>
onSelectionsBox_Change(send View <- object that changed to)
page LoadMe
inner.html = call database and select object "View" (sent from other page call)

the code is more complex obviously- but the logic is there.... by calling the other page you now have to wait for it to finish it's postback before you can postback therefore your page always loads after that page is done loading- does that help?

02-01-2012, 05:44 PM
Thanks for the reply.
I'm relatively new to VB programming so I'm still learning the functions and methods. So various bits of code I see on forums aren't immediately recognisable.
But I see the logic there and will explore and experiment further

Many thanks

02-01-2012, 06:42 PM
Well I think I'm getting there but I'm stumbling a bit now. Can anyone help me please?

I've tried doing it this way which works to a certain point.

Dim ie As InternetExplorer
Set ie = New InternetExplorer
ie.Navigate "http:myurl"

'Loop until ie page is fully loaded
Do Until ie.ReadyState = READYSTATE_COMPLETE

ie.Visible = True

And I can populate the form fields with

ie.Document.getElementById("field1").Value = "SOME DATA"
ie.Document.getElementById("field2").Value = "SOME MORE DATA"

However, my stumbling blocks are...

I want it to put the URL into the existing Activated page that I previously used by AppActivate
So can I replace some of the above code so it sends the URL to a page with the title of .... 'specify title here'?

Also, this bit doesn't seem to work


The form button is actually
<input type="submit" value="Search" class="searchbutton">
and therefore doesn't have an ID. But what is the equivalent of pressing the Submit button that I need for my code?

Many thanks

02-01-2012, 06:51 PM
I am getting confused a little- are you doing explicit VBa? ie vb windows form? what framework are you running under? I don't see how a VB app will have access to a non vb.net webapp ... there might be a way but I have never thought to try it since typically vb.net web apps are separate from vb win apps- I can think on it more though...
*I could be mistaken here- my vb xp is mainly in working with legacy VBA and vb.net webapps... all of my new development is in C# so my quote of typically vb.net apps and vba's are separate may be flawed- but I am pretty sure it is accurate

02-02-2012, 11:13 AM

I'm currently working in Excel using the VB editor to build macros.
My latest tweak does open the URL in a new IE window and populates the fields as I like but I can't quite find out how to combine it with the AppActivate method of finding an already open window, nor have it click the Submit button.

If I can do that bit then I'm well on my way


02-02-2012, 12:39 PM
Nearly there....
I've sorted out the Submit button part. I'll post it here in case anyone else is looking for the same thing.
Name of the Form is searchform
So I'd tried playing around with different variations and finally got this to work..


But I'm still stuck with my code opening up a new Internet Explorer instead of it activating the same one.

Can anyone help me tweak my code with that please?

Dim ie As InternetExplorer
Set ie = New InternetExplorer
ie.Navigate "http:myurl"

'Loop until ie page is fully loaded
Do Until ie.ReadyState = READYSTATE_COMPLETE

ie.Visible = True

I know the Set ie = New InternetExplorer bit opens a new window.
But how do I change this to do the same thing as AppActivate("Title")

Many thanks

02-02-2012, 01:26 PM
this might help you, parts of the process class
get array of processes (http://msdn.microsoft.com/en-us/library/system.diagnostics.process.getcurrentprocess.aspx)
then search through them for ie (array of process[] 'es- in VB Process() )

once you have that process you might be able to use StandardInput (http://msdn.microsoft.com/en-us/library/system.diagnostics.process.standardinput.aspx) and StandardOutput (http://msdn.microsoft.com/en-us/library/system.diagnostics.process.standardoutput.aspx)... still am a little unclear as to "what" your end goal is- either way the Process Class (http://msdn.microsoft.com/en-us/library/system.diagnostics.process.aspx) should help you out...

why don't you modify the web app to use the excel data? MSDN Article (http://support.microsoft.com/default.aspx?scid=kb%3B[LN]%3BQ316934) talking about how to use excel as a datasource

02-02-2012, 01:52 PM
Thank you I will explore all that.

Regarding my end goal... the current process is,
search on the website/database front end...
Go to the Excel sheet and trigger the Macro
This uses AppActivate to focus back to the webpage and extract some info and puts it into Excl

It then goes back to the webpage and puts in a new URL and pastes the data into relevant form fields.

This last bit I've been doing with AppActivate too using SendKeys to tab around to the fields and paste the data into the field. Problem was any delay when loading the webpage would mess things up.
So using this ie. bit of coding above works great except that's it's opening new Browser windows instead of doing it in the current one I'm using.

I need

Set ie = New InternetExplorer

to be tweaked so it's not a New InternetExplorer I want ie set to but the one that is called "This Title" or just current active one.

I can't modify the web app unfortunately. I'm only at front end level for that one.

I'll get there....


02-02-2012, 02:03 PM
Oh, I've managed to get it working with a little help from a code I've found..

This goes before the ie.navigate "http//myurl...."

Dim ie As Object

With CreateObject("Shell.Application").Windows

If .Count > 0 Then
Set ie = .Item(0)
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
End If
End With

But I'm trying to now work out how and why it works...

If you can explain this bit to me that would be great.
Thanks for your help