How to simulate File Open Dialog in Excel using VBA

Anzek

New Member
Joined
Jan 20, 2016
Messages
17
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
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jan 20, 2016
Messages
17
@ 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

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
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

Active Member
Joined
Feb 14, 2006
Messages
377
@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.
 

Forum statistics

Threads
1,082,250
Messages
5,364,033
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top