XL: How to Create Macro That Opens Multiple Selected Files

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

In the Open dialog box in Microsoft Excel, you can select more than one file to be opened at a time. If you are using Visual Basic for Applications in conjunction with the GetOpenFileName method, you must use a macro to loop through the selected files to open them.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Visual Basic Code Example

1. In a new workbook, insert a module, and enter the following code:

      Sub loopyarray()
   
      Dim filenames As Variant
   
      ' set the array to a variable and the True is for multi-select
      filenames = Application.GetOpenFilename(, , , , True)
   
         counter = 1
   
         ' ubound determines how many items in the array
         While counter <= UBound(filenames)
   
            'Opens the selected files
            Workbooks.Open filenames(counter)
   
            ' displays file name in a message box
            MsgBox filenames(counter)
   
            'increment counter
            counter = counter + 1
   
         Wend
      End Sub
   
   

2. Run the loopyarray macro.

3. In the Open dialog box, select multiple files to open: to do this, press CTRL as you click each file name.

4. Click OK.

REFERENCES

"Visual Basic User's Guide," version 5.0, pages 129-137