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


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q213755

SUMMARY

In the Open dialog box in Microsoft Excel, the default setting in the Files of type box is "Microsoft Excel Files (*.xl*; *.xls; *.xla; *.xlt; *.xlm; *.htm; *.html)."

This article provides a sample Visual Basic for Applications macro that changes the default setting to "All Files (*.*)", in order to display all files in the current directory.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. To change the default setting in the Files of type box, follow these 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 6. 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. If you recorded a macro in steps 4 and 5, you may delete the text in the module you recorded.
  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 worksheet menu bar, click File.

    This step causes the File menu to collapse.
  15. Click the Open button 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 Microsoft Excel Help on the Help menu, type built-in menus in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbdtacode kbhowto kbProgramming KB213755
©2004 Microsoft Corporation. All rights reserved.