PDA

View Full Version : Excel 2007 auto install add in



tpoynton
08-04-2007, 09:34 AM
Have had 2007 for about 24 hours now, and I'm puzzled...first, the macro recorder doesnt work for recording the Save As from the office button? that might help me solve this problem...

I've modified Johnske's article code a little bit to try and auto install an xlam from an xlsm. It actually works fine, until you restart excel - at which time the following message appears:

'Excel can not open the file because the file format or extension is not valid. verify that the file has not been corrupted and that the file extension matches the format of the file'

OK...been trying for about an hour now to create a test workbook; I had it working as above for a short time, then it stopped working and I cant figure out why. In fact, it freezes Excel, which is why I wont post a sample workbook here. I'll post the code below and perhaps someone can spot my problem...

in a module:
'
'---------------------------------------------------------------------
' Procedure : Sub InstallAddIn
' Author : John Skewes
' Purpose : Convert .xls file to .xla, move it to
' addins folder, and install as addin
'---------------------------------------------------------------------
'
Private Sub InstallAddIn()
'
Dim AddinTitle As String, AddinName As String
Dim XlsVersion As String, MessageBody As String
Dim AddinSavePath As String
With ThisWorkbook
AddinTitle = Left(.Name, Len(.Name) - 5)
AddinName = AddinTitle & ".xlam"
XlsVersion = .FullName '< could be anywhere

AddinSavePath = Application.UserLibraryPath

'check the addin's not installed in
If Dir(AddinSavePath & AddinName) = Empty Then

.IsAddin = True '< hide workbook window
'move & save as .xla file
.SaveAs AddinSavePath & AddinName, xlAddIn

'add a workbook as addin stuff doesnt work without it
Workbooks.Add
'go thru the add-ins collection to see if it's listed

If Listed Then 'check this addins checkbox in the addin dialog box
AddIns(AddinTitle).Installed = True
Else 'it's not listed (not previously installed)
'AddIns.Add(AddinSavePath & AddinName, true).Installed = True
AddIns.Add(AddinSavePath & AddinName, False).Installed = True
End If
Workbooks.Close
MessageBody = "The Add In has been installed - " & _
"to access the tools available in the add in," & _
vbNewLine & _
"you will find a new menu " & _
"in the 'Add Ins' tab for your use." & vbNewLine & vbNewLine & _
"You can safely delete the installation file now. " & _
"It is located at:" & vbNewLine & vbNewLine & XlsVersion

If BooksAreOpen Then '< quit if no other books are open
If MsgBox(MessageBody & vbNewLine & vbNewLine & _
"Other books are open. Click 'No' to save the other" _
& " workbooks or 'Yes' to close and quit now", vbYesNo) _
= vbYes Then
.Save
Else
Exit Sub
End If
Else
MsgBox MessageBody
'.Save - commented out for testing; prevents excel dialog
End If
End If
End With
End Sub
'---------------------------------------------------------------------
' Procedure : Function Listed
' Author : John Skewes
' Purpose : Checks if this addin is in the addin collection
'---------------------------------------------------------------------
Private Function Listed() As Boolean
'
Dim Addin As Addin, AddinTitle As String
'
Listed = False
With ThisWorkbook
'AddinTitle = Left(.Name, Len(.Name) - 4)
AddinTitle = Left(.Name, Len(.Name) - 5)
For Each Addin In AddIns
If Addin.Title = AddinTitle Then
Listed = True
Exit For
End If
Next
End With
End Function
'---------------------------------------------------------------------
' Procedure : Function BooksAreOpen
' Author : John Skewes
' Purpose : Check if any workbooks are open
' (this workbook & startups excepted)
'---------------------------------------------------------------------
Private Function BooksAreOpen() As Boolean
Dim Wb As Workbook, OpenBooks As String
'get a list of open books
For Each Wb In Workbooks
With Wb
If Not (.Name = ThisWorkbook.Name _
Or .Path = Application.StartupPath Or .Name = "Install") Then
OpenBooks = OpenBooks & .Name
End If
End With
Next
If OpenBooks = Empty Then
BooksAreOpen = False
Else
BooksAreOpen = True
End If
'
End Function
'---------------------------------------------------------------------
' Procedure : Sub ReInstall
' Author : John Skewes
' Purpose : Replace addin with another version if installed
'---------------------------------------------------------------------
Private Sub ReInstall()
'
Dim AddinName As String
Dim AddinSavePath As String
AddinSavePath = Application.UserLibraryPath

