XL97: Macro to Change File Type to "All Files (*.*)" in Open Dialog Box

In the Open dialog box in Microsoft Excel, the file type in the Files Of Type box is "Microsoft Excel Files (*.xl*; *.xls; *xla)" by default. This setting causes files that have a file name extension that begins with "xl" to appear in the Look In list.

To display all the files in the current directory, use a Visual Basic for Applications macro to change the default setting to "All Files (*.*)". This article explains how to change the default setting.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS (http://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms) To change the default setting in the Files Of Type box, use the following steps:

1. Close and save any open workbooks, and then create a new workbook.
2. Start the Visual Basic Editor (press ALT+F11).
3. On the View menu, click Project Explorer.
   
   If Personal.xls exists it is listed in the Explorer pane. If Personal.xls is listed, go to step 10. If it does not exist, click "Close and return to Microsoft Excel" on the File menu.
4. On the Tools menu, point to Macros and click Record New Macro. Click "Personal Macro Workbook" in the Store Macro In list and click OK.
   
   A Stop Recording toolbar appears.
5. Click Stop, and then start the Visual Basic Editor (press ALT+F11).
6. In Project Explorer, click "VBAProject(PERSONAL.XLS)" and click Module on the Insert menu.
7. Delete the text in the module you inserted.
8. Type the following macro:

          Sub Open_File()
              Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
          End Sub
   					

9. On the View menu, click Microsoft Excel.
10. On the Tools menu, click Customize and click the Commands tab.
11. On the File menu, click Open.
    
    Note that Modify Selection in the Customize dialog is available.
12. In the Customize dialog box, click Modify Selection.

    "&Open"
    						

    appears in the Name box.
13. Click Assign Macro, click "PERSONAL.XLS!Open_File" and click OK.
14. On the Workbook menu, click File.
    
    This step causes the File menu to disappear.
15. Click File Open on the Standard toolbar and repeat steps 12 and 13.
    
    The selected name referred to in step 12 should be "Open".
16. In the Customize dialog box, click Close.
17. On the File menu, click Exit. When you are prompted to save changes to Personal.xls, click Yes. If you are prompted to save changes to Book1, click No.

For more information about built-in menus, click the Index tab in Microsoft Excel Help, type the following text



and then double-click the selected text to go to the "Add a built-in menu to a toolbar " topic.