none
VBA Sheet Returns Error "Name Conclits WIth Existing Module, Project, Or Object Library." RRS feed

  • Question

  • I am setting up a testing environment for a major VBA spreadsheet we use so I can do testing without affecting prouctions.

    It has references back to the code files in production,:

    Sub AttachXLA()
        With CreateObject("WScript.NetWork")
            If UCase(.UserDomain) = "PINNSTR" Then
                strFilePath = "\\10.0.0.16\utility$\Internal\ShipperCode 3.0\Shipper.xla"
            Else
                 strFilePath = "\\OMAAC-Server\Pinnacle\Shipper\Shipper.xla"
                        End If
            ActiveWorkbook.VBProject.References.AddFromFile strFilePath
        End With
    End Sub

    so I changed them it it's development path:
    Sub AttachXLA()
        With CreateObject("WScript.NetWork")
            If UCase(.UserDomain) = "PINNSTR" Then
                strFilePath = "\\10.0.0.133\F$\Development\ShipperCode 3.0\Shipper.xla"
            Else
                strFilePath = "\\OMAAC-Server\Pinnacle\Shipper\Shipper.xla"
               xla"
            End If
            ActiveWorkbook.VBProject.References.AddFromFile strFilePath
        End With
    End Sub

    And it errors out here: ActiveWorkbook.VBProject.References.AddFromFile strFilePath by saying: "Name Conclits WIth Existing Module, Project, Or Object Library.""

    Any suggestions on how to fix this so I can continue working in a development environment?

    Thanks!
    Brian

    Tuesday, April 24, 2012 2:32 PM

All replies

  • VBA cannot handle multiple references with the same name (as the name appears in Application.References(x).Name). When adding a reference to another office document it will take the name of the VBA Project, which has no direct relation to the file name. So you have to remove the old reference to the prod version before you add the reference to the dev version, OR change the name of the VBA Project in the dev version so it is different than in the prod version. If you need help changing the name of the VBA Project in an XLA (it's the same for XLAM files), see the following:

    http://www.fontstuff.com/vba/vbatut08.htm


    jmh



    Tuesday, April 24, 2012 3:43 PM
  • I am sorry...I am a total newbie at this yet my work is now expecting of me. So you said all of that to say it simply isn't as easy to copy this source code to a development drives, rename those paths and work? So, since this is called TheShipper (see picture), I need to change to perhaps, ShipperDev?

    I did find/replace "theshipper" and made it "theshipperdev." No changes...same errors.
    Tuesday, April 24, 2012 6:57 PM
  • The names that matter are the project names. For the workbooks shown the project names are PinnShip and TheShipper, respectively.

    I do similar dev and prod versioning with Access databases, and my approach is to keep the project names the same between dev and prod versions, but just make sure that a "client" database (or workbook in your case) only points to one of them.

    Remember, to remove a reference all you have to do is go to Tools > References and uncheck the box next to the item you want to remove...


    jmh

    Tuesday, April 24, 2012 8:15 PM