gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-01 19:04:34 25 4
gpt4 key购买 nike

我可以使用 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?有什么想法吗?

最佳答案

您可以使用 macro recorder生成一个 VBA 代码,该代码将添加到您的 Excel 实例的连接。
我已在此答案的末尾添加了代码,但是如果您按照以下步骤操作,则可以生成自己的代码:
1) 启动宏录制器
2) 在功能区上,单击数据选项卡。单击连接,然后选择添加按钮,如下面的屏幕截图所示
step 2
3) 在下一个屏幕上,选择您现有的数据库连接,然后按照接下来的 2 或 3 个屏幕上的步骤来配置您的连接。
4) 建立连接并出现在连接列表中后,单击属性按钮,然后在下一个屏幕上导出连接文件
4
5) 停止宏录制器并打开 VBE (alt+F11) 并编辑 Module1 中的代码6) 从宏代码中删除这些行

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

7) 立即保存并关闭文件

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


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

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


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

关于excel - 如何使用 VBA 在 Excel 中添加连接(到外部数据源)并将其保存到该 Excel 电子表格的连接列表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17398579/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com