Excel Addin Reference

[fredlo2010]

Hello guys,

I am asking this question in the off-topic section because I am not sure if it would qualify as a real topic.

So i have this huge code and I want to put it into an addin so I can have it centralized and I can make modifications to it without necessary having to change the code in each individual workbook.

The workbook (not the add in) will sit on the server and it will be copied to different locations as necessary; making the code very hard to track.

I would like to make a reference to the Addin file (which will also sit in the server) so everyone always has a fresh copy of it. This of course as long as the file structure does not change. Although that part can also be checked withing the code. (future ideas)

I have used referencing and addin in the past but never between so many computers and RDP sesions. The only possible problem that would come to my mind is the missing file.

Any suggestions, comments, personal experience with this ?

Thanks lot for the help,

Alfredo

[Zack Barresse]

If you have an add-in centrally located and install it on a users machine, I believe it will copy the file locally, but I could be mistaken as I haven't done it in a number of years. There's really no good way to update code with VBA-based add-ins, unless you want to build it. You could code something (outside of Excel) to close Excel, copy/paste the file over the original, but it starts getting very mucky at that point. In all fairness VBA isn't the platform for keeping up-to-date distributed, whereas a managed code solution is. Ultimately I'd recommend either C# or VB.NET to perform this, but it can be a steep learning curve for some (myself included!).

Speaking to referencing other VBProjects, I generally tend to stay away from it, as it's only ever caused me headaches. Probably the most elegant solution I've ever seen with VBA was a routine to check for updates (looked to a specific network location, although you could keep it web-based if you'd like), if a file was found (i.e. you put a new file in for updating), generally the VBA code is put in a text file, your routine would delete the specific module you're updating and put in the VBA code from the file. This would obviously require a reference for programmatic access to the project.

All in all, generally speaking it's not that difficult to send an add-in to your users and say 1) close Excel, 2) copy/paste this file in xyz directory, 3) open Excel and enjoy life.

[Bob Phillips]

I have just built an application for a customer whereby I put the addin in a specific location on the network, I set some file properties that can be tested in script. Each user has a login script that checks their addin to see if it is uptodate, if not the server version is imported. I didn't write the scripts, but it works brilliantly.

[fredlo2010]

Hi guys,

Thanks a lot for the replies.

Yeah I see this can be a little bit of a pain. I think I will use the reference.

with a single module and procedure on the file, like:


sub Run_Report

Call ReportHome

End sub



The addin file will always be in the sever.
All computers work through RDP the local computer is barely used.
The computers have to be connected to the network for the Macro to work.
I could make a small check to see if the addin exists through vba.


Did I missed anything from your answers? maybe I missed everything. lol

By the way having a centralized code is not optional it has to be with an addin.xlam or file .xlsm (:yes boss orders, now I have to make sure I never reveal my forum username! :))

Thanks a lot

Alfredo

[SamT]

:dunno:

By the way having a centralized code is not optional it has to be with an addin.xlam or file .xlsm (:yes boss orders)
Convince him to use a *.bas file.

Following Zack's idea:

Instead of an add In, put the code modules in the workbooks and replace them as updated.

The Module(s) have a Version Constant, (= Version Date).

Inside the Update Folder:
0 Byte file(s) named "VersionDate & "." & ModuleName(s)
The updated module(s) (ModName.bas files)

In individual station Workbooks, compare the internal Version Constant(s) to the date part(s) of the VersionDate.ModName file(s).

For Each Module in Project
If Dir(Path & "*.ModName") > Project.ModName.VersionConstant Then
Project.ModName.Delete
Project.Import(Path & ModName.bas)

[fredlo2010]

The Module(s) have a Version Constant, (= Version Date).



I was browsing and learning about the VBE objects but I could not see the Module Version Property. I also looked in MSDN but the information regarding this objects are a little limited.

Any ideas.

Thanks

[Bob Phillips]

I don't think this is at a good approach. Programming the VBE is fine for just yourself/ourselves, but in a production environment, you are unlikely to find that most users have trusted access to VBA, getting them to set it is harder than getting them yto install an addin, and their IT team probably won't let them anyway.

[SamT]

Fred, I meant to create a constant

Public Const ModVersion As String = "03/17/2014"
Public Const ModVersion As Long = 20140317

Change it in each new version you export to the repository.

[Zack Barresse]

Bob's got a valid point. It will be difficult if you go that route, inherent with it's own degree of difficulty, which might not make sense to use and add that layer of complexity to your situation.

[fredlo2010]

All in all, generally speaking it's not that difficult to send an add-in to your users and say 1) close Excel, 2) copy/paste this file in xyz directory, 3) open Excel and enjoy life.

But the good part of the reference is that makes the calling of procedure smaller (no need to include the full path) I am dealing with paths that will wrap in two lines.

So many options but they all seem to have a down side. I guess i will have to deal with consequences later on :)

Thanks for all the help.

[Aflatoon]

Unless I missed something, you should just put the add-in on the server and have the users run it from there: http://www.excelguru.ca/content.php?152-Deploying-Add-ins-in-a-Network-Environment