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
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 '--------------------------------------------
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.
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=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.
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.
About The VBA Tutorials Blog
The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.Follow