Introduction | AppActivate | explorer.exe Folders | Closing Thoughts

Introduction

If you’re a follower of the wellsr.com VBA Tutorials Blog, you already know how to open a program, file or folder with VBA Shell. But, let’s say you have a program that opens a file and you run it again. What happens? A new instance of the program annoyingly appears.

Wouldn’t it be great if, instead of opening a new instance, you could activate an application that you previously opened? Today, I’ll show you two ways to do just that - keep reading to discover how!

  • Method 1 will teach you how to reactivate a previously opened program using the AppActivate function. This will work for all programs and applications, except for those opened using explorer.exe. Again, this will not work to open folders you originally opened with explorer.exe. See Method 2 for ways to work with folders.
  • Method 2 will show you how to reactivate windows explorer folders you previously opened with explorer.exe. For example, you can use it to reactivate an existing My Documents window instead of opening a new instance.

These two methods are adaptations of the concepts introduced in my article on opening and closing files with VBA Shell. If you want to learn some basics about opening and closing files, I encourage you to check out that article.


Method 1 - AppActivate

Public vPID As Variant
Public Sub OpenApplication()
    'Launch application if not already open
    If vPID = 0 Then 'Application not already open
101:
        vPID = Shell("C:\Windows\system32\notepad.exe", vbNormalFocus)
    Else 'Application already open so reactivate
        On Error GoTo 101
        AppActivate (vPID)
    End If
End Sub

Tutorial

Because the variable vPID is stored as a project level Public Variable, its value will be retained for as long as your instance of Excel (or other Microsoft Office application) is open.

If no value is assigned to the variable, a new instance of whatever program you want to open will appear. In this case, notepad.exe was opened.

The magic happens when you run this program a second time. Instead of opening a second notepad window, the previously opened window will activate! The AppActivate statement makes all this happen.

Normally, the AppActivate statement is used to activate an existing application based on its title. For example, if you have an open program called “Calculator,” you can type AppActivate ("Calculator") to activate that window. Fortunately, the AppActivate Function can also accept a process ID instead of a title, which makes it far more robust!

Again, the AppActivate example will not work if trying to reactivate a folder you previously opened with explorer.exe. Keep reading for folder-compatible solution.


Method 2 - explorer.exe Folders

Public Sub OpenExplorer()
    'Launch folder if not already open
    Dim strDirectory As String
    Dim pID As Variant, sh As Variant
    strDirectory = "C:\Users\Ryan\Documents"
    On Error GoTo 102:
    Set sh = CreateObject("shell.application")
    For Each w In sh.Windows
        If w.document.folder.self.Path = strDirectory Then 'if already open, activate it
            w.Visible = False
            w.Visible = True
            Exit Sub
        End If
    Next
    'if you get here, the folder isn't open so open it
    pID = Shell("explorer.exe " & strDirectory, vbNormalFocus)
102:
End Sub

Tutorial

Recall in my VBA Shell article, I had some pretty heavy disclaimers about using explorer.exe to open folders. The reason I made those disclaimers is that the Process ID, vPID, for explorer.exe is not retained after your folder opens. Trying to activate the window via the stored vPID will do nothing! No need to give up, though. The above example is the solution.

In the explorer.exe example, the script opens my My Documents folder. Change the path in the strDirectory variable to whatever folder path you want to open.

The macro works by looping through all your open windows. If the path in your Windows Explorer top bar matches the path in your macro, that window will activate. If the macro can’t find the path, it will open it. The next time you run the macro, the previously opened window will activate!

You may wonder why the w.Visible=False and w.Visible=True lines appear back to back. It’s been my experience that without first hiding the window by setting its visibility to False, the window sometimes would not reactivate. Your mileage may vary, so play around and see what you think.


Closing Thoughts

Since I published my VBA Shell article earlier this year, I’ve received several emails asking how to prevent the macros from opening multiple instances of the same application when re-executed. I’m hoping the skills you learned here will help you solve this problem.

This article is another example of how your emails and your comments are an important avenue for great VBA content ideas. Who knows, a question you ask in the comments today may turn into an article tomorrow!

As always, subscribe to my VBA Tutorials blog or follow me on Google+ for more great VBA content. Pass this article along to your buddies and don’t be afraid to show off your new skills.

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too


Your time is valuable. It's time to become a VBA expert.

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.