Access menubar of another application

kg8299 · April 6th, 2006, 11:08 PM

Hi..

Using vba i want to access the menubar of another application say internet explorer... can anyone tel me how it can be done..

Thanks...

maccas · April 19th, 2006, 06:25 AM

You can't directly access the menubar of other apps (to the best of my knowledge) in the same way in which you can't access the menubar of Excel directly. There are ways around, however.

Firstly and most simply you could try using Application.SendKeys as the following bit of code demonstrates:

Code:

Sub Test()

    Application.SendKeys Keys:="^c", Wait:=True
    Application.SendKeys Keys:="{DOWN}", Wait:=True
    Application.SendKeys Keys:="^v", Wait:=True
    Application.SendKeys Keys:="{ESC}", Wait:=True

End Sub

Most apps, IE included, can be controlled almost exclusively via a series of keystrokes so you should be able to automate behaviors. This said SendKeys is not the most robust way of dealing with this as other applications can give themselves focus at unexpected times (e.g. Outlook reminders) thereby destroying your program flow.

Another way round is to control the other app via automation. This method restricts your choice of application to be controlled somewhat as you will need a COM object model for the other app that you can automate. Fortunately a lot of MS programs have this, MS Office and IE included. If you specifically want to control Internet Explorer via automation then write back and I can can give you some more detailed code examples.

Hope this helps,
Maccas

kg8299 · April 19th, 2006, 10:44 PM

hi..

thanks for the immediate response.. yes.. i am tryin to automate IE..

I want to
1) access the menu bar of IE
2) click on a hyperlink.. which is hidden.. means it wont be listed in the links of the form..
3) select the checkboxes in the page.. which are again hidden..
4) right click to get the context menu and select an option from that

hope u wil be able to give me some clue for this..

thanks

maccas · April 20th, 2006, 10:56 AM

Like I said in previous posts you can't interact with the menus as they are merely part of the user interface. We can however, normally!, programmatically access the same functionality via interacting with the object model.

Unless you post with slightly more specific requirements for what you want your program to do then I'm going to find it hard to help you any more.

Please find attached a bit of code which domonstrates how to navigate to a webpage, click a link on the page, select an option from a combobox, press a button and then extract the text from the page.

Code:

Public Sub IETest()

Dim IE As SHDocVw.InternetExplorer
Dim htmlDoc As MSHTML.HTMLDocument
Dim hCol As MSHTML.IHTMLElementCollection
Dim hSel As MSHTML.HTMLSelectElement
Dim hInp As MSHTML.HTMLInputElement
Dim bReturn As Boolean

    ' Open up Internet Explorer
    Set IE = New SHDocVw.InternetExplorer
    IE.Visible = True
    IE.navigate "http://www.google.co.uk"

    ' Wait for page to load
    WaitForLoad IE

    ' Get the HTML Document
    Set htmlDoc = IE.document

    ' Nagvigate to the News page
    bReturn = ClickLink("News", htmlDoc, IE)

    ' Get the collection of Select elements on the page
    Set hCol = htmlDoc.getElementsByTagName("SELECT")

    ' Find the one specifying which characters we need and pull them out
    For Each hSel In hCol

        If hSel.Name = "ned" Then
            hSel.selectedIndex = 1
            Exit For
        End If

    Next hSel

    ' Get the collection of input elements on the page
    Set hCol = htmlDoc.getElementsByTagName("INPUT")

    ' Find the one specifying which characters we need and pull them out
    For Each hInp In hCol

        If hInp.Name = "btn" Then
            hInp.Click
        End If

    Next hInp

    ' Wait for page to load
    WaitForLoad IE

    ' Show text of page
    MsgBox htmlDoc.body.innerText

End Sub

Private Function ClickLink(LinkText As String, hDoc As MSHTML.HTMLDocument, IExp As SHDocVw.InternetExplorer) As Boolean
' Simulates clicking a link with the input LinkText on the open webpage

Dim hCol As MSHTML.IHTMLElementCollection
Dim hAnch As MSHTML.HTMLAnchorElement
Dim Ans As Boolean

    Ans = False

    ' Get the collection of links on the page
    Set hCol = hDoc.getElementsByTagName("a")

    ' Find the one specifying which characters we need and pull them out
    For Each hAnch In hCol

        If InStr(1, hAnch.innerHTML, LinkText, vbBinaryCompare) > 0 Then
            hAnch.Click
            Ans = True
            Exit For
        End If

    Next hAnch

    ' Dereference our variables
    Set hAnch = Nothing
    Set hCol = Nothing

    ' Wait for page to load
    If Ans Then WaitForLoad IExp

    ClickLink = Ans

End Function

Private Sub WaitForLoad(IExp As SHDocVw.InternetExplorer)
' Wait for page to load before continuing

Dim sngTime As Single

Const MaxTime As Integer = 10

    sngTime = Timer

    ' Wait until the webpage is doing something ...
    Do Until IExp.readyState <> READYSTATE_COMPLETE
        DoEvents
        If Timer > sngTime + MaxTime And Left(Trim(IExp.StatusText), 4) = "Done" Then Exit Sub
    Loop

    ' ... and then wait for it to finish
    Do Until IExp.readyState = READYSTATE_COMPLETE
        DoEvents
        If Timer > sngTime + MaxTime And Left(Trim(IExp.StatusText), 4) = "Done" Then Exit Sub
    Loop

End Sub

raj_phoenix · April 26th, 2006, 04:47 AM

