How to simulate File Open Dialog in Excel using VBA

[Anzek]

hi guys
I've been lurking the forum for a while now and has always found good answers. However, I'm having trouble finding a way to simulate the file open dialog using VBA. I'd like to users to click a link to a directory and it opens in the File Open view as if the users just clicked File/Open from Excel. And of course they could open any file from there (or navigate back and forth), I'd like them to have choices of opening as Read-only, as copy, in protected view, etc just like the down arrow next to the Open button in the File Open dialog. In other words, I'd like all the functionalities of the File Open Dialog
Thank you

 

[Richard U]

Obvious question: Why not use the open file dialogue box?

 

[Smitty]

Welcome to the Board!

You can use the GetOpenFileName method:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> GetOpenFName()<br>    <SPAN style="color:#00007F">Dim</SPAN> FileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Filt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FilterIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, Response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#007F00">'   Set to Specified Path\Folder</SPAN><br>        ChDir ActiveWorkbook.Path<br>    <SPAN style="color:#007F00">'   Set File Filter</SPAN><br>        Filt = "Excel Files (*.xls), *.xls"<br>    <SPAN style="color:#007F00">'   Set *.* to Default</SPAN><br>        FilterIndex = 5<br>    <SPAN style="color:#007F00">'   Set Dialogue Box Caption</SPAN><br>        Title = "Please select a different File"<br>    <SPAN style="color:#007F00">'   Get FileName</SPAN><br>        FileName = Application.GetOpenFilename(FileFilter:=Filt, _<br>            FilterIndex:=FilterIndex, Title:=Title)<br>    <SPAN style="color:#007F00">'   Exit if Dialogue box cancelled</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> FileName = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#007F00">'   Display Full Path & File Name</SPAN><br>        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")<br>    <SPAN style="color:#007F00">'   Open Selected Workbook</SPAN><br>        Workbooks.Open FileName<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH

 

[Anzek]

@ Richard: The users like to not navigate thru directories, having the common directories a click away is handy
@Smitty: Thanks for the quick answer. However, There's no option to open the file as read-only, as copy, in protected view, etc. The users would like to have a drop down list of those options by clicking the down arrow button next to the Open button in the File Open Dialog. Could you please help me with that too ? Thanks

 

[Smitty]

However, There's no option to open the file as read-only, as copy, in protected view, etc. The users would like to have a drop down list of those options by clicking the down arrow button next to the Open button in the File Open Dialog. Could you please help me with that too ? Thanks

Unfortunately, I don't think that property gets exposed in the GetOpenFileName method. The only way I can think of is with the Open Dialog:

Application.Dialogs(xlDialogOpen).Show

 

[Richard U]

@Anzek. Now I understand.

I did that a few years ago using a userform as a substitute. They needed very limited access to the network.... let me see if I can dig it up.

 

[Anzek]

Bump !

 

[Anzek]

Bump again!