Opening Multiple Files

  • Thread starter Thread starter Steve H
  • Start date Start date
S

Steve H

How can I create a shorcut on my desk top that will open
several spreadsheets located in various directories
Thanks!!!!
 
Create a .bat file somewhere.

Inside that .bat file, you'll have as many lines as you need that look like:

start "c:\my documents\excel\book1.xls"
start "c:\my documents\excel\book2.xls"

Then put a shortcut on the desktop that points at this .bat file.

(You could actually just put the .bat file itself on the desktop. But if you
delete the shortcut, you can just create a new shortcut. If you delete the .bat
file, you'll have to create that (and it's usually more difficult to remember
what 10 files you wanted to open.)
 
Try opening all the spreadsheets from the various
directories. File/Save as Workgroup into one of the
directories then create the shortcut. The files still
exist independently in their original directories.
 
One way is to use a macro in a blank workbook, which can either be saved to
the desktop, or be saved in a folder and a shortcut to the book created on
the desktop.

Step by Step:
---------------
1. Create a new blank workbook and save it to whichever folder you want and
with whatever name you want and . I'll use the name "Open Multiples.xls" and
save it to the My documents folder.

2. Create a shortcut to the workbook created in step 1 on your desktop

3. Switch back to Excel and while viewing the newly saved workbook press
Alt+F11 to access the Visual Basic Editor (VBE)

5. In the Project Explorer (usually the left side of the VBE) locate the
name of the workbook you just saved. It'll be in parenthesis.

6. Click the "+" next to the workbook

7. Locate and double-click the "ThisWorkbook" object

A module (window) should appear to the right of the Project Explorer.

8. Copy the following macro into the ThisWorkbook module:

Note: change the path and workbook names listed in the "vBooks = Array"
section of the macro to the names and locations of your desired workbooks.

Private Sub Workbook_Open()

Dim lCount As Long
Dim szBook As String
Dim szError As String
Dim vBooks As Variant

'Place the list of workbooks (with their paths) in an array that
' we'll loop through below
vBooks = Array("C:\My Documents\2003 Update\Friday.xls", _
"C:\My Documents\2003 Update\Grids\2003\09-08-2003
Prelimary.xls", _
"C:\My Documents\2003
Update\PrelimUpdate\sundayexample.xls")

'Loop through each workbook in the array
For lCount = LBound(vBooks) To UBound(vBooks)
'Store the name of the workbook in a variable called szBook
'Clear the contents of the variable
szBook = ""

'Test whether the file is in the location specified.
' If it is, the name of the workbook is stored in szBook
szBook = Dir(vBooks(lCount))

'Test to see if anything was stored in szBook
If Len(szBook) Then 'the file is available
'Open the workbook
Workbooks.Open vBooks(lCount)
Else
'The file wasn't found. Store its name in a variable called
szError
' for later display in an error message
szError = vBooks(lCount) & vbLf
End If
Next lCount 'Loop back up to the next workbook

'Test to see if anything was stored in the variable szError
If Len(szError) Then 'Something's there - one or more workbooks could
not be found
'Add a start phrase to szError and display the error in a Message
Box
szError = "The following workbooks could not be found:" & vbLf &
vbLf & szError
MsgBox szError, vbExclamation, "Workbooks Not Found"
End If

'Close this workbook without saving the changes.
' We only want the workbooks we opened displayed in Excel
ThisWorkbook.Close False
End Sub

9. Save and close the workbook

10. Try double-clicking the shortcut on your desktop and click Enable
Macros.
 
Back
Top