Begin at the Beginning....

Let's start with a description of the problem. We have a spreadsheet containing product data. Our simple spreadsheet has three columns: the UPC code in column A, the discontinued status in column B, and the next P.O. date in column C. For each row of our spreadsheet, we want to retrieve on-hand quantities and purchasing data from the iSeries to determine if the product is discontinued. The product in the spreadsheet is identified by a UPC code. The data we need from the iSeries is stored in our item master file and our availability file, both of which are keyed on product ID. The iSeries has a table that corresponds product ID to UPC number.

To make our lives easier (and to present you, our readers, with a more complete skill set), we will create a table on the iSeries and populate it with data based on a join query. We'll read each row in our spreadsheet and use the index type library to access the data by key in an OLE DB recordset. With data in hand, we'll update the rows in our spreadsheet.

First, we need to make sure we have all our equipment. Let's open MS Excel and create a new spreadsheet. Press alt+F11 to get to the VBA application environment. Then, add a module by right-clicking in the project explorer and choosing Insert/Module from the pop-up menu.

Open the References dialog by dropping the Tools menu and clicking References. See Figure 1 for details. Make sure you select Microsoft ActiveX Data Objects Library. I have Version 2.5, which comes with the Microsoft Data Access Components (MDAC) download (available free from Microsoft's Web site), and the AS/400 Express Toolkit Table Index Type Library (free with Client Access Express).

Figure 1: The References dialog box

Your versions will vary according to what you have installed. The example depicts V9 of the Office library, V2.5 of MDAC, and V1.2 or better of the Toolkit library. If you have Office 97, you will see V8 of the office library. If you don't see ActiveX installed, download the current version of MDAC. If you have any version of Client Access Express, you should see the AS/400 Index Type Library.

Globally Speaking...

Now, we need to set up some global variables in the general declarations section of the module we just created. Here's the code:

Option Explicit forces you to declare all variables. This is a good habit to get into. VB (and VBA) will create undeclared variables on the fly, but it creates them as variant data types. This can cause you to have type conversion and scoping difficulties that are difficult to ferret out. Use Option Explicit in your modules to help you avoid these types of problems.

These variables are all declared as public variables so that all of our modules and procedures can interact with them. In this example, we could also use the "dim" statement to declare the variables and scope them to this module only. With the declaration in the general declarations section, all the procedures in our module would have access to these variables.

Using these declarations, we have now defined the connection to the iSeries (iSeriesDataCN), the command object we can use to execute commands against that connection (iSeriesDataCM), the connection to iSeries index access (iSeriesDataIX), as well as our indexes and recordsets. Later, we'll create these objects by setting values for our variables so that we can connect, access, and execute commands.

Making It Happen

We'll now create a procedure that the macro processor can call.

This defines a subroutine or procedure in our module. The declaration of constants and variables within the procedure scopes them to that procedure. We'll use these values in processing our spreadsheet. Their use will become clear as we proceed through our example, but take note that the lSourceColumn variable is set to 1 (A, where the UPC code is stored), the lDiscontinuedColumn variable is set to 2 (B, where we want to put the discontinued flag), and the lPODate variable is set to 3 (C, where we'll store the date we retrieve from the iSeries).

