VBA and switching focus between applications

[Paris Hilton]

Hello reader,

I'm a Business Information Administrator with intermediate VBA skills
(Office 2003). I'd like to ask a question and would like to receive complete
sample code, because I'm just not skilled enough to handle it myself. I have
proven that today.

I'll explain:

An employee works with a VBA application (form in Word 2003), side by side
with a page on Internet Explorer. From that webpage he copies manually data
to controls in the userform. This costs too much (time).

My solution would be to build additional functions in VBA. It goes like
this:
1) Press a button on the form.
2) The code behind "button_clicked" event switches focus to the open
Internet Explorer webpage (defined by the windowtitle in he blue area).
3) I use sendkeys CTRL_A then CTRL_Copy to copy the webpage contents to the
clipboard.
4) Event switches back to the VBA form (Word 2003 instance).
5) VBA code analyses the data on the clipboard and copies the right stuff to
the controls.

I'll be honest, I don't have a clue how to do this and I'm ashamed of asking
so much of you: can and will you provide a working skeleton code?
I suspect the Windows API will be involved (have no experience with that)
and it involves handling the clipboard with VBA (also zilch experience).

If you have a better idea then feel free to address it. However, I may not
embed the page within the form.

Greetings,
Val

 

[Valmont]

I have used my playful alias by accident. My appologies.

~ Val

 

[JP]

I have some sample code for automating Internet Explorer here:

http://codeforexcelandoutlook.com/automateinetexplorer.html
http://tinyurl.com/2twmh9

It depends on what type of website you are accessing, but a lot of
them can be accessed via code. Keep in mind it might be less tedious
to push a button, but the VBA code in many cases will be slower.


HTH,
JP

 

[Valmont]

Interesting. I will study this today at my work. Thank you very much.


"JP" <[email protected]> schreef in bericht
I have some sample code for automating Internet Explorer here:

http://codeforexcelandoutlook.com/automateinetexplorer.html
http://tinyurl.com/2twmh9

It depends on what type of website you are accessing, but a lot of
them can be accessed via code. Keep in mind it might be less tedious
to push a button, but the VBA code in many cases will be slower.


HTH,
JP

 

[Valmont]

Well, I came up with this:

Private Sub CommandButton1_Click()

AppActivate "Google - Mozilla Firefox"
SendKeys "^a"
SendKeys "^c"
AppActivate "paste.doc - Microsoft Word"
SendKeys "^v"

End Sub


However, "^V" (pasting) doesn't work although the copied content is in the
clipboard. Any suggestions?

"JP" <[email protected]> schreef in bericht
I have some sample code for automating Internet Explorer here:

http://codeforexcelandoutlook.com/automateinetexplorer.html
http://tinyurl.com/2twmh9

It depends on what type of website you are accessing, but a lot of
them can be accessed via code. Keep in mind it might be less tedious
to push a button, but the VBA code in many cases will be slower.


HTH,
JP

 

[Valmont]

The answer is :
AppActivate "paste.doc - Microsoft Word", True

The next question is: how do I select the entire webpage so all its contents
are copied, not just a randomly - unknown - selected control?
Gosh I feel noob-like .

 

[Valmont]

I need to add that I cannot open IE from within VBA code. The IE page is
always open. So I can't use vba to open IE and navigatie to an url as IE is
always open on the same url, but contents may vary.
So how do I analyse the webpage with VBA - like: what control has the focus
at the moment?
The trick is that I don't want to focus on a single control persé. I
probably need the contents of the entire page.

 

[JP]

You can hook into an existing instance of IE like this:

Dim appIE As Object

On Error Resume Next
Set appIE = GetObject(,"InternetExplorer")
On Error Goto 0

If appIE Is Nothing Then
MsgBox "Cannot find IE"
Exit Sub
End If

HTH,
JP

 

[Valmont]

Nice, I didn't know.
There are multiple instances open. Can I choose which one I want to hook?


"JP" <[email protected]> schreef in bericht
You can hook into an existing instance of IE like this:

Dim appIE As Object

On Error Resume Next
Set appIE = GetObject(,"InternetExplorer")
On Error Goto 0

If appIE Is Nothing Then
MsgBox "Cannot find IE"
Exit Sub
End If

HTH,
JP

 

[Valmont]

Well I've gotten so far: just iterate through open instances. If the URL
matches the URL that the employee uses then I can analyse the document of
that url.
Next thing is to figure out how to iterate through all objects in a html
page...

You helped me great so far. It directs towards the right track.

"JP" <[email protected]> schreef in bericht
You can hook into an existing instance of IE like this:

Dim appIE As Object

On Error Resume Next
Set appIE = GetObject(,"InternetExplorer")
On Error Goto 0

If appIE Is Nothing Then
MsgBox "Cannot find IE"
Exit Sub
End If

HTH,
JP

 

[Valmont]

Funny.

innerText

It's on your site.

Well I've gotten so far: just iterate through open instances. If the URL
matches the URL that the employee uses then I can analyse the document of
that url.
Next thing is to figure out how to iterate through all objects in a html
page...

You helped me great so far. It directs towards the right track.

"JP" <[email protected]> schreef in bericht
You can hook into an existing instance of IE like this:

Dim appIE As Object

On Error Resume Next
Set appIE = GetObject(,"InternetExplorer")
On Error Goto 0

If appIE Is Nothing Then
MsgBox "Cannot find IE"
Exit Sub
End If

HTH,
JP

 

[Valmont]

Well I think I managed to collect all the basics for IE automation thanks to
your directions, hints and your awesome site.
The next task is traversing through all possible tags/elements on a htmlpage
and then learn to handle the value/property of that object. For example
returning all strings of all tags.

As a starter for those who don't have experience with IE automating - like
me - I"ll post my starterscode. From there you should build up your skills.
Last but not least: visit the URL JP posted here. It's bloody worth it.

Private Sub CommandButton1_Click()
'Get all currently open IE and Explorer windows as the employee has multiple
instances open

Dim objShell As Shell
Dim objIE As InternetExplorer
Dim objExplorer As ShellFolderView
'Dim elem As HTMLBody <--- don't know what I can do with this one. To be
explored later
Dim doc As HTMLDocument

Dim obj As Object

Set objShell = New Shell
For Each obj In objShell.Windows
If TypeName(obj.Document) = "HTMLDocument" Then
Set objIE = obj
Set doc = objIE.Document
'Explore the contents of outerText, outerHTML, innerText and
innerHTML.
'First I want to make sure it's the Google page I'm analysing (my
employee has multiple instances open remember?).
If objIE.LocationURL = "http://www.google.nl/" Then
Range("A1") = doc.documentElement.outerText '.outerHTML
'.innerText '.innerHTML
End If
'You can put the contents in a String variable and then parse it with
stringmanipulation-methods like InStr(), Mid(), Left, Right()
'See JP's site for an example, but these methods should be part of your
standarrepertoire anyway.
End If
Next obj
Set objShell = Nothing

End Sub

Thank you very much mr. Pena .

"JP" <[email protected]> schreef in bericht
I have some sample code for automating Internet Explorer here:

http://codeforexcelandoutlook.com/automateinetexplorer.html
http://tinyurl.com/2twmh9

It depends on what type of website you are accessing, but a lot of
them can be accessed via code. Keep in mind it might be less tedious
to push a button, but the VBA code in many cases will be slower.


HTH,
JP

 

[JP]

You are too kind, glad to see things worked out for you.

View>Source is your friend!


--JP