.xlam installation problem

Hi!

I have created a VBA based Add-in and have distributed it for a group of people at my company. I am distributing it out as an .xlsm file so the users don't have to find the correct file path to save it in (we have a blend of operating systems and Office versions, most Office 7, a few legacy XP, many Office 2007 and 2010 and a few 2013s). I have one user who when saving the file as .xlam isn't changing the file path and is enabling the macro properly but when he does it, it doesn't add my custom tab to the ribbon and instead opens up the .xlsm file.

As a temporary solution I'm having them drop their tabs with charts (it's a chart formatting add-in) straight into the enabled .xlsm file with the macros enabled to get the functionality but I haven't determined why this is happening. They are using Windows 7 64 bit with Office 2010. Others with this same setup have successfully installed the Add-in and are using it now.

Any suggestions on perhaps a different way to distribute the Add-in or any insight as to what is causing this?

Thanks for any suggestions! :)
FoxPerl
FoxPerlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkpieterseCommented:
Installing the addin is quite simple through VBA (this code is run from the xlam file!):

Public Sub CheckInstall()
    Dim oAddIn As AddIn
    If IsInstalled Then Exit Sub
    If MsgBox("Do you wish to install the addin?", vbQuestion + vbYesNo, GCSAPPNAME) = vbYes Then
        Set oAddIn = Application.AddIns.Add(ThisWorkbook.FullName, False)
        oAddIn.Installed = True
    End If
End Sub

Public Function IsInstalled() As Boolean
    Dim oAddIn As AddIn
    If ThisWorkbook.IsAddin Then
        For Each oAddIn In Application.AddIns
            If LCase(oAddIn.FullName) = LCase(ThisWorkbook.FullName) Then
                If oAddIn.Installed Then
                    IsInstalled = True
                    Exit Function
                End If
            End If
        Next
    Else
        IsInstalled = True
    End If
End Function
0
FoxPerlAuthor Commented:
Hi! Thank you for the quick comment! Do I have to put this code in a special area of the VBA like onlaunch or something like that? or they could click an install button? Would they have to enable their macros to run this code I'm guessing?

Thank you again!
Lauren
0
FoxPerlAuthor Commented:
Also, I'm assuming this will only save it to the correct spot and the user still has to enable it?
Just checking!

Thanks
FoxPerl
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jkpieterseCommented:
You would put a call to the "CheckInstall" routine in the Workbook_Open event in the Thisworkbook module.
It would make the addin installed in whichever folder they opened it from. But of course they first must enable macro's to make it all work :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FoxPerlAuthor Commented:
Thank you! I'll give this a try :) Thanks for the Workbook_Open too... I've heard of that before but it was a long time ago.
0
FoxPerlAuthor Commented:
oy! Ok so I added the code to ThisWorkbook and in Workbook_Open I add a call to CheckInstall and saved the file to an .xlam file (since the function checks to see if its an add-in). So I deleted my installed copy and opened the Add-in to test the code and I get a Complie Error: Variable not defined on "GCSAPPNAME". I thought I'd be slick and see if just deleting Option Explicit would help at all and I get through the code and get stuck on the line below... Set oAdd-In = Application.AddIns.Add(ThisWorkbook.FullName, False). I get a Run-time error '1004': Application-defined or object defined error. Unable to get the Add property of the AddIns class.

Any idea what's up? Sorry and thanks!

FoxPerl
0
FoxPerlAuthor Commented:
oo! So I found some troubleshooting around that inability to reference the Add method (an excel workbook has to be open so they recommend creating a temporary workbook and then closing it). Link to this post here.
http://vbadud.blogspot.com/2007/06/excel-vba-install-excel-add-in-xla-or.html

I basically just need to find the ideal spot in the code to place this. Currently where I put the code now doesn't install the add-in unless I say close to that message box which is weird. Almost there!

This is where I put it in now... I think I just need to play around with the placement of where I put my temporaryworkbook.