Notice that the constant and variable types are declared implicitly. That means we follow the name with the appropriate type symbol, so that we can say "Dim lRow&" instead of "Dim lRow as Long." Neither way is necessarily better than the other. Implicit declarations take fewer keystrokes; explicit declarations are easier to read. Pick the one that suits you best. (For those who don't know, the ampersand (&) represents long integer, the percent sign (%) represents integer, and the dollar sign ($) represents string.)

Having defined connections, we now need to create the objects and establish the connections. The following code does just that.

This code opens our connection to the iSeries, using the IBMDA400 provider, which comes with Client Access Express. The data source refers to the connection defined in Client Access. If you use the iSeries IP address or a system name from your hosts file, the connection will be created for you. The parameters following the connection string provide space for a user name and password. I don't like to use these in code, so I leave them empty. The consequence of that decision is that Client Access will request that the user provide them the first time the connection is opened. Since it will remember them, it will only ask the first time.

We must also open a connection to the iSeries for index access. This will let us get at specific records by key value. Note that the parameters are similar to the ADODB connection parameters.

Let's tell Excel not to update the screen so that our macro runs faster. However, doing so also prevents us from seeing what is going on, so we might not want to include it in our testing. If we turn the screen off, we should use the status bar to tell users what the macro is doing, so they don't get bored and turn off the machine. That would be bad....

As our problem is defined, the data we want exists in two tables on our iSeries. So that we can get the data we want together and use one recordset to take advantage of indexed access, we must create a table to receive the joined data. Using the string we defined previously (sSQL), we'll create and execute an SQL statement that will give us a place to put our iSeries data.

This code creates a physical file called JAVAIL in the USERFILES library. The file is keyed on IPROD. Defining a key is important to the index-based access we want to use.

Having created our data container, we need to populate it with the data we want to use. Here's how we do that:

The SQL statement above creates a group of records that it then inserts into the table called JAVAIL in the USERFILES library. Yes, that's the table we created in the previous step! This data set is a join between the WAVAIL table in the USERFILES library and the IMAST table in the PRODUCTION library. The product IDs (WSKU in WAVAIL and IPROD in IMAST) are used to define the join.

I should explain that the WAVAIL is a table containing summary purchasing information by SKU. The WPXXDT fields contain P.O. dates. No date, no PO. Note also that ONHAND is calculated from values in the IMAST table.

To make it go, we call the execute method of the connection object (iSeriesDataCN) we defined and opened earlier.

iSeries Data, Come On Down!

Now that we've set ourselves up to get the data we need, we can proceed to integrating it to the spreadsheet. Let's start by opening our recordsets and indexes so we can process the data contained therein. We'll use the command object we defined earlier to do that.

We can use either the IFS or SYS naming convention. Since index access requires IFS, we'll use that in all of our command definitions for consistency. Using the set statement, we'll create the command object (iSeriesDataCM) and define its active connection (iSeriesDataCN, which we created and opened earlier) and its text (the command we want to execute). In this case, the command is the fully qualified IFS path of the table we want to open for record-level access, followed by the member name and commitment control level. It can also be an SQL statement, if we so choose. Then, we'll use the command's execute method to execute it, opening the recordset.

Notice that the execute method takes parameters. The parameters are--in order--records affected, parameters, and options. In this case, records affected is empty because we are not doing a batch update, parameters is null because we are not using an SQL statement with a replaceable parameter, and the option says we want to open the table for record level access, please. It's beyond the scope of this article to explain all of this, so if you want more information, check out the references section that follows this article.

Our next step is to open the index over the JAVAIL file so we can get at records by key. This means we create the index object (ixWorkRecord) we defined earlier and connect it to our file. The code to do that follows:

Using the OpenIndex method of the iSeriesDataIX connection we defined earlier, we connect our index object to the JAVAIL table. The OpenIndex method takes two parameters: the file name and the index name. These must point to the same object. Parameter one could be a physical file and parameter two a logical over that file, or both parameters could be logical files. The point is, they have to end up at the same place. Since the physical file we created is keyed, we'll use that as our index.

Having opened our availability workfile, we'll also open the UPC table our system uses to correspond UPCs with SKUs:

But Wait, What About My Spreadsheet?

By now, you're probably wondering when Excel really comes into the picture. Well, now's the time!

We want to process each row in the spreadsheet, using the variables we've previously defined along with our constants. Since spreadsheets can be any length, we need to understand where our data ends. Excel gives us an easy way to do just that, with the SpecialCells method of the cells object:

We set the variable lLastRow (which we previously declared as a long integer) to the value of the row number for the last cell in the spreadsheet, as returned by the SpecialCells method. Now, we can loop through each row in the spreadsheet, getting the UPC code from the source column (defined as lSourceColumn), looking up its SKU, and determining whether it should be discontinued so we can write that value to the spreadsheet.

In VBA, you access the attributes of cells (values, border, shading, number format) through the cells object or the range object. The cells object needs the row and column, which are presented as long integers. For columns, A is 1, B is 2, etc. With the row and column, you can get (and set) the contents of the cell with the Value property, control the formatting with the numberformat property, and set borders with the border property. There are many properties you can retrieve and control; see the VBA Help within Excel for a complete list. You can also act on groups of cells with the range object.

For our example, we'll set the Discontinued column (lTargetColumn) of the spreadsheet to "Yes" if there is no on-hand inventory and no purchasing.

Now, with a simple for/next, we can loop through the spreadsheet:

In the example above, we loop row by row through the spreadsheet, and if the value in the column defined by lSourceColumn is not null, is not blank, and is not zero, we call the AvailableNOW function with the UPC code as the parameter. We've defined that function (as we'll see later on) to return a long integer value, which we will store in the lAvailability variable. Note that before the call to the function, we clear a global variable called sFirstDate. The AvailableNOW function will put the first P.O. date into this variable. If the function returns no on-hand and the sFirstDate variable remains empty, we set the value of the cell in the current row's availability column to "Yes" to indicate a discontinued product. Otherwise, we set it to "No". If we get a first P.O. date, we convert it from string to date value and store it in column 3.

