Open folder in Explorer

[DamienC]

Good morning,

I was wondering if it is possible to use a macro open a folder in Windows Explorer. Previously someone on this forum taught me how to open an Excel file from a macro, but in this case there will be a new file every week. As the files will all be stored in the same folder, I thought it would be easier to open the folder in the normal Explorer view and select the file manually than to change the macro every week.

I am using Windows XP and Excel 2003. My programmes are run from the C drive, but my documents are stored on the E drive. What I am looking for really is a line that says: open in Explorer E:\data\new\processed

Is that possible? I would be most grateful for any suggestions.

[royUK]

Is this what you mean?

Option Explicit

Sub openfile()
    Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String
    Dim iFilterIndex As Integer
On Error GoTo err_handler
    ' Set up list of file filters
    sFil = "Excel Files (*.xls),*.xls"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to Zip"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)

    Workbooks.Open Filename:=sWb
Exit Sub
err_handler:
MsgBox "No selection made"
End Sub

[DamienC]

Not really. I am not looking to zip a file. I wish to select a file from a specific folder. So really I am looking to skip 'Start - My Computer - E - Data - New - Processed' and go straight to the window that shows the contents of the Processed folder.

[royUK]

try the code and change the title, I just posted some code that i use to select a file before zipping it.

Option Explicit
Sub openfile()
    Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String
    Dim iFilterIndex As Integer
On Error GoTo err_handler
    ' Set up list of file filters
    sFil = "Excel Files (*.xls),*.xls"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to open"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)

    Workbooks.Open Filename:=sWb
Exit Sub
err_handler:
MsgBox "No selection made"
End Sub

[DamienC]

Have been messing with the code and have incorporated it in the overall macro, but still cannot get the pop-up window to go directly to the right folder. It opens either my desktop, or the last folder I was in when I opened a file. Been trying with ChDir, which seems to be ignored entirely, and have tried replacing bits that say 'title' with the location (I guess I didn't read your comment correctly), which isn't recognised.

Any thoughts?

[royUK]

try this

Option Explicit

Sub openfile()
    Dim sFil   As String
    Dim sTitle As String
    Dim sWb    As String
    Dim iFilterIndex As Integer
    On Error GoTo err_handler
    ' Set up list of file filters
    sFil = "Excel Files (*.xls),*.xls"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to open"
    ChDrive "C:"
    ChDir "C:\My Documents"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)
    MsgBox sWb
    ' Workbooks.Open Filename:=sWb
    Exit Sub
err_handler:
    MsgBox "No selection made"
End Sub

[broro183]

hi Damien,

Are you putting the "chdir" code before the "on error..." statement?

'... leave the above the same
ChDir "D:\TMP"
On Error GoTo err_handler
'... leave the rest the same

For more info on how this works, select the word "chdir" in the VBE & press [F1] to bring up the VBE Help files.

hth
Rob