Knowledge Base

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

Article ID: 161486

Article Last Modified on 11/23/2006

APPLIES TO

This article was previously published under Q161486

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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

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

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


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

Additional query words: XL97

Keywords: kbdtacode kbprogramming kbualink97 KB161486


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.