vba / IBM ISeries AS400 relations

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.

Please Login or Register  to view this content.

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.

Have you tried power query or power pivot?