philpl1jb Jan 22, 2009   3:13 PM GMT

Hi Nadeeshani You're getting a Windows DOS error -- you haven't gotten to the 400. I'm not sure how much help you'll get from the AS/400 group. Have you got the AS/400 client access ODBC stuff installed on your PC? And a data source defined -- mine appears to be defined as Excel Files -- so perhaps -- you put Excel in where you needed Excel Files ??? Phil

54,090 pointsBadges:

Pcatlin Jan 23, 2009   3:03 PM GMT

Nadeeshani... Install the data transfer pieces from the client access install, also install the excel interface pieces. Once you do that, you will be able to go into excel and under the "DATA" menu, you can "Import External Data" then select "New Database Query". That will open a wizard that will prompt you for your Database. If this is your first time, you can select "New Data Source". This prompts you to setup an ODBC connection to the iseries using the data transfer pieces you installed above. Once that ODBC connection is in place you select the table you want to download into your spreadsheet, and which cell you want the table to start in. The import will bring the field names in the first row and the data in the following rows of the spreadsheet. You can save the query as a part of the wizard as well so you only have to refer to it in future spreadsheets. As for your macro, you can then just use the "refresh data" option on the "data" menu, or bring that button out to your tool bar. Good Luck! Phil

200 pointsBadges:

philpl1jb Jan 23, 2009   3:34 PM GMT

Good answer from the other Phil Doesn't anyone who will use this sheet need to set-up the data source? Phil

54,090 pointsBadges:

Pcatlin Jan 26, 2009   2:52 PM GMT

Phil is right... Users that need to refresh the data will need to setup the data source.. Another option: You can save the sheet with the data. Future users can use the iSeries data, but without setting up the data source they would not be able to refresh it. Not setting up the data source lets you have some control over the generation of the sheet, depending on your needs. We have a document that is updated weekly. Most users cannot refresh the data because we want all of them working off the same starting point, but the data in the iSeries file is dynamic, so if we let everyone refresh, all the users would get different results. Something else I discovered... You can set the query properties to refresh automatically every time the worksheet is opened. This eliminates the need for either a macro or having to push the ! button. --the other Phil--

200 pointsBadges: