Monday, March 27, 2006

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

5 comments:

Anonymous said...

on the Dim d As New MSComDlg.commondialog
line i get User-defined type not defined. Any thoughts?

John Ward said...

Make sure you reference the menu under Tools, and select the Microsoft Common Dialog Controls 6.0 (or manually choose C:\windows\system32 or C:\winnt\system32\comdlg32.ocx, which shouldn't be necessary since the Common Dialog is already registered with Windows). Otherwise, the interpreter will assume your are refering to a user defined type and error out.

andrewt said...

Hi JOHN
Got run time error 429
debug points to
d.ShowOpen

John Ward said...

andrewt,

Good thing you brought this up. I hadn't revisited this article in some time. I was able to duplicate the problem. As it turns out, the Common Dialog is not properly "licensed". In order to correct it, you would need to install the developers tools for Microsoft Office, or one of the Visual Studio products (as far as I know, 6 is required, not sure if others will work).

See the Microsoft Support article (http://support.microsoft.com/kb/281848). Also, their workaround doesn't work either :)

Anonymous said...

Seems a lot of messing around when you could just use Excel's built in File Dialog.

sFileName = Application.GetOpenFilename

This will show a standard file open dialog and put whatever the user selects in the variable sFileName. If they click cancel, sFileName gets filled with "False".