Open Containing Folder

I ran into a problem, and maybe some of you out there have as well: Ever have an Excel file open and need to open the folder it's in?

Here's how it happens: After I browse to a folder and open the workbook I want, I close the Explorer window. It's a bad habit, but I can't help it. Inevitably, I need to open another file in the same folder. So I have to browse the entire folder tree again to get back to the folder I was in. No more!

This code will open Windows Explorer to the folder where the current file is located. The "Declare" statement should be placed at the top of a standard module. Note that it works on saved workbooks only. It uses a Windows API to open Windows Explorer.

Declare Function ShellExecute Lib "shell32.dll" Alias _
	"ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation _
                     As String, ByVal lpFile As String, ByVal lpParameters _
                     As String, ByVal lpDirectory As String, ByVal nShowCmd _
                     As Long) As Long

Sub OpenContainingFolder()

  On Error GoTo ErrorHandler

  Dim currentWkbkPath As String

  currentWkbkPath = ActiveWorkbook.Path

  ShellExecute 0, "open", currentWkbkPath, 0, 0, 1

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Usable in other Office programs too! For example, MS Word:

Sub OpenContainingFolder()

  On Error GoTo ErrorHandler

  Dim currentDocPath As String

  currentDocPath = ActiveDocument.Path

  ShellExecute 0, "open", currentDocPath, 0, 0, 1

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

and MS Access:

Sub OpenContainingFolder()

  On Error GoTo ErrorHandler

  Dim currentDBPath As String

  currentDBPath = Application.CurrentProject.Path

  ShellExecute 0, "open", currentDBPath, 0, 0, 1

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

although the Access VBA code must be tied to a specific DB. Now go, and never have this problem again!