Midrange Programmer

How many times have you wished you could display iSeries data in a Microsoft Excel spreadsheet? Maybe you've already tried by downloading DB2 data into comma- or tab-delimited files and importing them into Excel. But what about results from iSeries queries or program calls? How do you easily get that data into an Excel spreadsheet? What if you could just fill in a box or two, click a button, and--presto!-- the results are displayed in an Excel spreadsheet?

By clicking the appropriate button, this spreadsheet, using techniques I'll discuss later, can open four types of things:

As you're looking at the spreadsheet, note the tabs at the bottom. They can be anything the user wants. I've created five tabs: Daily, Weekly, Monthly, Yearly, and Notes. The tabs help organize the user's work activities. The background color is blue for the Weekly tab, yellow for the Monthly tab, and red for the Yearly tab, so the user can tell at a glance which sheet is open.

As you can see, there's a calendar in the top left-hand position. This is a look-up tool. To find out what, for example, was the date on the first Monday in October last year, you would use the pull-down boxes to choose the month and year. The calendar was put there by clicking Insert, then Object, then Calendar Control.

You can also insert media files, so instructions could be recorded and then played by clicking them.

The top option on the spreadsheet (Queries - Make Sure Dates are in MMDDCCYY order) runs the query whose name is in the Query Name box and whose library is in Query Library box. The date boxes are used by the program CRR001R to update a one-record file (CRRDATP), which has both dates in every format they occur on our system. It and its appropriate fields can be referenced in a query so dates don't need to be changed in the query every time it's run. (See the CL code included with this article.) The next three lines in the spreadsheet call "hard-coded" programs on the AS/400, and each return their output as Excel spreadsheets that open as soon as they're run.

Since this is an Excel spreadsheet, users can enter whatever text they want. For example, they might want to record the last date they ran the query, maybe the grand total from the last time they ran it, to call someone with the results. How many menu systems let users change the menu option text any time they want to?

What's the benefit to the programmer? Other than being a hero and watching people jump up and down once they see it? Well, my application is quicker to write, because there's no display file coding, and there's no output spec or external print file coding. You put the boxes in the Excel spreadsheet to fill in for the parameters (okay, you have to do a little Visual Basic and point-and-click type stuff, but it's easy), and the output gets written to a physical file, then copied to the IFS. So you do have to create the output file.

My application uses a combination of text boxes, command buttons, Visual Basic, Client Access's RMTCMD, batch files, and CL and RPG programming. It is easier than it sounds. The first one is challenging, but after that it's basically copy and modify just a little.

Text boxes contain what will be passed as parameters. When a command button is clicked, the related Visual Basic code is run. This code uses the Shell command to run the appropriate batch file, passing along what's in those text boxes as parameters. The batch file uses RMTCMD to call a CL program on the AS/400 and pass those parameters to it. The CL program runs the query, forcing the output to a file, which is then copied to the IFS as a CSV (comma-separated values) file. Meanwhile, the Visual Basic code has forced Excel to loop until a file named flag.txt (I just put the word "ready" in it) gets copied from the Headers folder to the appropriate folder on the IFS. Flag.txt is copied over as the last step of the CL program. When flag.txt shows up, Excel opens the CSV file, saves it as an Excel spreadsheet, makes the headings bold, sets the columns to the right width, and locks the titles so they won't scroll off the screen. Once you've built one, copying it is really easy.

Note that everything listed below /Rpts is a folder under /Rpts. Also note that you need a separate folder for each of the PCs you're going to run this on. This keeps their data separate from everyone else's, of course.

The /Rpts/Headers folder contains a CSV file that has headings for each of the report types that come from the AS/400. For example, the generic query header file has the text "Query Results from the AS/400, No Field Names Available." Hard-coded reports have a heading file that lists what the report is, the parameters sent to it, and the field descriptions. They may also include field names, depending on the user.

Take a look at the Visual Basic code to run the generic query. Note that the IFS has to be mapped to the I: drive. Change the drive letter in the code if it's mapped to another drive.

We needed two copies of each batch file. The Visual Basic in the control spreadsheet is set to use whichever copy is appropriate for the operating system on that machine. One of each copy of the batch files goes into the /Rpts/BatchXP and /Rpts/BatchNT folders. Every batch file used for Microsoft Windows XP with the RMTCMD in it has to point to C:\Windows\RMTCMD, and the Windows 2000/NT version has C:\Winnt\RMTCMD. The difference is the folder in which RMTCMD gets installed by Client Access.

The //MYAS400 is the name of the AS/400 the program resides on. The rest is just like what you would type on a command line, except that the parameters are %X, because this is a batch file and that's how the parameters passed to the batch file are represented. The number of parameters in the batch file must match the number of parameters the CL program accepts, of course.

The key to this program is line number 70. The CPYTOIMPF command copies the output file from the query to a CSV file in a folder on the IFS. Excel will open a CSV file. This CL copies the output from the query to the folder structure and name defined in &XXTONM3, which is defined to include the &X@DEVD name passed to it from the worksheet. This is how it keeps users' files separated from one another, should they be using them at the same time.

Once you understand these concepts, copying this procedure for hard-coded programs should be pretty easy.

The only line that does anything is the last one. It's just calling whatever is passed to it. Each of the PC document buttons has text boxes to the right that are pretty long and have the path and file name typed into them by the user. The appropriate text box name is the only parameter for each of those buttons.

Batch files use the DOS command interpreter, which doesn't allow long folder or file names, so C:\Program Files, for instance, has to be represented as C:\progra~1, etc. The same is true for what is inside the text boxes to the right of the P.C. file opening buttons.

Using tools you probably already have, this technique can get any data from your AS/400 into Excel at the push of a button. It can do this via queries or simply coded programs. It can make your job easier because of not having to code display files or output specs/print files. It saves your users lots of time and frustration, and allows them to change the text of their menu options by themselves. And, of course, it can open all their favorite spreadsheets, documents, PDF files, and Internet or intranet sites at the click of a button, and helps to organize their work. Last hint: If you create a shortcut on their desktop to their version of the spreadsheet, right click it, left click Properties, and add a shortcut key (by clicking to the right of shortcut then holding down the Ctrl key and pressing one of the function keys); that two-key Ctrl-Function key combination will pop it open whenever they want.

G. Wayne Hawks is a programmer/analyst with 14 years of experience in AS/400 programming. E-mail: donkey_hote@hotmail.com