We also set the statusbar text to say something like "Row 227 of 1000" using the values for lRow (the current row) and lLastRow (the last row, which we set earlier). Since the statusbar property requires string values, we must convert the data presented by our current row and last row variables to string using the Str function. Since the Str function returns the value concatenated to a leading blank, we'll use Trim to return the values without leading and trailing blanks.

Before we get to our function definition, let's finish up our main procedure. We should drop the work table JAVAIL we created earlier, close our connections, destroy the connection objects, and turn the screen back on.

We set the index objects and their connection object to Nothing because we want to release the resources. Since these objects don't support a "Close" method, setting them to Nothing accomplishes this task. The recordsets do support the "Close" method.

Executing the SQL "Drop Table" statement has the same effect as a command line "DLTPF USERFILES/JAVAIL."

We don't want to forget to turn the screen back on, and it's a good idea to communicate to the user that the macro has completed.

Functionally Speaking...

Now that our main procedure is defined, we can examine the function "AvailableNow." This is where we use record-level access and indexes to provide fast access to iSeries data so we can make decisions about it. In VBA, as with VB, we define procedures (subroutines) and functions. Functions return a value, and procedures don't, at least not directly. Both can affect any variable within their scope, like the public variables we defined earlier. Both can accept parameters.

When declaring a function, we declare the type of value we want the function to return, along with its parameters. When we declare our AvailableNow function, we declare it as a long integer and tell it to accept a string parameter.

We'll also establish the local variables for our function to use for its various processes.

To access a record by key, we first must set a variant array of the key used to access the UPC file to get the SKU. Keys are placed in the array using the array function, which takes a comma delimited list of arguments and places them in an array.

In the example, we are using one key, so there is one argument. In this context, what we are doing is very similar to the KLIST operation in RPG. You can use all keys over a file or a subset of the keys, just like KLIST. As with KLIST, we use keys in the order
of their appearance in the index.

This type of record access is very quick.

With the key array set, we use AD400's GetBookmark method to return the record pointer of the first record that matches the keylist in the array. Since the UPC code is unique by definition, the first match will also be the only match, so there is no need to loop through the records to match any other condition.

If the seek fails, an error condition occurs and control is handed over
to erHandler, which sets the function's value to zero and returns to the caller, hence the "On Error" statement above. erHandler is detailed below, at the end of the function.

If the seek is successful, it returns the bookmark of the found record. The next instruction moves the UPC file's record pointer to the record found. The Move method's parameters are a number of records and an optional bookmark. Here, we ask the Move method to move the pointer to 0 records from the bookmark returned in the previous instruction, placing the pointer on the desired record. This use of the move method is very similar to RPGs SETLL and SETGT opcodes.

