Wednesday, January 07, 2009

How to Windows Explorer using VBA

How to Open a Folder in Windows Explorer using VBA

ShellExecute() Windows API function can be called from a VBA macro to start another program under Microsoft Windows. Use ShellExecute() instead of Shell (a Visual Basic statement) or WinExec() (a Windows API function) to work around the following limitation of the latter commands.

With Shell and WinExec(), you cannot start an application by specifying a file name only. For example, the following Shell statement will fail:

Shell (“c:\temp”)

Declare the API function

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

The following code will open the specified folder in Windows Explorer

Sub Open_ExplorerWindow()

ShellExecute 0, "open", "c:\temp", 0, 0, 1

End Sub

11 comments:

  1. Anonymous2:15 AM

    Useful. :) Thanks!

    ReplyDelete
  2. Anonymous11:00 AM

    Is there a modification that will open the folder and then highlight a specific file? Basically looking for a way to point the user to a specific file.

    ReplyDelete
    Replies
    1. Anonymous6:31 AM

      Yes, use the SendKeys method to type out the name of the file. Windows will select that file.

      Delete
    2. Could you please elaborate on this SendKey method. I am still baffled how to do this within VBA.

      Delete
    3. Anonymous4:43 AM

      Windows Explorer command line option allows for this.

      Two examples:
      Example 1: Explorer /select,C:\TestDir\TestApp.exe

      Opens a window view with TestApp selected.

      Example 2: Explorer /e,/root,C:\TestDir\TestApp.exe

      This opens Explorer with C: expanded and TestApp selected.

      Reference:
      http://support.microsoft.com/kb/152457

      Delete
  3. Anonymous8:21 AM

    This is genious!

    ReplyDelete
  4. Holy CRUD, finally something worked! Thank you!

    ReplyDelete
  5. Anonymous6:55 AM

    Is there a way to determine if the windows explorer (yellow folder icon) is open?

    ReplyDelete
  6. Anonymous6:56 AM

    Updated question:
    Is there a way using vba to determine if the windows explorer (yellow folder icon) is open? Thanks

    ReplyDelete
  7. Anonymous4:45 AM

    Technically, you could have also just done this:

    Call Shell("explorer """"" & "c:\temp" & """""", vbNormalFocus)

    The qualification of the path with quotations is sufficient to get around the limitations that you've highlighted.

    ReplyDelete
  8. Anonymous11:10 AM

    so much awesomeness on comment 4:45, this is just what i needed

    Call Shell("explorer """"" & "c:\temp" & """""", vbNormalFocus)

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.