Solved

Excel 97: Add-In (xla) Path

Posted on 2002-05-09
6
389 Views
Last Modified: 2008-03-03
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 ?

0
Comment
Question by:cri
  • 4
  • 2
6 Comments
 
LVL 2

Expert Comment

by:macbone2
ID: 7000064
cri,
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 = _
Sheets("Sheet2").Range"SERVER_NAME").Text
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)
0
 
LVL 13

Author Comment

by:cri
ID: 7000739
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.
0
 
LVL 13

Author Comment

by:cri
ID: 7119700
macbone2, how to proceed on this one ? (A)bort, (R)etry, (I)gnore ?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 2

Accepted Solution

by:
macbone2 earned 150 total points
ID: 7121332
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?

Geoff
0
 
LVL 13

Author Comment

by:cri
ID: 7122912
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.
0
 
LVL 13

Author Comment

by:cri
ID: 7122925
macbone2: Accepting comment to settle this question. Thank you for tackling this one.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now