Macro to Change Default .XL to . in Open Dialog Box

ID: Q104581

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 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