With ThisWorkbook
AddinName = Left(.Name, Len(.Name) - 5) & ".xlam"
'check if 'addin' is already installed
If Dir(AddinSavePath & AddinName) = Empty Then
'
'install if no previous version exists
Call InstallAddIn
'
Else
'delete installed version & replace with this one if ok
If MsgBox("The file already exists " _
& "in the installation folder." & vbNewLine & vbNewLine & _
"Would you like to replace the existing file with " & _
"this one? (Yes recommended)" & vbNewLine & vbNewLine, vbYesNo) = vbYes Then

Kill AddinSavePath & AddinName
Call InstallAddIn
End If
End If
End With
End Sub
Public Sub Johnske_InstallRoutine()

'added to uninstall previous version if one exists
Dim i As Long
Dim xlaName As String
With ThisWorkbook
For i = 1 To AddIns.Count
xlaName = AddIns(i).Name
If (xlaName = "xlamtest.xlam") Then
ThisWorkbook.KillMenu
AddIns(i).Installed = False
End If
Next i
End With
Dim AddinTitle As String, AddinName As String
Dim XlsName As String
Dim AddinSavePath As String
AddinSavePath = Application.UserLibraryPath
AddinTitle = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)
XlsName = AddinTitle & ".xlsm"
AddinName = AddinTitle & ".xlam"
'
'check the addin's not already installed in librarypath
If Dir(AddinSavePath & AddinName) = Empty Then
Run "InstallAddIn"
Else
If ThisWorkbook.Name = XlsName Then
Run "ReInstall"
Exit Sub 'added
End If
End If
End Sub

NOTE that I had a button on a sheet that ran the Johnske_InstallRoutine first, so if you are brave enough to test, start there. also, I tried several variations on the .saveas (append .xlam, not append .xlam, xladdin = 18) and addins.add(switching between true and false on the copy)

ThisWorkbook:
Option Explicit
Public Sub Workbook_AddinInstall()
AddMenu
End Sub
Public Sub Workbook_AddinUninstall()
KillMenu

Dim xlaName As String
Dim i As Long

With ThisWorkbook
For i = 1 To AddIns.Count
xlaName = AddIns(i).Name
If xlaName = "xlamtest.xlam" Then
AddIns(i).Installed = False
End If
Next i
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
KillMenu
End Sub
Public Sub Workbook_Open()
Dim xlaName As String
Dim i As Long

With ThisWorkbook
If .IsAddin = True Then
For i = 1 To AddIns.Count
xlaName = AddIns(i).Name
If xlaName = "xlamtest.xlam" Then
If AddIns(i).Installed = False Then
AddIns(i).Installed = True 'hangs on this line for some reason; continue works
End If
AddMenu
End If
Next i
End If
End With
End Sub
Sub AddMenu()
KillMenu
Dim ctrlMain As CommandBarPopup
Dim ctrlItem As CommandBarControl
Dim iHelpIndex As Long

Dim cbHelp As CommandBarControl
Dim cbWorksheet As CommandBar
Set cbWorksheet = Application.CommandBars(1)
Set cbHelp = cbWorksheet.FindControl(ID:=30010)
iHelpIndex = cbHelp.Index

'build menu
Set ctrlMain = cbWorksheet.Controls.Add _
(Type:=msoControlPopup, Before:=iHelpIndex, temporary:=False)

With ctrlMain
.Caption = "TestMenu"

Set ctrlItem = _
.Controls.Add(Type:=msoControlButton)
With ctrlItem
.Caption = "Test Menu Item"
.OnAction = "ThisWorkbook.ItWorks"
End With

End With

End Sub

Sub KillMenu()
Dim cmdBar As CommandBar
On Error Resume Next
Set cmdBar = Application.CommandBars(1)
cmdBar.Controls("TestMenu").Delete
On Error GoTo 0
End Sub
Private Sub ItWorks()
MsgBox "holy cow, it works?"
End Sub

note that the file should be called 'xlamtest' for the menu to be added....

I'm hoping there's something silly I'm doing wrong. I did try adding the default add ins folder to the trust center...no luck. it's also frustrating that excel is locking up on me now whenever I try to run this code...changing the filename worked once, but then it started locking up again.

any help is appreciated. my goal is to take an add in that works on 97-2003 and update for 2007.

if you think I should just forget the automated installer, tell me that too! although in this case, I cant even get the xlam to work when I manually save as xlam...weird. testing yesterday indicated that if I just changed the extension from xlam to xla, it would work (did that to test for corruption). If I didnt want to manipulate the ribbon just a little bit, I wouldnt even bother -but putting the menu in the 'add ins' tab just doesnt look right, given how you interact with excel on everything else...

