I don't expect what you see, and it is not the behaviour on my Excel 2007. After I add then add-in there it loads automatically the next time I start Excel. It's also not something I can recall having been asked about.
This could be:
* A setting on your Excel - maybe a security setting or something. I don't know of anything particular.
* You are starting the Excel process as a COM server using a script or something.
* Something related to the particular add-in - either it has been marked by Excel in some way, or the registry entry for it says not to load automatically. Check for "Disabled Application Add-Ins" at the bottom of the add-ins page.
You could try to do the following from the VBA immediate window:
Application.AddIns.Add("c:\...path...\MyAddIn.xll").Installed = True
This should make it installed and load the next time you start Excel.
In the registry you can look in the registry under HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options
you should find values called OPEN, OPEN1, OPEN2 etc. for the add-ins that are installed to be loaded, with the data: /R c:\...path...\MyAddIn.xll.
You can also look under HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins for anything suspicious.
Finally you should check whether there is anything unusual under C:\Documents and Settings\YourName\Application Data\Microsoft\AddIns\. Files here are also treated differently - just make sure there's nothing related to your add-in here.
Please write again if you find out more, whether fixed or not.