Hello everyone.
I'm going on a longshot here but maybe someone has experience with this.
In AS400 I have a query that creates a table full of information. (I enter a product code and all information regarding that product is stored in that table).
I managed somehow to find a vba code that will import that information into excel.
Sub Macro1()

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=database;DBQ=library;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;" _
        ), Array("QRYSTGLMT=-1;")), Destination:=ActiveCell).QueryTable
        '.CommandType = 0
        .CommandText = Array("SELECT * FROM database.library.table table")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        '.ListObject.DisplayName = "Table"
        .Refresh BackgroundQuery:=False
    End With
    
End Sub
My problem is the following. I have to get information about thousands of products. This means I have to run 1 query in AS400 then run the macro in excel then again and again and again.
I would like to avoid using AS400 directly. Lets say I have a list and 1 by one it takes a product code > creates the table > imports the table in excel > takes next product code > creates table > imports table and so on.

I'm hope I'm not to vague. Any idea/help/suggestion is appreciated.

Thanks.