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
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
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
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.
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
About 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.Follow