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 the VBA Shell command. 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.

July 2018 Update: The original Method 2 OpenExplorer macro would reactivate windows explorer folders, but it wouldn’t bring them to the foreground if the folders were minimized. The macro for activating Windows Explorer was improved during this update and now gives you complete control over opening and activating folders using VBA.

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

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

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

'--------------------------------------------
Private Const SW_RESTORE = 9

#If VBA7 Then
    Private Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
    Private Declare PtrSafe Function IsIconic Lib "user32.dll" (ByVal hwnd As Long) As Long
#Else
    Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
    Private Declare Function IsIconic Lib "user32.dll" (ByVal hwnd As Long) As Long
#End If

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
            If CBool(IsIconic(w.hwnd)) Then ' If it's minimized, show it
                w.Visible = False
                w.Visible = True
                ShowWindow w.hwnd, SW_RESTORE
            Else
                w.Visible = False
                w.Visible = True
            End If
            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 on our [VBA Q&A] platform today may turn into an article tomorrow!

When you’re ready to take your VBA to the next level, subscribe using the form below.