Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2010
    Location
    Denmark
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine path of .xla

    Hi

    I have a problem with determining the path of my xla.

    Lets assume I have an xla-addin called MYADDIN.xla

    In this MYADDIN.xla i have to determine where the MYADDIN.xla is lacated on my harddrive. On easy way to do this is by using Workbooks("MYADDIN.xla").path

    But this requires me to type the name "MYADDIN.xla" in the code. And I'm seeking to avoide this.

    So I'm seeking a procedure that can determine the path of MYADDIN.xla within MYADDIN.xla itself. And that is without actually writing the name "MYADDIN.xla".

    Can anyone help me?

    Best
    Jonas

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jonas,

    Here's some code you can adapt as necessary for your needs.
    Code:
    Sub XLAPath()
        
       Dim oAddIn As AddIn
       Dim zMsg   As String
       For Each oAddIn In AddIns
          If UCase(Right(oAddIn.Name, 3)) = "XLA" And _
             oAddIn.Installed Then
            zMsg = zMsg & "File Path: " & oAddIn.Path & vbCrLf & _
                   "File Name: " & oAddIn.Name & vbCrLf & vbCrLf
          End If
          
       Next oAddIn
       
       MsgBox zMsg, vbOKOnly + vbInformation, _
              "Installed Add-Ins & Locations:"
       
    End Sub


    Edited: Added code to see if Addin was installed to get rid of entries with blank paths.
    Attached Images Attached Images
    Last edited by RetiredGeek; 2011-03-16 at 11:32. Reason: Improved Code
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Aug 2010
    Location
    Denmark
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    Thanks for your fast reply and for your help.

    There is a problem with your solution: I get a list of xla's. Not the one I'm working in (MYADDIN.xla).

    I don't know how to tell excel that the path I need is the xla I'm working in (Without writing the actual phrase "MYADDIN.xla").

    BR
    Jonas

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jonas,

    If I understand your question you want to place code inside of the .XLA that will report its location on the disk?
    If this is the case why not hard code the name in there? Sorry, but I have not coded an AddIn so I don't think I can offer any further advice.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Aug 2010
    Location
    Denmark
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, beacuse I have a three-part enviorement (development, test and production). When I want to shift from development to test I would like to just overwrite the MYADDIN_test.xla with MYADDIN_dev.xla.

    Inside each of these XLA's there should be a procedure to determine weather i'm using the development, test or production xla. Are you following me?

    Thanks for your help!

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jonas,

    Give this a try. It should work but I don't have the addin to test it with.
    Code:
    Sub XLAPath()
        
       Dim oAddIn As AddIn
       Dim zMsg   As String
    
       For Each oAddIn In AddIns
          If UCase(Left(oAddIn.Name, 7)) = "MYADDIN" Then
            MsgBox "Currently Using:" & oAddIn.Name & vbCrLf & _
                   "File Path: " & oAddIn.Path, vbOKOnly + vbInformation, _
                   "Which XLA Version"
          End If
          
       Next oAddIn
       
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You need:
    Code:
    ThisWorkbook.Path
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Lounger
    Join Date
    Dec 2009
    Location
    Toronto, Ontario, Canada
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The easiest way to do this I would think, would be to use a different User Profile for development than your production operational Profile. That way the object/program you're using the .xla with, along with other test files, etc., can be kept in the area associated with the development User Profile. Of course it means you need to sign on to that profile while you're testing, but it would allow you to test to your heart's content, not only the .xla, but everything associated with your testing, until you're ready to move things into your production operational Profile.

Posting Permissions

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