从多个连接 AS400 IBMi 构建可刷新的查询

Question

我的尝试是从 AS400 IBMi 的多个表中动态创建一个简单的数据透视表。这已实现，但数据透视表不是"可刷新的"。

因此，我开始查看有关以编程方式创建连接的帖子，并提出了以下示例，该示例可刷新，但只能刷新到一个表：

    ActiveWorkbook.Connections.AddFromFile "N:appsexcelconnectionsPRD IS.odc"
With ActiveWorkbook.Connections("PRD IS").ODBCConnection
    .BackgroundQuery = True
    .CommandText = Array("SELECT * FROM ""PRD"".""Y2K"".""IS""")
    .CommandType = xlCmdSql
    .Connection = "ODBC;DSN=s11111111;"
    .RefreshOnFileOpen = False
    .SavePassword = False
    .SourceConnectionFile = "N:appsexcelconnectionsPRD IS.odc"
    .SourceDataFile = ""
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("PRD IS")
    .Name = "PRD IS"
End With
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
    ActiveWorkbook.Connections("PRD IS"), Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:=ActiveCell, TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14

是否可以使用两个连接，将它们组合在一起，并创建可刷新的最终结果？

第二个连接是：

      ActiveWorkbook.Connections.AddFromFile "N:appsexcelconnectionsPRD PM.odc"
With ActiveWorkbook.Connections("PRD PM").ODBCConnection
    .BackgroundQuery = True
    .CommandText = Array("SELECT * FROM ""PRD"".""Y2K"".""PM""")
    .CommandType = xlCmdSql
    .Connection = "ODBC;DSN=s111111111;"
    .RefreshOnFileOpen = False
    .SavePassword = False
    .SourceConnectionFile = "N:appsexcelconnectionsPRD PM.odc"
    .SourceDataFile = ""
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("PRD PM")
    .Name = "PRD PM"
End With 

当前工作代码：

Sub CreatePivotTable()
'Declare variables
Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Param As ADODB.Parameter
Dim rs As ADODB.Recordset
Set Conn = New ADODB.Connection
Set Cmd = New ADODB.Command
Set rs = New ADODB.Recordset
'Open Connection'
Conn.ConnectionString = "DSN=s11111111;"
Conn.Open
'Set and Excecute SQL Command'
Set Cmd.ActiveConnection = Conn
Cmd.CommandText = "SELECT ISWH as WH,ISPART as Part,PMDESC as Description,ISCF01 As AC, PMPCLS As PC, PMPLIN As PL" & _
" FROM Y2K.IS LEFT JOIN Y2K.PM ON Y2K.IS.ISPART = Y2K.PM.PMPART" & _
" WHERE(ISWH) in ('XX')" & _
" AND (ISCF01) not in ('B','D')" & _
" AND (PMPLIN) in ('YY')" & _
" AND (PMPCLS) like ('Z%')"
Cmd.CommandType = adCmdText
'Open Recordset'
Set rs.Source = Cmd
rs.Open
'Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objPivotCache.Recordset = rs
objPivotCache.CreatePivotTable TableDestination:=ActiveCell, TableName:="PivotTable1"
With ActiveSheet.PivotTables("PivotTable1")
    .SmallGrid = False
    With .PivotFields("WH")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Part")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("PL")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("PC")
        .Orientation = xlDataField
        .Position = 1
    End With
 End With
End Sub

Answer 1

嗯，我想通了。 我没有添加已经存在的连接，而是添加了一个新的连接并定义了它。

"Test" is the name of the connection
"x" is the description
"Conn" is the connection string
"Cmdarray" is the sql
Sub CreatePivotTable()
Dim Cmdarray
Dim Conn
Cmdarray = Array("SELECT ISWH as WH, ISPART as Part,PMDESC as Description, ISCF01 as AC FROM ""PRD"".""Y2K"".""IS"" LEFT JOIN ""PRD"".""Y2K"".""PM"" ON ""PRD"".""Y2K"".""IS"".ISPART = ""PRD"".""Y2K"".""PM"".PMPART WHERE (ISWH) IN ('XX')")
Conn = "ODBC;DSN=s111111111;"
ActiveWorkbook.Connections.Add "Test", "x", Conn, Cmdarray
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
    ActiveWorkbook.Connections("Test"), Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:=ActiveCell, TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
End Sub