Macro to Change Default *.XL* to *.* in Open Dialog Box |
Q104581
The information in this article applies to:
- Microsoft Excel for Windows, versions 2.0, 2.01, 2.1, 2.10c, 2.10d, 3.0, 4.0, 4.0a, 5.0
- Microsoft Excel for Windows 95, version 7.0
- Microsoft Excel for OS/2, versions 2.2, 3.0
SUMMARY
In the Open dialog box in Microsoft Excel, the default string in the File Name box is "*.XL*." (To view the Open dialog box, choose Open from the File menu.) This string causes all files with a filename extension beginning with the letters "XL" to be displayed in the list of files below the File Name box.
If you want a list of all of the files in the current directory to appear in the list of files, use one of the following methods to change the default string from "*.XL*" to "*.*".
MORE INFORMATION
To permanently change this string
Microsoft Excel versions 5.0 and 7.0
In a Visual Basic module, enter the following:
Sub Open_File() Application.Dialogs(xlDialogOpen).Show arg1:="*.*" End Sub
- From the File menu, choose Save As. In the File Name box, type OPEN.XLS, and choose OK.
Note that you may want to save the OPEN.XLS file to the XLSTART subdirectory so that this file will be opened automatically when you start Microsoft Excel. - With the module sheet still active, choose Menu Editor from the Tools menu. From the Menu Items list, select &Open..., and choose Delete. Choose the Insert button. In the Caption box, type &Open...". From the Macro list, select Open_File. From the Menu Bars list, select Worksheet, and choose OK.
To replace the Open command on the File menu for a chart sheet, module sheet, or when no documents are open, repeat step 3 for each of these menu bars. Note that the file OPEN.XLS that contains the Open_File macro must be available in the directory in which you saved it in step 2 to avoid receiving an error message when you choose Open from the File menu.
You may want to save the OPEN.XLS file to the XLSTART subdirectory so that this add-in will be opened automatically when you start Microsoft Excel.
Microsoft Excel version 4.0 and earlier
In a macro sheet, enter the following:
A1: Auto_Open
A2: =DELETE.COMMAND(1,1,"Open...")
A3: =ADD.COMMAND(1,1,B1:F1,2)
A4: =RETURN()
A5:
A6: Open
A7: =OPEN?("*.*")
A8: =RETURN()
B1: &Open... C1: OPEN.XLA!OPEN D1: E1: F1: EXCELHLP.HLP!1058
- Select cell A1.
- From the Formula menu, choose Define Name. Select the Command option and choose OK.
- Select cell A6 and repeat step 2.
- From the File menu, choose Save As and select Add-In from the Save File As Type list. (In Microsoft Excel 3.0, choose Options to access the File Format list). In the File Name box, type OPEN.XLA.
Each time that you open the OPEN.XLA file, the Open command will be automatically altered as described above. You may want to save the OPEN.XLA file to the XLSTART subdirectory so that this add-in will be opened automatically when you start Microsoft Excel.
This setting will remain changed as long as the add-in macro you create loaded when you start Microsoft Excel.
To temporarily change this setting
Microsoft Excel versions 5.0 and 7.0
In a Visual Basic module, enter the following:
Sub Auto_Open SendKeys "*.*{enter}{esc}" Application.Dialogs(xlDialogOpen).Show End Sub
- On the Tools menu, click Make Add-In. In the File Name box, type Open. From the Directories list, select the XLSTART subdirectory of the Microsoft Excel directory, and choose OK.
In a macro sheet, type the following:
A1: Auto_Open
A2: =SEND.KEYS("%FO*.*~{esc}")
A3: =RETURN()
- Select cell A1. From the Formula menu, choose Define Name. Select the Command option and choose OK.
- From the File menu, choose Save As and select Add-In from Save File As Type list. (In Microsoft Excel 3.0, choose Options to access the File Format list). In the File Name box, type OPEN.XLA.
This macro, invokes the keystrokes necessary to change the string in the Open dialog box from "*.XL*" to "*.*." Since this macro is saved in the startup directory, it will be started each time you start Microsoft Excel.
With this method, the setting will remain changed for the session of Microsoft Excel in which you make the change, or until you change the setting again.
The following is an explanation of keystrokes sent by the SEND.KEYS() function:
This keystroke Does this -------------------------------------------------------- %F (Alt-F) Selects the File menu O Chooses the Open command from the File menu *.* Sends new string ~ Sends the ENTER key to confirm change {esc} Cancels the File dialog box since no files are being opened at this time.
REFERENCES
"User's Guide 2," version 4.0, pages 282-294
"User's Guide 1," version 4.0, page 79
"User's Guide," version 3.0, pages 553-554, 641-652
Additional query words: 2.1 2.10 2.1c 2.10c 2.1d 2.10d 2.2 2.20 2.21 3.0 4.0 4.00a howto List Files Of Type
Keywords : kbprogramming
Issue type :
Technology : kbExcelSearch kbExcel700 kbExcel500 kbExcel95Search kbExcelOS2Search kbExcel400 kbExcel220OS2 kbExcel300OS2 kbExcel201 kbExcel210c kbExcel210d kbExcel210 kbExcel300 kbExcel400a kbExcel200
Last Reviewed: June 12, 2001 |