Run AS400 Query in Excel

[hartzel...@gmail.com]

Hello,
I've been writing queries for employees and am looking for an easier way for them to run them without have to actually access the AS400 query function.

I'm sure there is a way to run them from Excel, but I am of limited coding/programming knowledge (just started learning VBA on my own). I'm in more of training/troubleshooting position and am teaching myself the IT side as I go to help out others.


Any easy step by step on how to build the Macro, etc, etc. would be appreciated.

Thanks

[A. Perry Nelson]

load the iaccess, odbc, with the excel/lotus software; which should load the plug in for excel, and also microsoft query, for reading the i5 databases and from with in excel/lotus. But one thing, will cause stress, is the database, library, field names, and what types, unless you have a databook for those power users. P

[Danilo Cussini]

SQL is more powerfull Query/400, so I suggest you to create a view that can be easly readed, filtered and imported in Excel via ODBC and Microsoft query.

[A. Perry Nelson]

On Tuesday, February 24, 2015 at 3:14:35 PM UTC-5, hartzel...@gmail.com wrote:

Excel/Lotus both have sql built into the software. you have to load the obdc plug in, so the power user can create sql on the fly. P

[Steve Richter]

On Tuesday, February 24, 2015 at 3:14:35 PM UTC-5, hartzel...@gmail.com wrote:

you need client access installed. And the use ODBC administration to config an ODBC connection to the 400.

in Excel use the data ribbon, from other sources, from data connection wizard. In the wizard, select ODBC DSN as the data source. Click next. On this next screen you should see your as400 ODBC data source listed. Click the as400 data source, click next and you get a list of tables and views in the current library.

Go thru that and you should see how an entire table or view can be read from the as400 into excel.

More can be done with this, including calling stored procedures on the 400 that return a result set to excel.

-Steve