[VBA] Excel: recuperare dati da AS/400

[Aki]

ave populus,
ho bisogno di recuperare dei dati da un db di AS400 in Excel, ho utilizzato i seguenti 2 metodi il primo con il driver ODBC iSeries Access ed il secondo via DSN:

Sub estrai()
    Dim a As Range
    Set a = ActiveSheet.Range("A4")
    Dim b As Range
    Set b = ActiveSheet.Range("B4")
    Dim c As Range
    Set c = ActiveSheet.Range("C4")
    Dim d As Range
    Set d = ActiveSheet.Range("D4")
    Dim e As Range
    Set e = ActiveSheet.Range("E4")
    Foglio1.Range("F4:IV65536").Clear
            With ActiveSheet.QueryTables.Add(Connection:= _
                "ODBC;Driver=iSeries Access ODBC Driver;
 Server=192.168.xxx.xxx; Uid=xxx; 
Pwd=xxx; System=xxx;" _
                , Destination:=Range("IT4"))
                .CommandText = Array( _
                "select CPCO,CUMV, QGF2RXG from MERSY.rxGIGI0f where cart='" & CInt(a) & "' and carv='"
 & CInt(b) & "' AND TAAD='"
 & CInt(c) & "' AND TMMI='"
 & CInt(d) & "' AND TGGM='" & CInt(e) & "'" _
                )
                .Name = "Ricerca..."
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = True
                .SaveData = True
                .AdjustColumnWidth = False
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh BackgroundQuery = False
            End With
End Sub

Sub estrai()
    Dim a As Range
    Set a = ActiveSheet.Range("A4")
    Dim b As Range
    Set b = ActiveSheet.Range("B4")
    Dim c As Range
    Set c = ActiveSheet.Range("C4")
    Dim d As Range
    Set d = ActiveSheet.Range("D4")
    Dim e As Range
    Set e = ActiveSheet.Range("E4")
    Foglio1.Range("F4:IV65536").Clear
            With ActiveSheet.QueryTables.Add(Connection:= _
                "ODBC;Dsn=AS400;" _
                , Destination:=Range("IT4"))
                .CommandText = Array( _
                "select CPCO,CUMV, QGF2RXG from MERSY.rxGIGI0f where cart='" & CInt(a) & "' and carv='"
 & CInt(b) & "' AND TAAD='"
 & CInt(c) & "' AND TMMI='"
 & CInt(d) & "' AND TGGM='" & CInt(e) & "'" _
                )
                .Name = "Ricerca..."
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = True
                .SaveData = True
                .AdjustColumnWidth = False
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh BackgroundQuery = False
            End With
End Sub

ebbene, entrambi danno

Errore 1004, errore generale di ODBC

non sò proprio come fare?

P.S. lo stesso codice con una connessione a SqlServer o con una fonte dati a PostgreSQL funziona

[Aki]

up

[Aki]

up