I have a small project I urgently need completed tonight. It should take an experienced VB programmer a couple hours. It's an Excel spreadsheet with a VB coded macro. ODBC connection. The data is just one recordset about 35000 rows with 11 columns. The values in the first 3 columns represent a unique key. There are about 65 rows for each unique key. Some values in the first and/or the second columns are null.
Each group of 65 rows in the base recordset will become one row in the Excel spreadsheet output with 76 columns. Each row in the base recordset has a column named "attribute" the distinct values in those columns will become the column names for the 65 new columns.
Each of the 65 rows in the base recordset has a column named "value" with numeric values and a column named "alpha" with text values.
The values in these columns will go into the new columns in the Excel output recordset with a column name that match the value in "attributes" column. (If a null or 0 value are in the "value" column then use the value in the alpha column.)
Simplified example:
base recordset from ODBC connection
column1 column2 column3 attribute value alpha
1002 10 23 size 2
1002 10 23 color blue
1002 10 23 weight 25
12 19 size 4
12 19 color white
12 19 height 12
Excel Spreadsheet Output recordset
column1 column2 column3 size color weight height
1002 10 23 2 blue 25
12 19 4 white 12
We already have an Office'97 Excel spreadsheet that will do this. The problem is that the data in the base recordset has changed. The original recordset had only 2500 rows and resulted in 28 rows in the Excel spreadsheet; the values in the first two rows made a unique key, so a new row has been added; and none of the values in the first two rows in the original base recordset were null, so trapping has to be added for null values.
The other problem that all the processing for all rows in the base recordset took place before any rows are written to the Excel spreadsheet. With the larger volume of rows in the new base recordset Excel freezes as it uses all the system resources. Our client's envoronment doesn't support stored procedures, so we can't do the processing at the database level.
We want to upgrade the Excel spreadsheet to Office'2003 (the original VB code was done in VB5 so we need to upgrade to VB6. The original connection was ADO and we want to update to and ODBC connection method.
The client's database is DB2 on an AS400. We created a view and I've exported the data into an Access database for development offsite. They use an ODBC datasource. I can provide the original Excel file, the Access DB with the actual data to be used as the base recordset.