Solved

Excel 97: Add-In (xla) Path

Posted on 2002-05-09
6
391 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2016 - Dotted line appears, but it's not a page break 14 96
Excel 2016 - Black cell borders 11 34
help with an excel problem 10 30
ProperCase in Excel (Sheet) 3 13
Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 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