如何使用 VBA 在 Excel 中添加连接（到外部数据源）并将其保存到该 Excel 电子表格的连接列表

我可以使用 VBA 创建新的 ADODB.Connection 以及关联的 ADODB.Command 和 ADOBD.Parameter，然后创建 PivotCache 和数据透视表

Sub CreatePivotTable()
    'Declare variables
    Dim objMyConn As ADODB.Connection
    Dim objMyCmd As ADODB.Command
    Dim objMyParam As ADODB.Parameter
    Dim objMyRecordset As ADODB.Recordset

    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset

    'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=myMIS;Data Source=localhost;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WKSTN101;Use Encryption for Data=False;Tag with column collation when possible=False"
    objMyConn.Open

    'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.CommandText = "select a.col1, a.col2, b.col3, b.col4" & _
                           "from TableA a, TableB b " & _
                           "where a.col3=b.col5 " & _
                           "and a.col1=?"
    objMyCmd.CommandType = adCmdText

    Set objMyParam = objMyCmd.CreateParameter("COLUMN1", adChar, adParamInput, 20, Range("AnotherSheet!A3").Value)

    objMyCmd.Parameters.Append objMyParam

    'Open Recordset'
    Set objMyRecordset.Source = objMyCmd
    objMyRecordset.Open

    'Create a PivotTable cache and report.
    Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    Set objPivotCache.Recordset = objMyRecordset
    objPivotCache.CreatePivotTable TableDestination:=Range("A11"), TableName:="PivotTable1"

    With ActiveSheet.PivotTables("PivotTable1")
        .SmallGrid = False
        With .PivotFields("Col3")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Col4")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Col1")
            .Orientation = xlColumnField
            .Position = 1
        End With
        With .PivotFields("Col2")
            .Orientation = xlDataField
            .Position = 1
        End With
    End With

...但是在我运行此宏后，如果我检查连接列表中的连接属性（在功能区的数据选项卡中），它们将显示为禁用（灰显）并且 SQL 命令不会出现在那里（进一步限制仅通过 VBA 进行更改）。

如何创建这些相同的对象，但将它们与 Excel UI 集成，以便将来的用户不需要使用 VBA？有任何想法吗？

You can use a macro recorder http://spreadsheets.about.com/od/advancedexcel/ss/excel_macro.htm to generate a VBA code that will add a connection to your excel instance.
I have added the code at the end of this answer, however you can generate your own if you follow the below steps:
1) Start a macro recorder
2) On the ribbon, click on Data tab. Click on the Connections and then choose the Add button like shown in the below screenshot

3) On the next screen, choose your existing DB connection, and follow the steps on the next 2 or 3 screens to configure your connection.
4) Once your connection is established and appears in the connections list, click on the Properties button and on the next screen Export Connection File

5) Stop your macro recorder and open VBE (alt+F11) and edit the code in your Module1 6) Remove these lines from your macro code

.ServerFillColor = False
.ServerFontStyle = False
.ServerNumberFormat = False
.ServerTextColor = False

7）立即保存并关闭文件

请注意，当您重新打开文件并运行宏时，连接应添加到您的连接列表中

您现在可以使用此代码从导出的文件添加连接

Workbooks("Book1").Connections.AddFromFile _
        "C:\Users\...\exported_file_name.odc"

或者可以运行录制的代码并让宏为您添加它