I usually run Excel 2003.
Using a mechanism which is unknown to me, Application.StartupPath is
initialized automatically to the name of a folder. (On my machine, which
does not have profiles, it is set to
C:\Users\<User ID\AppData\Roaming\Microsoft\Excel\XLSTART.)
Files in that folder automatically open so macros therein are available.

I find this does not port to a machine which has Excel 2007.
Application.StartupPath is not initialized. I have no information on
choices made installing 2007 and no ability to reinstall it. When I
examine Application in the debugger with View/Show Locals
(e.g. Sub probe(): Dim O as Object: Set O = Application: End Sub)
I find O.StartupPath is displayed as
<Unable to get the StartupPath property of the Application class

I could probably set the field equivalent to "Tools/Options/General/At
startup. open all files in:", but that field is not set in my 2003
installations. (That field sets Application.AltStartupPath.)

http://support.microsoft.com/kb/822107 seems to imply that StartupPath
can be controlled by setting the "default file location". This is
actually where new workbooks are saved by default.

How do I control the setting of Application.StartupPath in 2007?
--
Walter Briscoe