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

我可以使用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

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

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