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

• Microsoft Excel 97 for Windows

SUMMARY

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.

MORE INFORMATION

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 the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/refguide/

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.

REFERENCES

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

   menus, built-in 

Additional query words: XL97

Keywords          : kbprg kbualink97 kbdta kbdtacode xlloadsave xlui KbVBA 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: June 21, 1999