You may recall that we set rsSKURecord to point to the table containing the UPC to SKU correspondence.

The GetBookmark method of the index object also supports the following parameters:

• ad400SeekFirstEQ--first value in the table equal to the specified value
• ad400SeekGE--first value in the table greater than or equal to the specified
• value
• ad400SeekGT--first value in the table greater than the specified value
• ad400SeekLastEQ--last value in the table equal to the specified value
• ad400SeekLE--first value in the table less than or equal to the specified value
• ad400SeekLT--first value in the table less than the specified value

The record returned contains a field called "SKU", which contains the product ID associated with that UPC code.

Now that we have the SKU, we go to the work file JAVAIL we created earlier and get that SKU's on-hand and purchasing data. Same procedure: Set the keylist, get the bookmark, move the record pointer. A match gets dealt with, a not found invokes the error handler.

Now that we have a record, we can find out when the first P.O. is and set the appropriate value for our sFirstDate variable.

Using the above, if the first P.O. date is not zero, then we convert the P.O. date, which is in the form YYYYMMDD, to a date formatted string. Hence, 20020430 becomes "04/30/2002." We use the CStr function to convert the value to string and store it in the sPODateStr variable, and we manipulate the data in that variable. You could combine these two steps into one instruction, eliminating the sPODateStr variable. They are separated here for readability.

We'll also set the value of the function to the on-hand amount. Because inventory might be overissued, we'll check the value for less than or equal to zero, returning zero if true. If the on-hand is a positive number, then we'll return that number.

If we've gotten to this point, we've set our value, so we'll exit the function. If our seek has failed (we didn't get a record), control is passed to our error handler, which sets the function's value to zero and returns to the calling procedure.

Run, Macro, Run!

Now that we've created our macro, we can tell Excel to run it from a hot key. From within Excel, pull down the tools menu and choose the Macros submenu. Then, take the Macros option. You'll see a display that shows the macro name. Click on the Options button, and you'll get a dialog (shown in Figure 2) that allows you to assign a shortcut key and provide a description of the macro.

Figure 2: The Excel Macro Dialog

Note that the macro name is the name we gave our procedure earlier. Procedures are available to the macro processor, and functions are available to procedures.

Why Is This So Involved?

If you've gotten this far, you now know how to incorporate OLE DB calls and index access into a VBA macro within Excel. It is important to develop your ability to work with this technology. I encourage you to set up a test environment and play with the sample code, as this type of activity forms the core for integration.

If you just want to drop a subset of a table into a spreadsheet, the Excel add-in that comes with Client Access can do that for you. If you want to produce analytical documents based on conditions in the data, you'll have to use OLE DB calls in VB or VBA to produce the results you need. Not everything can be done within an SQL query; you might actually want to do something with the results of a query before you start populating a spreadsheet.

It should be noted that, with this technology, you can create macros to look up single pieces of information, like a list price from your item master. You don't have to process the entire worksheet as we did here. Macros can also be tied to a button on your spreadsheet, giving the user ability to execute them with a button click.

The sky is the limit here. You can take this technology virtually anywhere you want to go.

Chris Devous is the Director of IT Systems Development at The Antigua Group, Inc., a Peoria, Arizona, garment manufacturer. Chris has been in IT since '82 and lives in Arizona with his wife, three children, a bird, two dogs, a cat, and various marine life forms. You can email Chris at This email address is being protected from spambots. You need JavaScript enabled to view it..

References

• MSDN Online has an entire category of information on Office integration titled "Office Solutions Development."

• IBM Redbook SG24-5183-00: A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support by Bob Maatta, Mehboob Alam, Geert Maertens, Ray McRoberts, Craig Pelkie, Pankaj Sharma

Also Read

TechTip: Getting Started with Microsoft Visual Studio Express 2012

TechTip: Troubleshooting Team Foundation Server

TechTip: Installing Microsoft SQL Server 2012 Express on Your Local Machine