CODEWITHSUNDEEP
excelmacrosexcel-2007vba
iron
iron

Reputation: 31

Excel Addin (File.xll) will not open properly in excel 2007, but works fine in 2003

How do i make excel 2007 treat the xll addin the same when opening programatically, as it does when browsing via the addin-manager?

i have an excel addin (XLL file) that works fine on an old XP box, running excel 2003 (Excel XP)...but not on windows 7 running excel 2007.

On my new Windows 2007 machine, running Office/Excel 2007, the same XLL has odd behavior.

In Excel 2007, When i goto the excel addin manager, browse to the xll, and then load it...the XLL works just fine...but just for that session.

But, after i close this instance of excel, and then re-open excel (so, now the adddin will be loaded programatically from the Excel\Options list from the registry - which is how excel normally loads addins)....Excel throws an error saying

"The file you are trying to open, 'Addin.xll' is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

If i click 'yes', then the xll opens with garbage text, and does not work.

However, if i kill excel, open the registry, goto HKEY_Current_User\Software\Microsoft\Office]12.0\Excel\Options\

and remove the [OPEN] item which points excel to open the addin programatically...then excel opens fine (without the addin loading...obvs).

Again, if i then goto the Excel Options --> addin manager and re-add the Addin...then it will load and work just fine.

But again, after i close excel and re-open., i'll get the same error from above.

I've read that Excel sets the 'Current Working Directory' when you open the addin from the "addin manager --> browse" method...but not when excel opens the addin programaticaly.

So...what can i do to make excel open my addin correctly? It works fine with Excel 2003...and it works fine the 1st time in Excel 2007...so its got to be a problem with Excel.

Upvotes: 0

Views: 1783

Answers (1)

iron
iron

Reputation: 31

i figured the answer to my own question...so i thought i'd share.

On the new windows 7 box, there must be an environment variable 'PATH' to the folder directory where the addin and its associated libraries live.

My addin files are located in C:\AddinFolder\

So here are the steps to fix
1) right click on MyComputer and select "properties"
2) select the "System protection" item from top left list
3) click on the advanced tab
4) click in the button "Environment Variables"
5) in the lower section (system variables) scroll down till you find the "Path" variable and then select "Edit"
6) Add to the end of the string the path to your addin library. The list is a set of directory paths, separated by semicolons. So, i added ";C:\MyAddin\" to the end of the string, which already had a bunch of paths listed for a variety of other programs
7) click Ok a couple times to get out of that set of dialogue boxes
8) open Excel with the addin set to load, and voilla...it works perfectly

**note...don't delete the contents of the Path variable..just add to it...else some other software might stop functioning properly

Upvotes: 0

Related Questions