xla Path (VBA/Excel 97)

Topic

New Reply

Situation: I developed an own Excel 97 add-in (xla). The problem I have arises from the the fact that the xlstart directory at work is on a network drive (but it does not have to be a network drive, a different path is all it takes). So, if somebody take his/her workbook home and updates it and takes it back to work, all calls to my functions will have the full home xlstart path, and vice-versa.

In a simple to medium workbook a search and replace for each worksheet is the (tedious) cure, for complex workbooks the calculation can be derailed to the point where even a Ctrl+Alt+F9 does not help anymore, then all the calculation cells have to be refreshed individually by ‘F2+Enter’.

The obvious solution is to set up a alternate startup directory but this gets:
a) overwriten by our admins
people are not able to keep them in sync.
Arghhh…

Thefore: Is there a solution except to compile the _functions_ into a dll, because there the problem does not arise ?

Reply | Quote

Replies

Actually, there is another way…

1. Either convince IT to push the .XLA to each user’s C: (local) drive or get it to them yourself, somehow
2. When the add-in is added, simply deny having it copied to the XLstart directory. This will result in the .XLA being run from its original directory.

I had about the opposet problem: many 20 people often can’t use the same .XLA at the same time. I ended up leaving the original .XLA on the team’s production share (where I could update the .XLA from my development copy as needed). I then gave each member of the team a macro which would:
a) remove the .XLA reference
delete the actual XLstart copy of the .XLA file
c) remove the .XLA’s toolbar (so that any new version of the toolbar would be added when the .XLA was refreshed)
d) Add in the Add-in AND ALLOW EXCELL TO COPY IT TO THE XLstart DIRECTORY.

I set up each team-member’s “personal.xls” with the macro and a button to access it and now all they need do to refresh the Team Toolkit – their .XLA – is click on the ‘refresh’ macro.

Going this route, you might actually have it easier than I did: you only have to imbed the code in _one_ copy of personal.xls (since your team seems to be sharing it).

I’d be more than happy to provide my code, if you want.

Reply | Quote

malkor, thank you. Looks like I will have to make an installation routine and enforce an own directory. A rather big work-around for something which would take M1

Reply | Quote