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

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

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.

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")

Select allOpen 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.

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.

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:

---

»bp

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:
2nd:

Back to the drawing board......

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

---

»bp

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...

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

Select allOpen in new window

just-keeps-opening-it.PNG

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

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

Select allOpen 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.

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.

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.

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.

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

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)

Select allOpen in new window

Please note also called in Microsoft Excel not Microsoft Access application.

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

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....

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

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.

Both the original code and Bill's modified code is working for me - I'm getting the same result.

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)

Select allOpen in new window

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

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...

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.

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

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

Select allOpen in new window

HandlingWindowsExplorer.accdb

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.

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:

Go figure.... "Documents" is NOT "EMRM Financials".....

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

Works fine in Access though...

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....

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.

Can you provide the Excel sample you were working with also please?

OK
Book1---Copy.xlsm

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

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

Select allOpen in new window

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!

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

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

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?

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...

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

Select allOpen in new window

Has anybody else tried this Excel latest version and only get one Windows opened?

As I step thru the code, the line:

    THandle = FindWindow(vbNullString, tmp)

Select allOpen 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?

Here's an idea, make a copy of your workbook, then sanitize it and post it.
I'll test it.

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)

Select allOpen 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.

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!

Great, glad you got useful info and a solution to your original question.

---

»bp