如何使用 VBA 在 Excel 中添加连接（到外部数据源）并将其保存到该 Excel 电子表格的连接列表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17398579/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use VBA to add a Connection (to an External Data Source) in Excel and Save it to that Excel spreadsheet's list of Connections
提问by Sam
I can use VBA to create a new ADODB.Connection and associated ADODB.Command and ADOBD.Parameter and then create a PivotCache and a PivotTable
我可以使用 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
... BUT after I have run this macro, if I check the Connection properties in the Connections list (in the Data tab of the Ribbon) they appear disabled (grayed-out) and the SQL Command doesn't appear there (limiting further changes through VBA only).
...但是在我运行这个宏之后，如果我检查连接列表中的连接属性（在功能区的数据选项卡中），它们会显示为禁用（灰显）并且 SQL 命令不会出现在那里（进一步限制仅通过 VBA 更改）。
How can I create these same objects but have them integrate with the Excel UI so future users don't need to use VBA? Any ideas?
如何创建这些相同的对象，但让它们与 Excel UI 集成，以便将来的用户不需要使用 VBA？有任何想法吗？
采纳答案by Sam
You can use a macro recorderto 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 Datatab. Click on the Connectionsand then choose the Addbutton 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 Propertiesbutton and on the next screen Export Connection File
5) Stop your macro recorder and open VBE(alt+F11)and edit the code in your Module16) Remove these lines from your macro code
您可以使用宏记录器生成 VBA 代码，该代码将添加到您的 excel 实例的连接。
我在此答案的末尾添加了代码，但是如果您按照以下步骤操作，您可以生成自己的代码：
1) 启动宏记录器
2) 在功能区上，单击Data选项卡。单击Connections，然后选择Add如下图所示的按钮
3) 在下一个屏幕上，选择您现有的数据库连接，然后按照接下来的 2 或 3 个屏幕上的步骤配置您的连接。
4) 一旦您的连接建立并出现在连接列表中，单击Properties按钮，然后在下一个屏幕上
5) 停止您的宏记录器并打开并编辑您的代码Export Connection FileVBE(alt+F11)Module16）从宏代码中删除这些行
.ServerFillColor = False
.ServerFontStyle = False
.ServerNumberFormat = False
.ServerTextColor = False
7) Save and close the file now
7) 现在保存并关闭文件
Note when you reopen the file and run the macro the connection should be added to your connections list
请注意，当您重新打开文件并运行宏时，应将连接添加到您的连接列表中
you can now add the connection from the exported file with this code
您现在可以使用此代码从导出的文件中添加连接
Workbooks("Book1").Connections.AddFromFile _
"C:\Users\...\exported_file_name.odc"
or can run the recorded code and let the macro add it for you
或者可以运行录制的代码并让宏为您添加它
