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

• Microsoft Excel 2000

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, 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 Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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.

Additional query words:

Keywords : kbprg kbdta kbdtacode xlloadsave xlui KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto