Kurt Marshman
Kurt Marshman

Reputation: 215

Build Refreshable Query From Multiple Connections AS400 IBMi

My attempt was to create a simple PivotTable on the fly from multiple tables from an AS400 IBMi. That was achieved, but the PivotTable is not "refreshable".

So I started looking at posts about programmatically creating connections and came up with the example below that is refreshable, but to only one table:

    ActiveWorkbook.Connections.AddFromFile "N:\apps\excel\connections\PRD 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:\apps\excel\connections\PRD 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

Is it possible to use two connections, combine them, and create the final result that is refreshable?

The second connection would be:

      ActiveWorkbook.Connections.AddFromFile "N:\apps\excel\connections\PRD 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:\apps\excel\connections\PRD PM.odc"
    .SourceDataFile = ""
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("PRD PM")
    .Name = "PRD PM"
End With 

Currently working code:

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

Upvotes: 3

Views: 352

Answers (1)

Kurt Marshman
Kurt Marshman

Reputation: 215

Well, I figured this out. Instead of adding connections that already existed, I added a new one and defined it.

"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

Upvotes: 0

Related Questions