How to open a folder in Windows Explorer from VBA?

I want to click a button on my access form that opens a folder in Windows Explorer.

Is there any way to do this in VBA?

Solution 1:

You can use the following code to open a file location from vba.

Dim Foldername As String
Foldername = "\\server\Instructions\"

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

You can use this code for both windows shares and local drives.

VbNormalFocus can be swapper for VbMaximizedFocus if you want a maximized view.

Solution 2:

The easiest way is

Application.FollowHyperlink [path]

Which only takes one line!

Solution 3:

Thanks to PhilHibbs comment (on VBwhatnow's answer) I was finally able to find a solution that both reuses existing windows and avoids flashing a CMD-window at the user:

Dim path As String
path = CurrentProject.path & "\"
Shell "cmd /C start """" /max """ & path & """", vbHide

where 'path' is the folder you want to open.

(In this example I open the folder where the current workbook is saved.)

Pros:

• Avoids opening new explorer instances (only sets focus if window exists).
• The cmd-window is never visible thanks to vbHide.
• Relatively simple (does not need to reference win32 libraries).

Cons:

• Window maximization (or minimization) is mandatory.

Explanation:

At first I tried using only vbHide. This works nicely... unless there is already such a folder opened, in which case the existing folder window becomes hidden and disappears! You now have a ghost window floating around in memory and any subsequent attempt to open the folder after that will reuse the hidden window - seemingly having no effect.

In other words when the 'start'-command finds an existing window the specified vbAppWinStyle gets applied to both the CMD-window and the reused explorer window. (So luckily we can use this to un-hide our ghost-window by calling the same command again with a different vbAppWinStyle argument.)

However by specifying the /max or /min flag when calling 'start' it prevents the vbAppWinStyle set on the CMD window from being applied recursively. (Or overrides it? I don't know what the technical details are and I'm curious to know exactly what the chain of events is here.)