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?
If you use the shortcut method, you can make it even easier to run by adding the shortcut to your quick launch toolbar (next to the Start button) – you can drag and drop the shortcut on there.
The reason I mention this is that if you add a reference to the Microsoft Scripting runtime library in VBA, you can write code which creates the shortcut programmatically (the library has a special function) and put it in the quick launch toolbar (the library gives you the location of the quick launch folder). My work has an Excel utility which we use a lot, so we put it on the network, and the first time you run it, VBA creates the quick launch icon which you use from then on.
There are two XLSTARTs. The one under the program installation folder applies to all users of a machine, The one under the user’s folder just to that user.
A couple of months ago, I noticed that Excel 2007 stopped loading my personal.xlsb workbook. I have verified that the file is in
C:Documents and SettingsjaywApplication DataMicrosoftExcelXLSTART
but if I want to use the functionality in personal.xlsb I have to load it manually.
Has anyone seen this behavior before and/or have a suggestion as to how to restore the original functionality.
thanks,
jay
Try this… Office Button, Excel Options, Add-Ins. By “Manage:” select “Disabled Items”, then click “Go” to display a list. Hopefully you will see your xlstart file in the displayed list. Make your selection, then click “Enable” and restart Excel.
My xlstart file ends up here when I inadvertently select “yes” when Excel has crashed upon opening and blames my xlstart file.
Thanks Jim,
That seems to have done it!
jay
With respect to XLSTART locations, it’s still a bad idea to put any data files into %ProgramFiles%. If there should be separate all users and current user XLSTART locations, then if the current user’s XLSTART path were
%USERPROFILE%Application DataMicrosoftExcelXLSTART
then it’d seem the appropriate location for all users would have been
%ALLUSERSPROFILE%Application DataMicrosoftExcelXLSTART
I realize that the all user XLSTART directory has always been under %ProgramFiles%, so it seems MSFT isn’t taking Office 2010 as an opportunity to fix this.
In fairness, the Lotus Notes client still defaults to putting users’ mailbox files under the Notes directory in %ProgramFiles%, which is much worse since mailbox files update far more frequently than presumably static all user common startup files.