Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Add-In Problem on Network Servers (Excel 20

    Hello you all,

    I found this thread which is close, but doesn't quite answer my concerns: Post 380241

    We are on a Novell Network environment, running Windows XP Professional, and Microsoft Excel 2000.

    I built the custom functions in a Module, tested them locally, and then did File | Save as | Save as Type: **.xla | sheetinfo.xla.
    Then, in a new workbook selected Tools | Add-Ins | Browse to network drive folder, selected "sheetinfo.xla"
    Eventually, Excel requires confirmation that I want to copy sheetinfo.xla to a directory on my local C: drive in Cocuments and SettingsusernameApplication DataMicrosoftAddIns
    I confirmed, Excel completes the copying, and the user-defined functions worked as expected.

    <hr>' Added 22 Feb 2006 by me
    ' Derived from Allen Wyatts Excel Tip
    ' http://exceltips.vitalnews.com/Pages/T0131...sheet_Name.html

    Function SheetInfo(numWanted As Byte) As String
    Select Case numWanted
    Case 1
    SheetInfo = Application.Caller.Parent.Name
    ' Returns the worksheet tab name
    ' ActiveSheet.Name old way to get the worksheet name
    Case 2
    ' Returns the workbook name
    SheetInfo = Application.Caller.Parent.Parent.Name
    ' SheetInfo = ThisWorkbook.Name
    Case 3
    ' Returns the workbook full path and name but not worksheet name
    SheetInfo = Application.Caller.Parent.Parent.FullName
    ' SheetInfo = ThisWorkbook.FullName
    Case Else
    SheetInfo = ActiveSheet.Name
    End Select
    End Function<hr>

    I distributed this Custom Add-in across the network. Users followed steps above to enable the Add-in on their profile. For Excel workbooks created on their local profile, the user-defined functions worked as expected.

    Here's the trouble encountered. When another networked user (with the Custom Add-in installed in their local profile) attempts to open an Excel workbook on a network drive that I saved, the user gets the following message:
    <blockquote><hr>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Add-In Problem on Network Servers (Excel 20

    These problems are to be expected, since the add-in is stored in a user-specific location. To get around it, you should either store the user-defined function in each workbook that needs it, or store the add-in in a fixed (not user-specific) location. A good location would be

    C:Program FilesMicrosoft OfficeOfficeLibrary

    This path should be present on all PCs with a standard installation of Office 2000, and it is used for some of the add-ins that come with Excel. You'd have to click No if Excel asks whether it should copy the add-in to your profile.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Add-In Problem on Network Servers (Excel 20

    All you did wrong was to answer "Yes" to Excel offering to copy the addin. If you answer "No", Excel installs the add-in, but leaves it at the original location, which is what you want.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Add-In Problem on Network Servers (Exce

    Dear Hans,

    Thanks so much for your reply. I did really think I was going bonkers!

    I do like your suggestion about:
    <hr>C:Program FilesMicrosoft OfficeOfficeLibrary<hr>
    I saved sheetinfo.xla in the above library and opened Excel, selected Tools | Add Ins, but SheetInfo add-in is not available for selection. I tried creating a folder in that directory, AddIns. That didn't work either. I haven't tired rebooting yet. So that brings up this question.

    Question: In Excel (or Office) is there a setting only IT tech's with admin rights can tell Excel where to find user-defined/custom add-ins?

    If so, perhaps I can negotiate with IT management to change the current setting that hooks into the user profile to your suggested more generic location. We do not have "roving" profiles on our Novell network.

    Best regards,
    Rich

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Add-In Problem on Network Servers (Exce

    > but SheetInfo add-in is not available for selection

    Have you tried clicking the Browse... button? You only need to locate the .xla once, thereafter it will be listed in the Add-ins dialog itself.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Add-In Problem on Network Servers (Exce

    Dear Hans,
    <hr>Hans said:
    Have you tried clicking the Browse... button? You only need to locate the .xla once, thereafter it will be listed in the Add-ins dialog itself.<hr>
    Okay, so I made an assumption. I expected that Excel would copy from the directory C:Program FilesMicrosoft OfficeOfficeLibraryAddIns to the directory Cocuments and SettingsusernameApplication DataMicrosoftAddIns
    That's why I didn't browse to it..

    As you suggested, I simply browsed to the directory C:Program FilesMicrosoft OfficeOfficeLibraryAddIns, selected sheetinfo.xla. Excel acknowledged the add-in, left it in this directory, and away we went! Voila! It works like perfectly.

    Now I'l have to try this with other users on the network.

    Again, you are a genius, Hans, and a very great help.

    Best regards,
    Rich

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Add-In Problem on Network Servers (Exce

    As I said, you should be able to browse to the addin on the network share, say NO to the offer to copy it to the addins library and be up and running too.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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