When the Macro is invoked, it will do the following things:
It will create a sheet for the CUST file’s single valued fields, name the sheet “CUST”, and load the single valued data into it.
It will create a sheet for the CUST file’s MultiValued fields that belong to the MultiValued AccountTypes group, name the sheet “CUSTAccountTypes” and load the data for the AccountTypes multivalued group into it. The first two columns are the item-id of the CUST file item that “owns” the MultiValued row and the MultiValue number (1-based offset within the owning item).
It will create a sheet for the CUST file’s SubValued fields that belong to the SubValued Accounts group which is nested within the MultiValued AccountTypes group, name the sheet “CUSTAccountTypesAccounts” and load the data for the Accounts subvalued group into it. The first three columns are the item-id of the CUST file item that “owns” the MultiValued row, the MultiValue number (1-based offset within the owning item) and the SubValue number (1-based offset within the owning MultiValue set).
In our example, we use the CUST table, skipping one item which we provide as an example of non-conforming data. The FusionWare Direct command for this is:
CUST 'SELECT CUST NE "BAD.3"'
You can also use the FUSIONWARE.DICTCALL ITEMPROC subroutine to generate the equivalent of a TCL LIST/SORT commands output, without having to do any mapping. The equivalent command is:
EXEC ITEMPROC CUST "SSELECT CUST NE ""BAD.3"" BY AGE" FUSIONWARE.DICTCALL "CUST.NAME,,,0" "AGE,INTEGER,,0" "BIRTHDATE,DATE,,0" "NET.WORTH,DECIMAL,2,0" "BIRTHTIME,TIME,,0" "ACCOUNT.TYPE,,,1,AccountTypes" "ACCOUNT.TYPE.NAME,,,1,AccountTypes" "ACCOUNT.NUMBER,,,2,AccountTypes,Accounts" "ACCOUNT.BALANCE,DECIMAL,2,2,AccountTypes,Accounts"
Which is the equivalent of this TCL SORT command:
SORT CUST NE "BAD.3" BY AGE CUST.NAME AGE BIRTHDATE NET.WORTH BIRTHTIME ACCOUNT.TYPE ACCOUNT.TYPE.NAME ACCOUNT.NUMBER ACCOUNT.BALANCE
Create a new Excel workbook, and immediately click the office button (top right, round button), and select “Save As”, “Excel Macro-Enabled Workbook”. Give it a name and save it.
Click on the Developer tab in the menu bar. On the far left, click on the Visual Basic button.
Right click on “ThisWorkbook” in the left pane and select View Code.
Now paste the code from the following section into the main pane.
' Copyright (C) 2010, FusionWare Integration Corp.
' Macro to process data from FusionWare Direct Data source
' Modify this value to use the OLE DB connection string that will connect to your MultiValue system
Const CONNECTIONSTRING = "Provider=GAXOLEDB; Data Source=van-web2;User Id=GAXOLEDB;Location=GAXOLEDB;"
' Modify this value to be the command you want to process
Const COMMANDTEXT = "CUST 'SELECT CUST NE ""BAD.3""'"
Public Connection As Object
' Sample code
Sub LoadCUSTData()
Dim rs
Set rs = GetResultSet(CONNECTIONSTRING, COMMANDTEXT)
ThisWorkbook.Sheets.Add
ThisWorkbook.ActiveSheet.Name = "CUST"
Call ProcessSingleValues(rs, ThisWorkbook.ActiveSheet)
Set rs = GetResultSet(CONNECTIONSTRING, COMMANDTEXT)
ThisWorkbook.Sheets.Add
ThisWorkbook.ActiveSheet.Name = "CUSTAccountTypes"
Call processMultivalues(rs, ThisWorkbook.ActiveSheet, "AccountTypes")
Set rs = GetResultSet(CONNECTIONSTRING, COMMANDTEXT)
ThisWorkbook.Sheets.Add
ThisWorkbook.ActiveSheet.Name = "CUSTAccountTypesAccounts"
Call processSubvalues(rs, ThisWorkbook.ActiveSheet, "AccountTypes", "Accounts")
' There is one more MV group and one more SV group, but you can see the point
' If you want the other groups, you'll have to create new sheets in your
' workbook as it defaults to having only 3.
' Be a good citizen and clean up after yourself!
If Connection.State = 1 Then
Connection.Close
End If
End Sub
' These helper methods should not require serious changes
Function GetResultSet(connStr, cmdText)
If Connection Is Nothing Then
Set Connection = CreateObject("ADODB.Connection")
End If
If Connection.State = 0 Then
Connection.Open connStr
End If
Set GetResultSet = CreateObject("ADODB.Recordset")
Set GetResultSet = Connection.Execute(cmdText)
End Function
Sub ProcessSingleValues(ByRef rs, ByRef objSheet)
Dim row
Dim column
Dim objValue
' Process single valued fields into Sheet1
objSheet.Activate
row = 1
' Get column names into first row
For column = 1 To rs.Fields.Count
If CInt(rs(column - 1).Type) <> 136 Then
objSheet.Cells(row, column) = rs(column - 1).Name
End If
Next
row = 2
While Not rs.EOF
For column = 1 To rs.Fields.Count
If CInt(rs(column - 1).Type) <> 136 Then
If CInt(rs(column - 1).Type) = 134 Then
objSheet.Cells(row, column) = Format(rs(column - 1), "hh:mm:ss")
ElseIf CInt(rs(column - 1).Type) = 134 Then
objSheet.Cells(row, column) = Format(rs(column - 1), "dd-mmm-yyyy")
Else
objSheet.Cells(row, column) = CStr(rs(column - 1))
End If
End If
Next
rs.MoveNext
row = row + 1
Wend
rs.Close
End Sub
Sub processMultivalues(ByRef rs, ByRef objSheet, MVGroupName)
Dim row
Dim column
Dim itemid
Dim rsMV
Dim mvc
objSheet.Activate
row = 1
' Start with itemid field
objSheet.Cells(row, 1) = rs(0).Name
objSheet.Cells(row, 2) = "MVCount"
While Not rs.EOF
mvc = 1
itemid = CStr(rs(0))
Set rsMV = rs(MVGroupName).Value
While Not rsMV.EOF
If row = 1 Then
' Get column names into first row
For column = 1 To rsMV.Fields.Count
If CInt(rsMV(column - 1).Type) <> 136 Then
objSheet.Cells(row, column + 2) = rsMV(column - 1).Name
End If
Next
row = 2
End If
objSheet.Cells(row, 1) = itemid
objSheet.Cells(row, 2) = mvc
For column = 1 To rsMV.Fields.Count
If CInt(rsMV(column - 1).Type) <> 136 Then
If CInt(rsMV(column - 1).Type) = 134 Then
objSheet.Cells(row, column + 2) = Format(rsMV(column - 1), "hh:mm:ss")
ElseIf CInt(rsMV(column - 1).Type) = 134 Then
objSheet.Cells(row, column + 2) = Format(rsMV(column - 1), "dd-mmm-yyyy")
Else
objSheet.Cells(row, column + 2) = CStr(rsMV(column - 1))
End If
End If
Next
rsMV.MoveNext
row = row + 1
mvc = mvc + 1
Wend
rsMV.Close
rs.MoveNext
Wend
rs.Close
End Sub
Sub processSubvalues(ByRef rs, ByRef objSheet, MVGroupName, SVGroupName)
Dim row
Dim column
Dim itemid
Dim rsMV
Dim mvc
Dim rsSV
Dim svc
objSheet.Activate
row = 1
' Start with itemid field
objSheet.Cells(row, 1) = rs(0).Name
objSheet.Cells(row, 2) = "MVCount"
objSheet.Cells(row, 3) = "SVCount"
While Not rs.EOF
mvc = 1
itemid = CStr(rs(0))
Set rsMV = rs(MVGroupName).Value
While Not rsMV.EOF
svc = 1
Set rsSV = rsMV(SVGroupName).Value
While Not rsSV.EOF
If row = 1 Then
' Get column names into first row
For column = 1 To rsSV.Fields.Count
If CInt(rsSV(column - 1).Type) <> 136 Then
objSheet.Cells(row, column + 3) = rsSV(column - 1).Name
End If
Next
row = 2
End If
objSheet.Cells(row, 1) = itemid
objSheet.Cells(row, 2) = mvc
objSheet.Cells(row, 3) = svc
For column = 1 To rsSV.Fields.Count
If CInt(rsSV(column - 1).Type) <> 136 Then
If CInt(rsSV(column - 1).Type) = 134 Then
objSheet.Cells(row, column + 3) = Format(rsSV(column - 1), "hh:mm:ss")
ElseIf CInt(rsSV(column - 1).Type) = 134 Then
objSheet.Cells(row, column + 3) = Format(rsSV(column - 1), "dd-mmm-yyyy")
Else
objSheet.Cells(row, column + 3) = CStr(rsSV(column - 1))
End If
End If
Next
rsSV.MoveNext
row = row + 1
svc = svc + 1
Wend
rsMV.MoveNext
mvc = mvc + 1
Wend
rsMV.Close
rs.MoveNext
Wend
rs.Close
End Sub
You will need to change the constant value CONNECTIONSTRING to connect to your system. You may wish to leave the COMMANDTEXT constant alone until you have tested. You will eventually change this to point to your own files or data.
You can run the code in Debug Mode, or create a button on a spreadsheet and call it from there.
The macro code consists of a number of components that can be customized.
If you look at the helper methods for retrieving the single valued, multivalued and subvalued fields, you will see that I do some special formatting for the date and type fields. You can add custome formatting for any other fields types (decimal, for instance) that we return.
In the LoadCUSTData sub, we create the sheets and name them to match the data we are returning. You can change how this works.
If you change the command and the data that you are retrieving, you will need to use different multivalue group names and subvalue group names when calling the helper functions.
The FusionWare Direct product family includes client providers that enable access to your MultiValue data from Java, .NET and Legacy COM/COM+ environments.