none
Want to change .xlam location in Tools/References RRS feed

  • Question

  • Hi,

    I have a couple of spreadsheets (Excel 2007) that have a lot of common code. I have decided to start moving that common code into an .xlam file. Both files can then contain a reference to the .xlam file in the Tools\References collection, thus using the same code. Generally, that works fine.

    However, when it comes to developing the spreadsheets and/or common code further, I want to be able to do this: 

    - Take copies of the Excel spreadsheets into a development folder, change the xlam reference to point to a development version (same name, just different location) - so that I can work on these files and make changes. Then, when finished, I would move the files back to the original location where I would therefore want to change the reference back to the original path. 

    In this way, I can make changes to the common code while I am developing some functionality, leaving the original xlam untouched until I am ready to update it with my newer version.

    The problem with all of this is that there does not appear to be an easy way to change the location of an existing reference.

    I have tried this sort of approach:

    Public Sub ForceLiveRef()
    Dim oRef As Object
        For Each oRef In ActiveWorkbook.VBProject.References
            If UCase(oRef.Name) = UCase(myAddInFullPAth) Then
                RemoveReference oRef
            End If
        Next oRef
        AddReference DEV_LOC_BONDS
    End Sub
    
    
    Private Sub RemoveReference(ref As Object)
        Debug.Print "Removing: " & ref.Name & " " & ref.FullPath
        ActiveWorkbook.VBProject.References.Remove ref
    End Sub
    
    
    Private Sub AddReference(strAddInFullPath As String)
        Debug.Print "Adding: " & strAddInFullPath
        ActiveWorkbook.VBProject.References.AddFromFile strAddInFullPath
    End Sub

    This works, as in it does not generate an error - but when I look in Tools/References in the VBA IDE, the original location is retained. So, it seems to go through the process of removing the reference (I have checked that separately and it does get 'unticked') but when I add the same NAMED reference back in but pointing to a different location, that new location seems to be ignored.

    Is there some obvious workaround/solution please? Is there an altogether different/better way of being able to share VBA code between spreadsheets? Although my issue is not so much being able to share the code, but being able to seamlessly switch between 'live' and 'dev' versions of it....

    Thanks

    Wednesday, February 4, 2015 12:39 PM

Answers

  • Hi _Peteg_,

    I have reproduced your problem. When I call References.Remove method, if I don't reopen the workbook, the VBA editor will not refresh VBAProject list. One solution is to use different names for the test .xlam file and the production .xlam file. This is the most easist way I think, but the VBAProject view will keep the old project. As far as I know, we can't use VBA code to remove the VBAProject from the project explorer.

    If you're familiar with Visual Studio and .Net Framework, I would recommend that you create Application-level or Document-level managed Add-in instead of the Excel add-in(.xla,.xlam). In this way you can use the method provided by VSTO to deploy the add-in application. But if you only want to deploy some VBA code, Excel Add-in(.xla,.xlam) is a good choice.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by _Peteg_ Thursday, February 5, 2015 11:10 AM
    • Edited by CaillenModerator Friday, February 6, 2015 1:33 AM
    Thursday, February 5, 2015 6:52 AM
    Moderator

All replies

  • One workaround I have is that I can remove the reference, save the file and exit Excel. Then, re-open Excel and add the reference that I want. That works fine but seems a little awkward.
    Wednesday, February 4, 2015 1:59 PM
  • Hi _Peteg_,

    I have reproduced your problem. When I call References.Remove method, if I don't reopen the workbook, the VBA editor will not refresh VBAProject list. One solution is to use different names for the test .xlam file and the production .xlam file. This is the most easist way I think, but the VBAProject view will keep the old project. As far as I know, we can't use VBA code to remove the VBAProject from the project explorer.

    If you're familiar with Visual Studio and .Net Framework, I would recommend that you create Application-level or Document-level managed Add-in instead of the Excel add-in(.xla,.xlam). In this way you can use the method provided by VSTO to deploy the add-in application. But if you only want to deploy some VBA code, Excel Add-in(.xla,.xlam) is a good choice.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by _Peteg_ Thursday, February 5, 2015 11:10 AM
    • Edited by CaillenModerator Friday, February 6, 2015 1:33 AM
    Thursday, February 5, 2015 6:52 AM
    Moderator
  • Hi,

    Yes, I am only trying to (quickly!) consolidate some VBA code so will probably stay with the .xlam file, but thanks for your advice here.

    Thursday, February 5, 2015 11:10 AM