Let’s say that you want to open a specific file when you start Excel. Here are some options:
XLSTART
If you have Excel installed, you have a folder called XLSTART. Mine’s here C:\Documents and Settings\Dick\Application Data\Microsoft\Excel\XLSTART\. On my Windows 7 machine running Office 2010 Beta, it’s here C:\Program Files\Microsoft Office\Office14\XLSTART\. (Are we putting data files under Program Files again?)
Any file you put in XLSTART will open automatically when you start Excel. The next time your coworker leaves his workstation unattended, put a couple hundred CSV files in his XLSTART folder. You’ll be the office comedian.
Alternate Startup Location
2003: Tools – Options – General – At startup, open all files in:
2007: Office Orb – Excel Options – Advanced – General – At startup, open all files in:
2010: File – Options – Advanced – General – At startup, open all files in:
Works just like XLSTART, but you can leave this textbox blank (it is by default).
Here’s some other stuff I wrote on XLSTART and Alternative Startup Locations.
Shortcuts
You can make a new shortcut with the following Target "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" "C:\Documents and Settings\Dick\My Documents\multiplelookups.xls".
That will open multiplelookups.xls whenever you use this shortcut to open Excel. You could also just use the file name without the executable as long as your file associates are correct. If you don’t know what file associations are, they’re probably OK.
Auto_Open
Do you already have a workbook or add-in that opens automatically? I have a bunch, but the easiest one for me to modify is PERSONAL.XLS. I can create a new module in my PMW and put in a macro that opens a workbook by following these steps:
- From Excel, press Alt+F11 to open the VBE
- Press Control+R to show the Project Explorer if it’s not already showing
- Find PERSONAL.XLS (or whatever workbook opens for you at startup) in the Project Explorer and select it. Make sure that the file name in the title bar is correct.
- From the Insert menu, choose Module
- In the code pane, type something like
Sub Auto_Open()
Workbooks.Open "C:\Documents and Settings\Dick\My Documents\multiplelookups.xls"
End Sub
Make sure it’s called Auto_Open because that’s Excel’s trigger to run it on startup.
- Still in the VBE, from the File menu, choose Save PERSONAL.XLS
- Press Alt+F4 to close the VBE and return to Excel
Now whenever you start Excel, the Auto_Open code will run and your file will open.
Open Event
This is like Auto_Open. Instead of inserting a new Module, locate the ThisWorkbook module and open it. At the top of the code pane, there are two drop down boxes. Select Workbook from the left one and Open from the right one. Was Open already in the right one? That’s OK. Now type that same one line of code from above so that your module looks like this
Private Sub Workbook_Open()
Workbooks.Open "C:\Documents and Settings\Dick\My Documents\multiplelookups.xls"
End Sub
Like Auto_Open, this code will run whenever PERSONAL.XLS is opened.
That’s all the ways I can think of to open a file when Excel opens. What did I miss?
