Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Open an Explorer Window using VBA

Posted on 2011-02-28
16
Medium Priority
?
426 Views
Last Modified: 2012-05-11
I am trying to develop a document management application that uses an Access Data Project front-end. Data about [construction] projects is stored in a MS-SQL database, and I want to use the front end to link to a directory structure on the file server where project related files can be stored. I am using a form to present the project information to the user, and want to give him a button that will open up the relevant folder in a windows explorer window.
 I am very new to VBA and cannot seem to get Shell or ShellExecute to work. My latest attempt is listed below. It returns `Run time error '53': File not found.'
Private Sub cmdOpenFolder_Click()
   
    Dim strFolder, strCommand As String
    Dim x As Variant
   
    strFolder = "c:\"       'ideally this will be a value from the current record
    strCommand = Chr(34) & "EXPLORER.EXE" & strFolder & Chr(34)
   
    x = Shell(strCommand, vbNormalFocus)
   
End Sub

Access 2007
SQL Server 2000
Windows Server 2003/SP2
Windows XP/SP3
0
Comment
Question by:wlrc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +3
16 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35000204
Shell "explorer C:\", vbNormalFocus
0
 
LVL 10

Expert Comment

by:conagraman
ID: 35000220
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")

ie.Visible = True
With ie
.navigate ("c:")
End With
0
 
LVL 75
ID: 35000281


 Call Shell("explorer c:\")

mx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35000339
followhyperlink "c:\"
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 35000413
Or, if you really want to go to town

Use the Windows API.

Supply the OpenFile() function with a path and filename or just a path and it will open the the appropriate application, or just explorer if only a path is specified.

Paste the whole lot into a module and practice with the Tester procedure

Private Declare Function apiShellExecute 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

Function OpenFile(fileToOpen As String)

Dim retVal As Long, varTaskID As Variant
Dim retStr As String
        retVal = apiShellExecute(0, vbNullString, fileToOpen, vbNullString, vbNullString, 1)
           
    If retVal > 32 Then
        retStr = vbNullString
        retVal = -1
    Else
        Select Case retVal
            Case 31:
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " & fileToOpen, 1)
                retVal = (varTaskID <> 0)
            Case 0:
                retStr = "Error: Out of Memory."
            Case 2:
                retStr = "Error: File not found."
            Case 3:
                retStr = "Error: Path not found."
            Case 11:
                retStr = "Error:  Bad File Format"
            Case Else:
        End Select
    End If
    fHandleFile = retVal & IIf(retStr = "", vbNullString, ", " & retStr)
End Function


Sub Tester()
'Will open the file with registered application
x = OpenFile("C:\Sample Files\Sample.docx")

'Will open Explorer at the path supplied
x = OpenFile("C:\Sample Files")


End Sub
0
 
LVL 10

Expert Comment

by:conagraman
ID: 35000419
: )
0
 

Author Comment

by:wlrc
ID: 35000439
Okay, Chris, that works. However, the big problem for me is that I need to use a variable as the folder. I just put the c:\ constant in there because the rest of my syntax was throwing the error. It's defined in the current record as Folder, and resides on the file server. How do I get it into the Shell command?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35000468
Like:

Dim str As String
    str = "c:\delete me"
    shell "explorer " & str, vbNormalFocus

Chris
0
 
LVL 10

Expert Comment

by:conagraman
ID: 35000496
you could drop an active x web browser on a form and have it navigate to your file.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35000580
you can also do
dim str as string
str="c:\myfolder\another folder\"
followhyperlink str
0
 
LVL 10

Expert Comment

by:conagraman
ID: 35000624
i can never remember the followhyperlink
that command is the heat
0
 

Author Comment

by:wlrc
ID: 35000646
Still not working. Here's my latest attempt:

    Dim strFolder As String
    strFolder = Folder
    Shell "explorer" & strFolder, vbNormalFocus

The value of Folder is \\Server\Jobs\Big Project A, as plain text.
Still getting error 53.
Note that I want to open an Explorer folder, not a specific file.
Thanks
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35000737
Try typing it in initially as hard data and see if it works ... if it does then I suggets folder is not what you think it is ... i.e. it might have control codes therein.

Chris
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 35000828
did you try using

  Dim strFolder As String
    strFolder = "\\Server\Jobs\Big Project A\"
 
followhyperlink strFolder

0
 
LVL 10

Expert Comment

by:conagraman
ID: 35000839
if you mean the active x browser it will open to your file or a folder
0
 

Author Comment

by:wlrc
ID: 35001134
capricorn1:

It worked! I can even use the drive mapping to refer to the folder, instead of giving the absolute address.

Thanks for your help.
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question