Products

Automating Excel using VB .Net

This article describes a code download which demonstrates using VB.Net to connect to an AS/400 and produce and Excel spreadsheet. If you do not have Visual Basic, you can download the Express Edition for FREE from Microsoft and use it to talk to your iSeries and automate Excel!

The file available here contains an AS/400 stored procedure, a Visual Basic 2005 Express Edition project and a spreadsheet template. Create a directory called iSeriesTestSpreadsheet on your computer and extract all of the files in this zip to that directory.

The purpose of the project is to create a listing of the logical files and SQL indexes available over a table or physical file on the iSeries. This listing contains some interesting information like the utilization of the logical files versus other logical files, the number of days since the file was accessed, and the selectivity of the keys in the logical. I find it extremely useful in evaluating the effectiveness of an index or logical file.

The program works by showing a windows form which prompts for User ID, Password, 400 Name, Library and physical file name. Once this information is provided, and the user clicks the GO button, the program creates a connection to the iSeries, queries the system catalogue to find all of the logical files associated with the physical file and for each file runs the DSPFD command and redirects the output to a file in QTEMP. Once this is complete, the program issues two SQL statements to populate a DataSet object, starts Microsoft Excel, opens a template and then processes the dataset to create the spreadsheet. Click here to see an example of the spreadsheet output.

Making it work

To use this code, first use SQLThing Enterprise Edition, or Client Access Run SQL Scripts if you must, to create the stored procedure LFINFO. Note that this procedure is contained in the LFINFO Procedure.SQLP file in the download. Note that this procedure takes the library, filename and output filename and takes care of processing the DSPFD information for both the physical file and all of it's associated logical files and SQL indexes. The procedure excludes any multi-format logical files as they can not be used in SQL processing.

Procedure Code

CREATE PROCEDURE SQLBOOK.LFINFO ( IN LIB CHARACTER(10), IN FIL CHARACTER(10), IN FN CHARACTER(10)) LANGUAGE SQL /*BEGIN PROCEDURE CODE*/ BEGIN DECLARE TFIL CHAR ( 10 ) ; DECLARE TLIB CHAR ( 10 ) ; DECLARE YN CHAR ( 1 ) DEFAULT 'Y' ; DECLARE AT_END INT DEFAULT 0 ; DECLARE CMDLEN INTEGER; DECLARE CMDSTR VARCHAR(200); DECLARE CMDSTR2 VARCHAR(200); DECLARE CMDLENA VARCHAR(16); DECLARE C1 CURSOR FOR SELECT DBXFIL , DBXLIB FROM QSYS . QADBXATR INNER JOIN QSYS . QADBLDEP ON ( DBFLDP = DBXLIB AND DBFFDP = DBXFIL ) WHERE DBFFIL = FIL AND DBFLIB = LIB AND DBXATR IN ( 'LF' , 'IX' ) AND DBXREL='Y' ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET AT_END = 1 ; OPEN C1 ; SET CMDSTR = TRIM('DSPFD FILE('||TRIM(LIB)||'/'||TRIM(FIL) ||') TYPE(*MBR) OUTPUT(*OUTFILE) OUTFILE(QTEMP/'|| FN||')'); SET CMDLENA = DIGITS(CAST(LENGTH(CMDSTR) AS DECIMAL(10)))||'.' ||DIGITS(CAST(0 AS DECIMAL(5))); SET CMDSTR2 = 'CALL QSYS.QCMDEXC('''||CMDSTR||''','||CMDLENA||')'; PREPARE S1 FROM CMDSTR2; EXECUTE S1; WHILE AT_END = 0 DO FETCH C1 INTO TFIL , TLIB ; IF AT_END=0 THEN SET CMDSTR = TRIM('DSPFD FILE('||TRIM(TLIB)||'/'||TRIM(TFIL) ||') TYPE(*MBR) OUTPUT(*OUTFILE) OUTFILE(QTEMP/'|| FN||') OUTMBR(*FIRST *ADD)'); SET CMDLENA = DIGITS(CAST(LENGTH(CMDSTR) AS DECIMAL(10)))||'.' ||DIGITS(CAST(0 AS DECIMAL(5))); SET CMDSTR2 = 'CALL QSYS.QCMDEXC('''||CMDSTR||''','||CMDLENA||')'; PREPARE S1 FROM CMDSTR2; EXECUTE S1; END IF; END WHILE ; CLOSE C1; END

Once the procedure is created, and before you attempt to open the Visual Studio Project iSeriesTestSpeadsheet.sln, you need to ensure that you have the Office XP Primary Interop Assemblies installed on your computer. These assemblies allow .Net programs to automate the COM based Excel, Word and PowerPoint programs. In my project, I am referencing the Excel program, so before you open the project you need these assemblies installed or you will receive an error. I use the Office XP assemblies because it makes my program compatible with Office 2000, XP and 2003. If you use the Office 2003 assemblies, you are creating programs that will not work with previous versions of office.

Now, use Visual Studio to open the iSeriesTestSpreadsheet.sln file. Once the project is open, you can look at the forms and code provided to see how to project works. Pressing F5 in the Visual Studio environment will run the project in Debug mode. Fill in the appropriate fields and press the GO! button to cause the spreadsheet to do it's stuff. If you have any questions, please do not hesitate to email me.

Howard.