JWhite
06-19-2008, 09:03 AM
I hesitate to post this because I'm beginning to doubt my sanity.
I'm developing an Excel add-in which will be distributed to a large number of users and updated regularly so I was very pleased to find John Skewes' code (Thanks, John!) on VBA Express for auto-installing addins. I had to make a few minor changes because some things seem to work a little differently in Excel 2007 but I did a dozen or so installs and re-installs before I started having problems.
My problem now is that about 1 out of 4 re-installs fail. I'm developing in Windows XP SP2 with a completely updated version of Office Enterprise 2007.
I do most of my test installs on a Vista machine (with the same version of Office) but when it fails on Vista I then try it on XP and it fails there, too - consistently. The message I'm getting on Vista is "Excel cannot complete this task with available resources. Choose less data or close other applications." When I click OK I get a VBA error. The failure is occurring when I do the SAVEAS an add-in. However, the problem is not in the VBA SAVEAS instruction, as you will see.
As I said, MOST OF THE TIME the re-install works. I start with saving the XLAM file on my development machine as an XLSM file. When I open that file on another machine it asks if I want to install an update and then it asks me if I want to overwrite the existing file. No problem, usually.
Here's the weird part. If I respond NO to the re-install, I can then install it manually with no problem, i. e., I uncheck the add-in in Excel Options, delete the existing add-in manually and then manually save the file as an add-in. And it functions perfectly. In fact, if I uncheck the add-in and delete the file, I can run the auto-install and it installs the add-in with no problem. Only the re-install has a problem.
I thought that maybe something in the Reinstall code was causing a problem so I stopped the execution of the code after responding YES to the install and BEFORE it went into the Re-install routine and tried to manually install it at that time. When I manually did the SAVEAS I got the same "Excel cannot complete ..." error. So somehow the file had become corrupted before it even got into the Re-install - but nothing had happened yet! I even tried commenting out the first question so it went straight into the re-install and it still failed.
I don't expect anyone to have an answer to this. Right now when I'm ready to send out an update I test the re-install. If it fails, I can make ANY change to my add-in (like adding a blank comment line), re-save it, and the re-install works. Like I said, I'm doubting my sanity.
This is the WORKBOOK OPEN code. Since the workbook opens as an XLSM file it bypasses the upper code. I'm not posting the Re-install code because the problem occurs before it gets there. Keep in mind that if I respond NO I can install the add-in manually. If I respond YES and stop the execution at the next instruction the manual install fails - BELIEVE IT OR NOT!
Private Sub Workbook_Open()
'---------------------------------------------------------------------
' Purpose of sub: Call for installation as an addin if not installed
'---------------------------------------------------------------------
'
Dim sAddinTitle As String, sAddinName As String
Dim sXlamName As String
'
sAddinTitle = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)
sXlamName = sAddinTitle & ".xlam"
' If this workbook is functioning as an add-in, exit - otherwise, install the add-in
If ThisWorkbook.Name = sXlamName Then
' Delete and re-add the Menu short-cuts
AddMenuShortCut
' Load configuration data
GetConfig
' Initialize Application-wide Event trapping
Exit Sub
End If
' This is an XLSM workbook, ready to be installed as an add-in
sAddinName = sAddinTitle & ".xlam"
'
'ask if user wants to install now
If MsgBox("Install " & sAddinTitle & _
" as an add-in?", vbYesNo, _
"Install?") = vbNo Then Exit Sub
'check the addin's not already installed in UserLibraryPath
If Dir(Application.UserLibraryPath & sAddinName) = Empty Then
Run "GLB_InstallAddIn"
Else
Run "GLB_ReInstall"
End If
'
End Sub
.
~Oorang
I'm developing an Excel add-in which will be distributed to a large number of users and updated regularly so I was very pleased to find John Skewes' code (Thanks, John!) on VBA Express for auto-installing addins. I had to make a few minor changes because some things seem to work a little differently in Excel 2007 but I did a dozen or so installs and re-installs before I started having problems.
My problem now is that about 1 out of 4 re-installs fail. I'm developing in Windows XP SP2 with a completely updated version of Office Enterprise 2007.
I do most of my test installs on a Vista machine (with the same version of Office) but when it fails on Vista I then try it on XP and it fails there, too - consistently. The message I'm getting on Vista is "Excel cannot complete this task with available resources. Choose less data or close other applications." When I click OK I get a VBA error. The failure is occurring when I do the SAVEAS an add-in. However, the problem is not in the VBA SAVEAS instruction, as you will see.
As I said, MOST OF THE TIME the re-install works. I start with saving the XLAM file on my development machine as an XLSM file. When I open that file on another machine it asks if I want to install an update and then it asks me if I want to overwrite the existing file. No problem, usually.
Here's the weird part. If I respond NO to the re-install, I can then install it manually with no problem, i. e., I uncheck the add-in in Excel Options, delete the existing add-in manually and then manually save the file as an add-in. And it functions perfectly. In fact, if I uncheck the add-in and delete the file, I can run the auto-install and it installs the add-in with no problem. Only the re-install has a problem.
I thought that maybe something in the Reinstall code was causing a problem so I stopped the execution of the code after responding YES to the install and BEFORE it went into the Re-install routine and tried to manually install it at that time. When I manually did the SAVEAS I got the same "Excel cannot complete ..." error. So somehow the file had become corrupted before it even got into the Re-install - but nothing had happened yet! I even tried commenting out the first question so it went straight into the re-install and it still failed.
I don't expect anyone to have an answer to this. Right now when I'm ready to send out an update I test the re-install. If it fails, I can make ANY change to my add-in (like adding a blank comment line), re-save it, and the re-install works. Like I said, I'm doubting my sanity.
This is the WORKBOOK OPEN code. Since the workbook opens as an XLSM file it bypasses the upper code. I'm not posting the Re-install code because the problem occurs before it gets there. Keep in mind that if I respond NO I can install the add-in manually. If I respond YES and stop the execution at the next instruction the manual install fails - BELIEVE IT OR NOT!
Private Sub Workbook_Open()
'---------------------------------------------------------------------
' Purpose of sub: Call for installation as an addin if not installed
'---------------------------------------------------------------------
'
Dim sAddinTitle As String, sAddinName As String
Dim sXlamName As String
'
sAddinTitle = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)
sXlamName = sAddinTitle & ".xlam"
' If this workbook is functioning as an add-in, exit - otherwise, install the add-in
If ThisWorkbook.Name = sXlamName Then
' Delete and re-add the Menu short-cuts
AddMenuShortCut
' Load configuration data
GetConfig
' Initialize Application-wide Event trapping
Exit Sub
End If
' This is an XLSM workbook, ready to be installed as an add-in
sAddinName = sAddinTitle & ".xlam"
'
'ask if user wants to install now
If MsgBox("Install " & sAddinTitle & _
" as an add-in?", vbYesNo, _
"Install?") = vbNo Then Exit Sub
'check the addin's not already installed in UserLibraryPath
If Dir(Application.UserLibraryPath & sAddinName) = Empty Then
Run "GLB_InstallAddIn"
Else
Run "GLB_ReInstall"
End If
'
End Sub
.
~Oorang