Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    xla Path (VBA/Excel 97)

    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
    [img]/forums/images/smilies/cool.gif[/img] 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 ?

  2. #2
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla Path (VBA/Excel 97)

    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
    [img]/forums/images/smilies/cool.gif[/img] 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.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xla Path (VBA/Excel 97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •