如何自动添加Simpack COM接口至Excel可用引用列表

Ugh, that repetitive manual reference add is such a hassle—let's get this sorted for you. The core issue here is that the Simpack COM component isn't registered in your Windows registry, so Excel doesn't keep it in the References list permanently. When you browse to the .exe file, Excel registers it temporarily for that session, but it vanishes once you close Excel. Here are three solid solutions, ordered by how reliable they are:

1. Switch to Late Binding (No More References Needed) #

Since you're already using CreateObject("Simpack.Slv"), you don't actually need to add the COM reference at all—this is late binding, which bypasses the static reference requirement. The only tradeoff is losing IntelliSense (auto-completion) for Simpack's methods and properties, but you can work around that by:

• Keeping a separate test module where you use early binding (with the reference added) to get the syntax right
• Then switching back to late binding in your production code (declare Srv as Object instead of the specific Simpack type)

Your code would look like this:

Dim Srv As Object
Set Srv = CreateObject("Simpack.Slv")

This will work every time you open the workbook, no manual steps required—no more digging into the Tools menu.

2. Register the Simpack COM Component System-Wide #

If you prefer early binding (for IntelliSense), you can register the component once so it stays in Excel's References list permanently. Here's how:

• Open Command Prompt as Administrator (right-click > Run as administrator)
• Paste this command (double-check the file path matches your Simpack 2018x install):

  regsvr32 "C:\Program Files\Simpack-2018x\run\bin\win64\_simpack-com-slv.exe"
  

• If you see a "DllRegisterServer succeeded" message, you're good to go. Next time you open Excel, the Simpack library will show up in the Tools > References list without you having to browse for it.

Note: If you get an error, make sure you're using the 64-bit regsvr32 (located in C:\Windows\System32\) since your Simpack is the 64-bit version. Also, confirm the file path is correct—typos here are common!

3. Auto-Add the Reference on Workbook Open #

If the above two options don't fit your workflow, you can write a macro that automatically adds the reference when you open the workbook. Here's the code:

Private Sub Workbook_Open()
    Dim targetRef As Reference
    Dim simpackPath As String
    simpackPath = "C:\Program Files\Simpack-2018x\run\bin\win64\_simpack-com-slv.exe"
    
    ' Check if the reference is already present
    On Error Resume Next
    Set targetRef = ThisWorkbook.VBProject.References.Item("Simpack.Slv")
    On Error GoTo 0
    
    ' Add it if it's missing
    If targetRef Is Nothing Then
        ThisWorkbook.VBProject.References.AddFromFile simpackPath
    End If
End Sub

To make this work, you need to enable a setting in Excel:

1. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings
2. Check "Trust access to the VBA project object model"

Heads up: This setting has security implications, so only enable it if you trust all the workbooks you open.

内容的提问来源于stack exchange，提问作者user8666372