Setting the Installed Property to True is failing for the AddIns Class

[devilishblueyes]

I'm trying to make my VBA addins simpler to install for my co-workers so that they can install and update them to their computer with basically just the click of a button. I have been able to get the code to work fine for my computer and another co-worker's computer. But when I tried to install it on a 2nd worker's computer it keeps failing when I try setting Installed to True. I'm using a .XLSM file on a shared server that has a command button on it that when it is clicked does the following:

1. Determines if there are any old addins with the same name and removes them

Sub rmvAddIn(MyName As String)
If HasAddIn(MyName) = True Then
AddIns(MyName).Installed = False
End If
End Sub

Function HasAddIn(MyName As String) As Boolean
Dim MyAddin As AddIn

Err = 0
On Error Resume Next
Set MyAddin = AddIns(MyName)
If Err = 0 Then HasAddIn = True Else HasAddIn = False
Set MyAddin = Nothing
On Error GoTo 0

End Function

2. Copies the add in files from the server and adds them to the AddIns folder as an Addin

Dim MyName() As String
ReDim MyName(1)

MyName(0) = "MyCalculator"
MyName(1) = "MyEntries"

Call rmvAddIn(MyName(0))
AddIns.Add ThisWorkbook.Path & "\" & MyName(0) & ".xla", True

3. Installs the AddIn

AddIns(MyName(0)).Installed = True


It is the last line where it fails on the one computer, yet it works perfectly fine on my computer and another. I've researched it on the web and can't seem to find an answer anywhere. I'm not having difficulty adding the AddIn. It shows that it has been added and by using the command button in .XLSM file it forces at least one workbook to be open. I've tried different variations of Excel Security settings and that hasn't worked. And I've even tried looping through the AddIn objects like so:

Dim MyObj As AddIn

For Each MyObj IN AddIns
If MyObj.Name Like "MyCalculator*" Then
MyObj.Installed = True
Exit For
End If
Next MyObj

And even that wouldn't work. Even tried with one line:

AddIns.Add(ThisWorkbook.Path & "\" & MyName(0) & ".xla", True).Installed = True

So the AddIn definitely exists when I try to install it. I'm at a total loss about how to tackle it. The only article that made some sense was something someone wrote where they found that in their case it wasn't clearing out the name for their addin in the registry. I normally try to avoid working with the registry since I don't know a lot about it and don't want to majorly screw up the computer.

Please help!

 

[RoryA]

Do you actually need locally installed copies? It is much simpler to maintain one network copy if possible.

 

[devilishblueyes]

Do you actually need locally installed copies? It is much simpler to maintain one network copy if possible.

Yes, I would think so. The program has individual settings for each person. And it is used by multiple people quite frequently. I'm not sure if one AddIn file could run macro programs on multiple computers at once. Maybe there's a way to do that, but I don't know how. If it was a macro that was used infrequent that might make sense I think.

 

[RoryA]

Each user would get a local copy in memory for the duration of the session so multiple users is not an issue and if you store settings in the registry (better than in the addin) those are not a problem either. See Ken's article here.

 

[devilishblueyes]

Each user would get a local copy in memory for the duration of the session so multiple users is not an issue and if you store settings in the registry (better than in the addin) those are not a problem either. See Ken's article here.

Ok, that's a good article, I like it a lot and it definitely makes sense for using on a multi-user platform. My only question now is: "How do I set/store their settings/data to their local computer?"

I'm totally unfamiliar with storing stuff like that to the registry. As far as settings, there are only a few. The first logs their clock number on the install so when they do a search on a local website it opens the web search under their clock number. It took me a little bit to figure that one out because I had to go through the source code of the website and identify the right tags to go to, to retrieve and enter data at. The other records their favorite storage folder locations for when they go to save their file the folders pop up in a listbox with a checkbox for each item to be able to save to whatever multiple folders of their favorites they want to by checking which folders they want to save to. These two probably aren't quite so hard to record.


What might be difficult to record is they fill out a user form that has over 100 entries, basically comprised of just text boxes and combo boxes. The program allows them to save up to 20 forms that they've filled out. I'm sure there is definitely a better way than how I'm doing it. For those entries, I had created a 2nd add-in used to just store that data. The size of the main add-in is so huge because of all the user forms that I saved the data to another smaller file so that the data is saved faster. However it runs into a problem when the user has two Excel applications running because the first uses the actual file and the 2nd session of the application accesses only a copy of the AddIn file and they can't save to the copy. I semi addressed that by having a message pop up when the computer tries to use Save As for the AddIn instead of just Save. At first I had tried identifying the number of Excel sessions, but with Windows 7 compared to XP they made it much tougher to identify how many Excel sessions are open. I can guess there is probably a much better way of saving such data. If you know of it, I'm totally open to listening and learning. I'm really good at VBA in some respects from working with it for a long time (since 1997) and reading books, but I'm not a professional programmer that has worked in multiple programming languages and is used to doing stuff like storing stuff to the registry or browsing through it, etc.

 

[RoryA]

The simplest method in VBA is to use GetSetting and SaveSetting. From what you describe, I'd probably save the form info as a delimited string (depending on length) or use a locally stored text file (a bit like an .ini file) that you can read/write to as required. It should make it simple to update the addin without affecting any existing settings they have saved.

 

[devilishblueyes]

The simplest method in VBA is to use GetSetting and SaveSetting. From what you describe, I'd probably save the form info as a delimited string (depending on length) or use a locally stored text file (a bit like an .ini file) that you can read/write to as required. It should make it simple to update the addin without affecting any existing settings they have saved.

Is there a max character length that can be used for the delimited string? I haven't worked a lot with delimited strings and text files. Some of the entries will likely have commas in them. In those cases can I surround the entry with quotation marks to overlook the commas in the entry in case like say I have a Text file formatted as a .CSV file and that was to be converted to an Excel file. Or should I use a different character for separation such as a semicolon ( that would basically never get used in the entries and I could possibly put entry handling code to prevent the typing of that character in an entry. And I'm sure I could probably use a carriage return or line feed in the delimited string. Just am thinking how to organize it to set it up. But you're definitely helping me put the whole big picture together. I really appreciate your help!