Excel 97: Add-In (xla) Path

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. So, if somebody take his/her workbook home and update 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 a 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 belowed admins b) people are not able to keep them in sync. Arghhh...

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

LVL 13
Who is Participating?

Improve company productivity with a Business Account.Sign Up

macbone2Connect With a Mentor Commented:
cri, I dont want to misunderstand this, but can you accomplish what you want to do by storing the xla in a specific alternate startup folser, then resetting the alternate setup folder on opening the workbook at work.

e.g. in the Workbook open macro, something like:-

Private Sub Workbook_Open()
Dim servername As String
servername = "\\Myserver\"
On Error Resume Next
ChDir servername
If Err.Number = 0 Then
altpath = servername & "MyFolder"
    With Application
        .AltStartupPath = altpath
    End With
End If
End Sub

so opened in work, the server should be found and set as the alternate.....or am I on the wrong track here?

I don't know if I've understood your problem properly. I have VBA macros in a number of workbooks. Sometimes, if a server is unavailable, workbooks may have to run from a different server. In many of the macros I have path names defined, but I always have a range in each workbook defined as SERVER_NAME. Then a string variable for the server name e.g Public ServerName as string.
Then when filling variables later: -
ServerName = _
If users take workbooks home, you could use a macro to check for existence of the server, and then substitute C: for SERVER_NAME. When running on the server, uses value in named range. I don't know if you can use this method in the function. The advantage of course if you can use it, is that if your "beloved" admin peeps do change servers etc. you only need to edit 1 range in each workbook (you can do this with another macro if you keep the sheet name and range name consistent.
Hope this helps.
Geoff (macbone2)
criAuthor Commented:
macbone2, thank you for tackling this one. I do not have any hardcoded paths in my functions. Excel/Microsoft seems to handle functions in a xla like a Link. And the problem is not limited to server drives, all it (seems to) take is 2 Excel 97 configurations having a different xlstart directory.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

criAuthor Commented:
macbone2, how to proceed on this one ? (A)bort, (R)etry, (I)gnore ?
criAuthor Commented:
macbone2, this solution requires the _workbook_ having the path setting macro. However, my xla contains general purpose functions (i.e. what Microsoft should have included in version 3 or 4...). Either I try to explain to the users how to include this code or I have to make an macro which does this, either automatically or manually. Whereas this certainly is an option, I will not pursue it as it complicates the stuff beyond 'in-house' practicability.
criAuthor Commented:
macbone2: Accepting comment to settle this question. Thank you for tackling this one.
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.

All Courses

From novice to tech pro — start learning today.