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 SubMake 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.
Tutorial
Because the variable
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,
In the explorer.exe example, the script opens my My Documents folder. Change the path in the
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.