Hi maccas

The piece of code you provided is wonderful. I didn't know that you can access IE with VBA. It's really fantastic. So, is it possible to enter the username and password from vba to login to a secure page. I am trying to get a data from my website's content management system by logging in every time and click 100's of button just to create a simple report.

I wonder if you could provide me some help with this. Your help would be greatly appreciated.

Many Thanks
Jann

maccas · April 26th, 2006, 04:59 AM

Jann,

I think what you mean by logging into a secure webpage is the separate logon form that pops up prompting username and password. I've never tried to code for the interaction with this form so I'm not totally sure.

The first thing I'd do is get a bit of code that will take you up to the point at which you're being prompted for username and password. I'd then debug the code (by setting a breakpoint or other such methods) and have a look at the objects exposed at that point in time and their methods and properties (in the Locals window) to see if there is any way in which you might interact with the logon form via the object model.

I suspect that there will be no way to do what I've just suggested as this is a security feature (but you never know until you investigate). If you can't do anything with the object model then you will have to resort to the brute force method of using Application.SendKeys. This should get you past this obstacle.

Sorry I can't provide a definitive answer but I'm pretty busy at work atm and I've never tried this before so don't know the answer off the top of my head.

Hope this helps. Post back with an update report as I'd be interested to know how this pans out.

Maccas

maccas · April 26th, 2006, 06:22 AM

Actually, I've had a chance to have a quick look at this and I couldn't find a way of interacting with the object model properly. I could, however, get the SendKeys method working. Please find attached some demonstration code. Clearly to get this working you will need a valid webpage, logon UserName and Password (the supplied ones are all dummies)

Code:

Option Explicit

Public Sub IETest()

Dim IE As SHDocVw.InternetExplorer
Dim htmlDoc As MSHTML.HTMLDocument
Dim bReturn As Boolean

Const PWord As String = "Test"
Const UserName As String = "Me"

    ' Open up Internet Explorer
    Set IE = New SHDocVw.InternetExplorer
    IE.Visible = True
    IE.navigate "http://securelogonsite.com/default.aspx"

    ' Pause for 3 seconds
    Application.Wait (Now + TimeValue("00:00:03"))

    ' Use SendKeys to Logon
    Application.SendKeys Keys:=PWord, Wait:=True
    Application.SendKeys Keys:="+{TAB}", Wait:=True
    Application.SendKeys Keys:=UserName, Wait:=True
    Application.SendKeys Keys:="+{TAB}", Wait:=True
    Application.SendKeys Keys:="+{TAB}", Wait:=True
    Application.SendKeys Keys:="{ENTER}", Wait:=True

    ' Wait for the page to load
    WaitForLoad IE

    ' Get the HTML Document
    Set htmlDoc = IE.document

    ' Show text of page
    MsgBox htmlDoc.body.innerText

End Sub

Private Function ClickLink(LinkText As String, hDoc As MSHTML.HTMLDocument, IExp As SHDocVw.InternetExplorer) As Boolean
' Simulates clicking a link with the input LinkText on the open webpage

Dim hCol As MSHTML.IHTMLElementCollection
Dim hAnch As MSHTML.HTMLAnchorElement
Dim Ans As Boolean

    Ans = False

    ' Get the collection of links on the page
    Set hCol = hDoc.getElementsByTagName("a")

    ' Find the one specifying which characters we need and pull them out
    For Each hAnch In hCol

        If InStr(1, hAnch.innerHTML, LinkText, vbBinaryCompare) > 0 Then
            hAnch.Click
            Ans = True
            Exit For
        End If

    Next hAnch

    ' Dereference our variables
    Set hAnch = Nothing
    Set hCol = Nothing

    ' Wait for page to load
    If Ans Then WaitForLoad IExp

    ClickLink = Ans

End Function

Private Sub WaitForLoad(IExp As SHDocVw.InternetExplorer)
' Wait for page to load before continuing

Dim sngTime As Single

Const MaxTime As Integer = 10

    sngTime = Timer

    ' Wait until the webpage is doing something ...
    Do Until IExp.readyState <> READYSTATE_COMPLETE
        DoEvents
        If Timer > sngTime + MaxTime And Left(Trim(IExp.StatusText), 4) = "Done" Then Exit Sub
    Loop

    ' ... and then wait for it to finish
    Do Until IExp.readyState = READYSTATE_COMPLETE
        DoEvents
        If Timer > sngTime + MaxTime And Left(Trim(IExp.StatusText), 4) = "Done" Then Exit Sub
    Loop

End Sub

raj_phoenix · April 26th, 2006, 07:26 AM

Hi maccas

Many Thanks, it worked very well, exactly what I expected. You are really a genius. Once again thank you very much for all your help.

Regards
Jann

kg8299 · April 26th, 2006, 08:30 PM

hi..

that was just too good.. just what i wanted.. thanks a lot..

one last query... i want to right click on my frame and click view source and then save the notepad. Is there anyway i can automate this process?

Hope you have some clue..

maccas · April 27th, 2006, 02:28 AM

Just use

Code:

htmlDoc.body.innerHTML

once the page has loaded. This will give you the text of the source code in a string variable.

If you really want it dumped into notepad you can use

Code:

Shell("notepad.exe", vbNormalFocus)

to open notepad and SendKeys to write. Or you could create a text file by using file I/O commands (see VBA help for "Print" or you could use Windows Script Host and FileSystemObeject)