EDIT - just to be clear, things work fine if running the code from an xls workbook to create an xla...

tpoynton
08-04-2007, 11:59 AM
I apologize for having these conversations with myself; just wanted people who have read this to know there's an update...

I found out why excel was crashing; I should have had the line above where it hangs set to true instead of false...anyway, I've attached a .xlsm that works at creating a .xla, but not a .xlam. by setting the .saveas fileformat to xlAddIn8 (for 97-2003 add ins; xlAddIn is for 2007 add ins, but that's what is not working) and setting the name where needed to xla instead of xlam, it appears to work just fine, even after restart.

so, perhaps more evidence that it's that saveas line that is at the root of my problem...is it not possible to programatically save to a xlam? is this a bug in excel 2007?

thanks to those who have perused this post...tim

Bob Phillips
08-04-2007, 03:02 PM
I think xlAddin8 is Excel 2007, not the other way. Doesn't matter though, they both resolve to 18 (at present).

tpoynton
08-04-2007, 05:49 PM
Thanks Bob - according to the help file for xlFileFormat Enumeration, xlAddIn is for 2007, and xlAddIn8 is for 97-2003. You are correct that the value for both is 18, but that doesnt mean much to me :)

I did try using xlAddIn8 with the xlam extension (since I'd take your advice over the help file any day), and still got the same error message after restarting Excel (although it works before restarting, which I just find odd). If I manually change it from xlam to xla, it works just fine...should I give up? My guess would be that my attempts to do this are futile until xlAddIn8 and xlAddIn 'resolve' to different values...I am disappointed, as I am real happy with the automated installer. Looks like people with the latest and greatest in Excel will need to manually install their add in...

well, maybe not; since this is excel 2007 specific (I usually try to have all my stuff work on macs and PCs), perhaps I can create an exe? any free installer packages with enough documentation that someone without knowledge of much more than VBA can use? and installer for dummies? I did a Google search looking for code (or a start) to do this with Inno Setup, as I have heard a lot about that. I found what may be a start with NSIS...looks like I'll be learning something new tomorrow!

I know this is a little off topic of the original question I posed, but the spirit is the same!

Bob Phillips
08-05-2007, 05:50 AM
Thanks Bob - according to the help file for xlFileFormat Enumeration, xlAddIn is for 2007, and xlAddIn8 is for 97-2003. You are correct that the value for both is 18, but that doesnt mean much to me :)

What it means is that when the compiler does its stuff, it compiles the constant to its value, so whether you use xlAddin or xlAddin8, it makes noi difference as both compile to 18. I would guess it has been added for documentary purposes.


I did try using xlAddIn8 with the xlam extension (since I'd take your advice over the help file any day), and still got the same error message after restarting Excel (although it works before restarting, which I just find odd).

I wasn't suggesting that one might work where as the other wouldn't, quite the opposite, but just pointging out the fact.


If I manually change it from xlam to xla, it works just fine...should I give up? My guess would be that my attempts to do this are futile until xlAddIn8 and xlAddIn 'resolve' to different values...I am disappointed, as I am real happy with the automated installer. Looks like people with the latest and greatest in Excel will need to manually install their add in...

I will take a look at it when I have some time, and see if I can track it down.


well, maybe not; since this is excel 2007 specific (I usually try to have all my stuff work on macs and PCs), perhaps I can create an exe? any free installer packages with enough documentation that someone without knowledge of much more than VBA can use? and installer for dummies? I did a Google search looking for code (or a start) to do this with Inno Setup, as I have heard a lot about that. I found what may be a start with NSIS...looks like I'll be learning something new tomorrow!

No, don't use nsis, it is quite complicated. Go for Inno Setup, it is far more intuitive, still free ... a great product.

Installing the files is simple, easy peasy, but the big problem with Excel addins is registering them. The addin gets a key of HKCU\Software\Microsoft Office\version\Excel\Options\OPENx, where x is the next free number. So you need code to loop through the keys and find the next free number, and assign that. No installer that I know of provides that facility, but you can script it. I am in the process of writing such a script for Inno, which should be ready in a few days.

tpoynton
08-05-2007, 12:34 PM
Thanks for the explanation on what the xladdin value means :). it makes sense...now!

I'll try inno setup. I've decided in the short-term to actually develop some excel 2007-specific content before worrying about the installer, and I'd like to try and figure it out - but if I run into trouble, you'll be sure to hear from me! As always, thanks Bob! tim