Excel: File Open from VBA

I had a question about calling the File Open Dialog from within an Excel VBA macro. The solution I present here is the path of least resistance by importing the ComDLG32.OCX file directly. The other option is to instantiate associated DLL files, and let me tell you, you don’t want to go that route. This will demonstrate how to prompt the user to get a file from within an Excel Macro. Alternatively, I will also show how to open an Excel Spreadsheet from within an Excel Macro.

First, create a new Macro project. Once the project is open and you create a new function/sub program, go up to the Tools menu, and select References

.

Next select the Browse button, change the associated file type to OCX, and select the ComDLG32.OCX file. My system, by default, already defaults to the C:\Windows\System32 directory, if your does not, you will need to navigate to that location to file this file.



Once selected, the file will already be checked for inclusion in your project. Below is a code snippet that will prompt the user for the file, and display the selected file name in Column 1, Row 1 in the open Excel spreadsheet.

Sub PromptForFile()
Dim d As New MSComDlg.CommonDialog

d.ShowOpen

Cells(1, 1).Value = d.Filename

Set d = Nothing
End Sub

So, to build off of this concept, I will now have the Macro prompt a user for the filename, and then open that file within Excel. This accomplishes the same thing as the “File/Open” menu, but it is a simple enough example. Since the filename is stored in the Dialog object under the Filename field, all I need to do is call the Workbooks.open method to open the file. Below is the resulting code, which will filter all files except *.XLS files, and open the file that is selected.

Sub PromptForFile()
Dim d As New MSComDlg.CommonDialog

d.Filter = "xls"
d.Filename = "*.xls"
d.ShowOpen

Excel.Workbooks.Open d.Filename

Set d = Nothing
End Sub