CODEWITHSUNDEEP
vbaexcel
Daniel Bencik
Daniel Bencik

Reputation: 969

VBA - Relative vs Absolute References to XLAM files

my colleague developed an application contained in an xslm file, which has to be accessible to me as well. The file connects to a lot of xlam libraries which are saved locally on his machine due to speed reasons (loading 30 libraries from a shared drive takes ages).

In order for me to access this application a copy was created on a shared drive, where the libraries were copied as well. When my colleague opens another applications (which references the his local xlam libraries) and then the shared xlsm file in one instance of Excel, the references in the shared file get diverted from the shared xlams to the local xlams, making the shared application dysfunctional to me - I cannot access his local xlams. Can this automatic change of xlam references be prohibited? Is there a way to hard-code paths to xlam references just as one would reference a DLL in VBA?

Any help is much appreciated, Daniel

Upvotes: 1

Views: 1960

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

These xlam files are addIns.

You should be able to reference them at runtime using the path. Here is an example I found for you on MSDN Blog: Accelerating Things (Loading Excel Add-Ins at Runtime) (you could find this with a Google search)

Private Sub Workbook_Open()
    call LoadExternalAddIns
End Sub

Private sub LoadExternalAddIns(optional byval sAddinName as string="")

    Dim success As Boolean
    Dim myAddIn As AddIn
    dim sPath2AddIn as string
    'dim sAddinName as string

    sPath2AddIn = "c:\myaddins\"
    if sAddinName="" then sAddinName="myxlam.xlam"

    ' Load XLL
    'success = Application.RegisterXLL(sPath2AddIn & sAddinName)

    ' Load and install new XLAM
    Set myAddIn = Application.AddIns.Add(sPath2AddIn & sAddinName)
    myAddIn.Installed = True

    ' Load known XLAM
    For Each myAddIn In AddIns
        If myAddIn.Name = "myknownaddin.xlam" Then
            myAddIn.Installed = False
            myAddIn.Installed = True
        End If
    Next    
End Sub

Of course, this is just a sample to get you started, in fact as you have lot's of AddIns, you might want to list them on a hidden worksheet then call the above procedure LoadExternalAddIns maybe passing the name for each one in a loop such as call LoadExternalAddIns (sAddInName).

More Resources:

Upvotes: 2

Related Questions