Determine path of .xla

[Luch]

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

[RetiredGeek]

Jonas,

Here's some code you can adapt as necessary for your needs.

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.

[Luch]

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

[RetiredGeek]

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.

[Luch]

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!

[RetiredGeek]

Jonas,

Give this a try. It should work but I don't have the addin to test it with.

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

[rory]

You need:

ThisWorkbook.Path

[BigDocD]

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.