VBA Coding to open Windows Explorer with a particular location without opening multiple instances of that.

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
This code is great cause I can tell it to open Windows Explorer to a particular location.
The problem with it is it just keeps opening Windows Explorer every time it is ran.

What I want it to do is have it check to see if there is a Windows Explorer already opened with that location and if there is, put the Windows highlight on that particular one.

How may I change the below to do this?

Public Sub OpenWindowsExplorer(sFullFolderPath As String)
On Error GoTo Err_Proc

  Shell "C:\WINDOWS\explorer.exe """ & sFullFolderPath & "", vbNormalFocus

Exit_Proc:
  Exit Sub
  
Err_Proc:
  Call LogError_feo(Err, Err.Description, "modCommon @ OpenWindowsExplorer")
  Resume Exit_Proc
  
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
something like this should worked for you

Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName As Any) As LongPtr
Private Declare PtrSafe Function ShowWindow Lib "user32.dll" (ByVal hwnd As LongPtr, ByVal nCmdShow As LongPtr) As LongPtr

Private Const SW_RESTORE As LongPtr = 9

Public Sub OpenWindowsExplorer(sFullFolderPath As String)
    On Error GoTo Err_Proc

    Dim THandle As LongPtr
    Dim tmp As String, tmpArr() As String
    
    tmpArr = Split(sFullFolderPath, "\", , vbTextCompare)
    If UBound(tmpArr) = -1 Then
        tmp = ""
    Else
        tmp = tmpArr(UBound(tmpArr))
    End If
    
    THandle = FindWindow(vbNullString, tmp)
    If THandle = 0 Then
        Shell "C:\WINDOWS\explorer.exe """ & sFullFolderPath & "", vbNormalFocus
    Else
        ShowWindow THandle, SW_RESTORE
        BringWindowToTop THandle
    End If
    
Exit_Proc:
  Exit Sub
  
Err_Proc:
  Call LogError_feo(Err, Err.Description, "modCommon @ OpenWindowsExplorer")
  Resume Exit_Proc
  
End Sub

Open in new window

Author

Commented:
I think we are close.  The only error I get is for line no:  BringWindowToTop THandle
please note image.

Also how can I change the above to allow for both 32 bit and 64 bit applications to run it without throwing an error?
Capture.PNG
Bill PrewTest your restores, not your backups...
Top Expert 2016
Commented:
Give this a try, it adds the BringWindowToTop api, and also the code to determine if it needs to run in 32 or 64 bit mode.

Keep in mind that as written, the code only checks the last nod of the full path.  So the following will open two instances of Explorer:

   OpenWindowsExplorer "c:\temp"
    OpenWindowsExplorer "c:\test"


But this will only open one (since the second call will match the already open Explorer and "use" that.

   OpenWindowsExplorer "c:\test1\temp"
    OpenWindowsExplorer "c:\test2\temp"


#If VBA7 Then
    Private Const SW_RESTORE As LongPtr = 9
    Private Declare PtrSafe Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function ShowWindow Lib "user32.dll" (ByVal hwnd As LongPtr, ByVal nCmdShow As LongPtr) As LongPtr
    Private Declare PtrSafe Function BringWindowToTop Lib "USER32" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Private Const SW_RESTORE As Long = 9
    Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function ShowWindow Lib "user32.dll" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
    Private Declare Function BringWindowToTop Lib "USER32" (ByVal hwnd As Long) As Long
#End If

Sub Test()
    OpenWindowsExplorer "c:\temp"
    OpenWindowsExplorer "c:\test"
End Sub


Public Sub OpenWindowsExplorer(sFullFolderPath As String)
    On Error GoTo Err_Proc

#If VBA7 Then
    Dim THandle As LongPtr
#Else
    Dim THandle As Long
#End If

    Dim tmp As String, tmpArr() As String
    
    tmpArr = Split(sFullFolderPath, "\", , vbTextCompare)
    If UBound(tmpArr) = -1 Then
        tmp = ""
    Else
        tmp = tmpArr(UBound(tmpArr))
    End If
    
    THandle = FindWindow(vbNullString, tmp)
    If THandle = 0 Then
        Shell "C:\WINDOWS\explorer.exe """ & sFullFolderPath & "", vbNormalFocus
    Else
        ShowWindow THandle, SW_RESTORE
        BringWindowToTop THandle
    End If
    
Exit_Proc:
  Exit Sub
  
Err_Proc:
  Call LogError_feo(err, err.Description, "modCommon @ OpenWindowsExplorer")
  Resume Exit_Proc
  
End Sub


Sub LogError_feo(err, desc, text)
    MsgBox err & vbCrLf & desc & vbCrLf & text
End Sub

Open in new window


»bp
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Mark EdwardsChief Technology Officer

Commented:
Stephen:  You didn't say if you wanted to use an already open Explorer (regardless of the path it has), or if you wanted to use an open Explorer ONLY if it has the same path.

You may be able to figure out how to change the code based on what you want to do.

Author

Commented:
I think we are getting even warmer now Bill.  So now it works with both 32-bit and 64-bit.

However if I simply do this once in Immediate Window.

Call OpenWindowsExplorer("C:\XOM\SPTTemp")

Open in new window


as it opens Windows Explorer correctly.... then if I do it a second time  do that same call - it opens up another Windows instead of opening the first one.

Author

Commented:
Mark:  I want to use an already open Explorer - that has the same path.   If it's not opened - obviously then open a new Windows Explorer.  however if it's opened already I don't want to open another and then another every time a call is made.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Odd, it works fine in a test here.  It doesn't open the Explorer a second time.

After the first Call, does the Explorer title bar look like this:
sshot-11.png
»bp
Mark EdwardsChief Technology Officer

Commented:
I put Bill's code in a new Windows 10/Access 2016 database module and ran test with both calls to the same folder and got the following:
Two Explorer Windows:
1st:
First-Explorer-Window.PNG2nd:
Second-Explorer-Window.PNG
Back to the drawing board......
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Dunno if it matters, I did my test from Excel VBA.


»bp
Mark EdwardsChief Technology Officer

Commented:
It should be noted that if you step thru the code, then it works as expected, it selects the already open window and gives it the focus.
Looks like a "DoEvents" or need a timer delay of some sort issue.

I'll work on it...

Author

Commented:
No it just keeps opening it.

I'll put the code back as to what I have again:

#If VBA7 Then
  Private Const SW_RESTORE As LongPtr = 9
  Private Declare PtrSafe Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
  Private Declare PtrSafe Function ShowWindow Lib "user32.dll" (ByVal hwnd As LongPtr, ByVal nCmdShow As LongPtr) As LongPtr
  Private Declare PtrSafe Function BringWindowToTop Lib "USER32" (ByVal hwnd As LongPtr) As LongPtr
#Else
  Private Const SW_RESTORE As Long = 9
  Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
  Private Declare Function ShowWindow Lib "user32.dll" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
  Private Declare Function BringWindowToTop Lib "USER32" (ByVal hwnd As Long) As Long
#End If

Public Sub OpenWindowsExplorer(sFullFolderPath As String)
On Error GoTo Err_Proc

  #If VBA7 Then
  Dim THandle As LongPtr
  #Else
  Dim THandle As Long
  #End If
  Dim tmp As String, tmpArr() As String
  
  tmpArr = Split(sFullFolderPath, "\", , vbTextCompare)
  If UBound(tmpArr) = -1 Then
    tmp = ""
  Else
    tmp = tmpArr(UBound(tmpArr))
  End If
  
  THandle = FindWindow(vbNullString, tmp)
  If THandle = 0 Then
    Shell "C:\WINDOWS\explorer.exe """ & sFullFolderPath & "", vbNormalFocus
  Else
    ShowWindow THandle, SW_RESTORE
    BringWindowToTop THandle
  End If
    
Exit_Proc:
  Exit Sub
  
Err_Proc:
  Call LogError_feo(Err, Err.Description, "modCommon @ OpenWindowsExplorer")
  Resume Exit_Proc
  
End Sub

Open in new window

just-keeps-opening-it.PNG
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Yes, it does take some time for Explorer to open, and if the next Call is made before it is "alive" then it would make sense that a second one could be created.

Could add an intelligent wait loop waiting for the new instances of Explorer to render I suppose also...


»bp
Mark EdwardsChief Technology Officer

Commented:
OK.  Tried the following code in the Test procedure and it worked:
Sub Test()
    Dim x As Integer
    OpenWindowsExplorer "c:\AAA"
    For x = 1 To 2000
    DoEvents
    Next x
    OpenWindowsExplorer "c:\AAA"
End Sub

Open in new window

Looks like a delay between calls did the trick.  Under normal circumstances (time lag between 1st and 2nd opening), the code should work without the delay.
Mark EdwardsChief Technology Officer

Commented:
It should also be noted that the folder path that is used MUST exists, otherwise you get two explorer windows with the default path indicated.

Author

Commented:
To give some more clarification the matter....

I run VBA code that creates a file.  Then once that file is created - I close out of Microsoft Excel with Application.Quit.  But right before I run the OpenWindowsExplorer code to open Windows Explorer to that spot.

Let's say I open up the Microsoft Excel VBA .xlsm file again.  Run the macro - the OpenWindowsExplorer code is ran.... Application.Quit - it opens another separate Windows Explorer - multiple times as shown in my image above.

So a delay is not going to help in the matter.  I already have a severe delay between running the code already.
Mark EdwardsChief Technology Officer

Commented:
OK.  To get conditions to be more like Stephen described above, I opened my Access db and ran the Test procedure with just ONE call to the code to open Windows Explorer using a folder path (not a file path), and got a window.
I then closed the db file, left the Explorer window open, and then reopened the db file and ran the Test procedure again with the same folder path - it worked as wanted - it did NOT open a 2nd window to the same folder.

Now I'm going to try a full file path and see what happens if I open the full path file and try again.
Mark EdwardsChief Technology Officer

Commented:
OK.  Tried a file path (Excel file) and the code opened the file in Excel - not Windows Explorer.

Author

Commented:
This is also how I'm calling the sub:

'placed in a global module.
Public Const g_sLocalPath As String = "C:\XOM\SPTTemp\"
'placed in another global module.
Public Const g_sLocalAddPath As String = "EMRM Financials"

Call OpenWindowsExplorer(g_sLocalPath & g_sLocalAddPath)

Open in new window


Please note also called in Microsoft Excel not Microsoft Access application.
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
So for the most part it works as expected for Mark and Bill, but not for Stephen.  I wonder what's different there?

Also, I suspect it doesn't matter, but I did notice what I think is a small bug in the original posted code.  I think this line:

Shell "C:\WINDOWS\explorer.exe """ & sFullFolderPath & "", vbNormalFocus

should have been:

Shell "C:\WINDOWS\explorer.exe """ & sFullFolderPath & """", vbNormalFocus

to add the trailing double quote around the pathname.


»bp
Mark EdwardsChief Technology Officer

Commented:
OK Using a folder path, I don't get a 2nd Explorer window.  I.e. can 't duplicate what the author is getting, so I've got a feeling there is something else going on that we don't know about....
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
The fact that you hav a space in the folder name, and given the lack of the closing double quote, could be causing a new window to always be opened.  The code may be matching "EMRM Financials" to "EMRM" and failing.

Just another thing to look at...


»bp

Author

Commented:
When I step through the code... THandle = 0  is always 0 no matter what I do.

Even despite changing the Shell "C" with the double quotes, Bill.
Mark EdwardsChief Technology Officer

Commented:
Both the original code and Bill's modified code is working for me - I'm getting the same result.
Mark EdwardsChief Technology Officer

Commented:
Stephen:  Try stepping thru the code and seeing if the value of the "tmp" variable is the same for the code line:
THandle = FindWindow(vbNullString, tmp)

Open in new window

If it is different in any way, you'll get a 2nd window.
Mark EdwardsChief Technology Officer

Commented:
Just saw Stephen's post on the code he's using.  I'll try that...
Access or Excel shouldn't make a difference (you would think), but stranger things have happened...
Mark EdwardsChief Technology Officer

Commented:
OK, I setup a set of folders on my C drive just like Stephen and everything worked as expected - I only got 1 Explorer window.

Author

Commented:
wow.  Can you send me a sample of the file Mark?  I'll just copy and replace that in my application what you have.
Mark EdwardsChief Technology Officer

Commented:
OK.  I've attached my Access file, and here's the code in the module - it's the only code in the whole file.
Option Compare Database
Option Explicit

'placed in a global module.
Public Const g_sLocalPath As String = "C:\AAA\BBB\"
'placed in another global module.
Public Const g_sLocalAddPath As String = "CCC CCC"

#If VBA7 Then
    Private Const SW_RESTORE As LongPtr = 9
    Private Declare PtrSafe Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function ShowWindow Lib "user32.dll" (ByVal hwnd As LongPtr, ByVal nCmdShow As LongPtr) As LongPtr
    Private Declare PtrSafe Function BringWindowToTop Lib "USER32" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Private Const SW_RESTORE As Long = 9
    Private Declare Function FindWindow Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function ShowWindow Lib "user32.dll" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
    Private Declare Function BringWindowToTop Lib "USER32" (ByVal hwnd As Long) As Long
#End If

Sub Test()
    'the public constants are defined in the declarations section above.
    Call OpenWindowsExplorer(g_sLocalPath & g_sLocalAddPath)
'    Dim x As Integer
'    OpenWindowsExplorer "c:\AAA\BBB\CCC CCC"
'    For x = 1 To 2000
'    DoEvents
'    Next x
'    OpenWindowsExplorer "c:\AA"
End Sub


Public Sub OpenWindowsExplorer(sFullFolderPath As String)
    On Error GoTo Err_Proc

#If VBA7 Then
    Dim THandle As LongPtr
#Else
    Dim THandle As Long
#End If

    Dim x As Integer
    
    Dim tmp As String, tmpArr() As String
    
'    For x = 1 To 2000
'    DoEvents
'    Next x
    
    tmpArr = Split(sFullFolderPath, "\", , vbTextCompare)
    If UBound(tmpArr) = -1 Then
        tmp = ""
    Else
        tmp = tmpArr(UBound(tmpArr))
    End If
    
'    For x = 1 To 2000
'    DoEvents
'    Next x
    
    THandle = FindWindow(vbNullString, tmp)
    Debug.Print tmp
    If THandle = 0 Then
        Shell "C:\WINDOWS\explorer.exe """ & sFullFolderPath & """", vbNormalFocus
    Else
        ShowWindow THandle, SW_RESTORE
        BringWindowToTop THandle
    End If
    
Exit_Proc:
  Exit Sub
  
Err_Proc:
  Call LogError_feo(err, err.Description, "modCommon @ OpenWindowsExplorer")
  Resume Exit_Proc
  
End Sub


Sub LogError_feo(err, desc, text)
    MsgBox err & vbCrLf & desc & vbCrLf & text
End Sub

Open in new window

HandlingWindowsExplorer.accdb
Mark EdwardsChief Technology Officer

Commented:
Stephen:  Keep in mind that if the folder path used is invalid (doesn't exists), then you will always get a new Explorer window because Explorer will default to the default folder (displayed in the title bar) and that won't match the folder name the code is looking for.
Be sure to note the name of the folder in the Explorer window title bar - that's what the code is looking for.
Mark EdwardsChief Technology Officer

Commented:
OK.  I put my code in an Excel 2016 workbook and ran it.  The first time, instead of opening Explorer to the folder "C:\XOM\SPTTemp\EMRM Financials",the Explorer window opened here:
Excel-Explorer-Window.PNG
Go figure.... "Documents" is NOT "EMRM Financials".....

The folder "C:\XOM\SPTTemp\EMRM Financials" DOES exists on my machine...

Works fine in Access though...
Mark EdwardsChief Technology Officer

Commented:
OK.  I navigated the 1st Explorer window to "EMRM Financials" and ran the code a 2nd time, and, again, it opened Explorer to "Documents" - it did not find "EMRM Financials"..

hmmmmmmm…… looks like we need to figure out what's going on with Excel... why it is NOT working.
I'll try another path and see if it works....
Mark EdwardsChief Technology Officer

Commented:
Interesting still...  I tried a new folder "C:\AAA\BBB\CCC CCC" with the same excel code and it WORKED AS EXPECTED!!!!!
I only got ONE Explorer window.

OK, I'm going to shut up and see what someone else comes up with.
Stephen:  Setup another folder path and see if that works.... and always check what folder name is showing in the Explorer title bar.

Author

Commented:
Can you provide the Excel sample you were working with also please?
Mark EdwardsChief Technology Officer

Commented:
Mark EdwardsChief Technology Officer

Commented:
OK.  On a hunch, I moved the code from a basic module to a sheet module and ran the code for "C:\XOM\SPTTemp\EMRM Financials" and only got ONE Explorer window!

I've attached the Excel file.
Book1---Code-In-Sheet-Module.xlsm

Author

Commented:
OK so just verifying for you... this code you just provided does not work in Excel but is working in MS Access right?

The key problem as you step through this is THandle = 0....
we need it to get to this ShowWindow THandle, SW_RESTORE for it to work properly.

If THandle = 0 Then
        Shell "C:\WINDOWS\explorer.exe """ & sFullFolderPath & """", vbNormalFocus
    Else
        ShowWindow THandle, SW_RESTORE
        BringWindowToTop THandle
    End If

Open in new window

Mark EdwardsChief Technology Officer

Commented:
I just opened, ran, and closed the EE attached file twice and got only ONE Explorer window for "C:\XOM\SPTTemp\EMRM Financials".
I'm done!
Mark EdwardsChief Technology Officer

Commented:
It's running fine in the last Excel file I attached.

Author

Commented:
Sorry but that didn't work for me.  Still giving me two windows - when I run macro in the latest one:  Book1---Code-In-Sheet-Module.xlsm
Mark EdwardsChief Technology Officer

Commented:
OK.  I moved the code back to a basic module and ran the tests again - only got ONE Explorer window.

Works fine in Excel now.

How it going with you, Stephen?
Mark EdwardsChief Technology Officer

Commented:
OK Steve, are you in the code module with your cursor placed in the Test() procedure and clicking on the Run button?
That's what I'm doing.  Are you doing the same thing or something different?

Let's see if we can find a common ground...

Author

Commented:
I place my cursor in the test() procedure and click on the Run (green arrow) as well.
I keep getting as many Windows Explorer windows as I run it....

1.) You sure you are not closing out of Windows Explorer after it's ran?
2.) You actually step through the coding and get to the yellow part where it says:  

ShowWindow THandle, SW_RESTORE
        BringWindowToTop THandle

Open in new window

Author

Commented:
Has anybody else tried this Excel latest version and only get one Windows opened?
Mark EdwardsChief Technology Officer

Commented:
As I step thru the code, the line:
    THandle = FindWindow(vbNullString, tmp)

Open in new window

results in 0 the first time thru, but if I leave the Explorer window open, close the excel file, then reopen it and run Test() again, it results in a large number (handle number) so it does NOT =0.

If it always =0 for you in the same situation, then the api function is failing for you.
What version of Windows/Excel are you running?
Mark EdwardsChief Technology Officer

Commented:
Here's an idea, make a copy of your workbook, then sanitize it and post it.
I'll test it.
Mark EdwardsChief Technology Officer

Commented:
I haven't seen you mention the results of checking what's in the title bar of your open Explorer window.
As you are stepping thru the code for your second pass, what is the value of the "tmp" variable, and what is in the title bar of your Explorer window at the time you run the code line:
THandle = FindWindow(vbNullString, tmp)

Open in new window


Please include those values when you post the results of your test.  They MUST MATCH, or you are going to get another Explorer window opened.
Mark EdwardsChief Technology Officer
Commented:
OK, one more thing.  The code is setup to look for only the name of the last folder in the path as the text in the Explorer title bar.
If you check the "Display full path in the title bar" checkbox in the Options dialog box, you get the whole path, not just the name of the last folder.

What setting do you have?
Explorer-Window-Options.PNG

Author

Commented:
Thank you all for your extremely helpful comments.  Only until Mark Edwards posted changing the folder settings:  Display the full path in the title bar - and me changing it to that got this to work finally!  

Appreciate the collaborative effort!
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Great, glad you got useful info and a solution to your original question.Fotolia_101597037_XS-1-.jpg
»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial