Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 97: Add-In (xla) Path

Posted on 2002-05-09
6
Medium Priority
?
401 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Accepted Solution

by:
macbone2 earned 600 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

773 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