added code...

Public Sub CheckInstall()
   
Dim thisAddIn As AddIn
Dim temporaryWorkbook As Workbook   '   New line!

If IsInstalled Then
   MsgBox "This Add-In is already installed successfully", vbExclamation, "Add-In successfully installed"   '  I'll probably take this line out and put it to how it was recommended but I like this for testing
   Exit Sub
End If

If MsgBox("Do you wish to install the addin?", vbQuestion + vbYesNo, GCSAPPNAME) = vbYes Then
   Set temporaryWorkbook = Workbooks.Add   '  New line!
   Set thisAddIn = Application.AddIns.Add(ThisWorkbook.FullName, False)
   thisAddIn.Installed = True
   temporaryWorkbook.Close   '  New line!
End If
   
End Sub
0
FoxPerlAuthor Commented:
I still am unable to have the addin recognize it is installed... the msgbox in the CheckInstall sub keeps opening and I keep selecting Yes to install. I think the add-in installation code is having a couple problems...

In the sub CheckInstall I don't think the "If IsInstalled Then Exit Sub" call to the function below is working properly. In the IsInstalled function I think it is getting tripped up on the line "If oAddIn.Installed Then". I've stepped through the add-in installation a couple times and it never recognizes that it is installed even though it is. Is there possibly a way to cache it's knowledge of installed addins perhaps? Once it's installed and I open an excel file Excel does recognize this Add-in is installed so I think it's just not recogizing it immediately.

I also think in the CheckInstall sub that "oAddIn.Installed = True" causes a fresh version of the addin to open that  steps through the code all over again causing the msgbox to open again which is pretty bizarre.

Workaround: It works if I first select "Yes" I want to install to the add-in and then I need to click "No" I don't want to install which doesn't make any sense if I'm distributing this to users to do on their own.



Current code with the temporary workbook opening and closing which is allowing Applications.AddIns.Add to work...

Private Sub Workbook_Open()

CheckInstall

End Sub

Public Sub CheckInstall()
   
Dim thisAddIn As AddIn
Dim temporaryWorkbook As Workbook

If IsInstalled = True Then Exit Sub

Set temporaryWorkbook = Workbooks.Add

If MsgBox("Do you wish to install the addin?", vbQuestion + vbYesNo, GCSAPPNAME) = vbYes Then
   Set thisAddIn = Application.AddIns.Add(ThisWorkbook.FullName, False)
   thisAddIn.Installed = True
End If
   
temporaryWorkbook.Close

End Sub

Public Function IsInstalled() As Boolean
   
Dim singleAddIn As AddIn

If ThisWorkbook.IsAddin Then
   For Each singleAddIn In Application.AddIns
      If LCase(singleAddIn.FullName) = LCase(ThisWorkbook.FullName) Then
         If singleAddIn.Installed Then
            IsInstalled = True
            Exit Function
         End If
      End If
   Next
Else
   IsInstalled = True
End If

End Function
0
jkpieterseCommented:
Sorry about the dangling GCSAPPNAME variable! Also sorry about the no workbooks problems, I forgot about that one, which I solved in another part of the code, which I did not post:-(

Try if changing this part:

Private Sub Workbook_Open()
 CheckInstall
 End Sub

to this:

Private Sub Workbook_Open()
 Application.Ontime Now(), "CheckInstall"
 End Sub

solves the problem with the code running twice.
0
FoxPerlAuthor Commented:
OOOOOOOO THANK YOU THANK YOU!
I'm still pretty new to all this so all your comments and quick feedback were awesome!
I made another rookie mistake of putting the code in the ThisWorkbook area but then bumped it out to a module and now it's working perfectly!!! :D

Thanks for all your feedback for this rookie :)
FoxPerl
0
FoxPerlAuthor Commented:
Yay Experts Exchange and jkpieterse.
0
jkpieterseCommented:
You're welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.