3: Displaying the results of a query in Microsoft Excel
This mrScriptBasic example illustrates:
▪Using ADO to access case data and perform a query on it.
▪Exporting the results of the query to Excel.
Requirements
Microsoft Excel
Code
This example is included with the UNICOM Intelligence Developer Documentation Library as a sample script called
MSExcelQuery.mrs. See
Sample mrScriptBasic files for more information.
Dim strError, i, strName
Dim ConnectionString, sqlQuery, adoConnection, adoRS, adoFields, adoField
Dim xlApp, xlWorkbook, xlWorksheet, xlStartRow
Dim xlStartColumn, xlCol, xlRow
ConnectionString = "Provider=mrOleDB.Provider.2; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.ddf; _
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.mdd; _
Mode=3; _
MR Init Category Names=1"
Set adoConnection = CreateObject("ADODB.Connection") ' Line 12
adoConnection.Open(ConnectionString)
sqlQuery = "SELECT serial, age, gender FROM vdata WHERE serial < 10" ' Line 14
Set xlApp = CreateObject("Excel.Application") ' Line 16
xlApp.Visible = true
Set xlWorkbook = xlApp.Workbooks.Add()
Set xlWorksheet = xlWorkbook.Worksheets[1]
Set adoRS = adoConnection.Execute(sqlQuery) ' Line 21
xlStartRow = 1
xlStartColumn = 1
xlCol = xlStartColumn
xlRow = xlStartRow
Dim xlCell
For i = 0 to (adoRS.Fields.Count - 1) ' Line 29
Set xlCell = xlWorksheet.Cells[xlRow][xlCol]
xlCell.FormulaR1C1 = adoRS.Fields[i].Name
xlCol = xlCol + 1
Next
If Not adoRS.EOF Then
xlRow = xlStartRow + 1
Do Until adoRS.EOF ' Line 37
xlCol = xlStartColumn
For i = 0 to (adoRS.Fields.Count - 1)
xlWorksheet.Cells[xlRow][xlCol].FormulaR1C1 = adoRS[adoRS.Fields[i].Name]
xlCol = xlCol + 1
Next
xlRow = xlRow + 1
adoRS.MoveNext()
Loop
End If
adoConnection.Close()
Line 6
Sets up a string containing the
Connection properties for connecting to the UNICOM Intelligence Data File (.
ddf) version of the Museum sample that is installed with the UNICOM Intelligence Developer Documentation Library. The metadata file is called
museum.mdd and by default it is installed in the [INSTALL_FOLDER]\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\ folder. If the sample files are stored in a different location on your computer, edit the example scripts accordingly before running them. Alternatively, you could amend the example to display the Data Link Properties dialog box. See
CreateObject for more information.
Line 12
Uses the
CreateObject function to create an ADO
Connection object, which is then used to open a connection to the data source and to return a recordset by executing a query against VDATA.
Line 16
Uses the
CreateObject function again to create an Excel application object, which is then used to create a workbook.
Line 29
Uses a
For...Next statement to iterate through the fields in the recordset, and writes the field names in the first row of the worksheet as column headings.
Line 37
Uses a
Do...Loop statement to iterate through the recordset writing the contents of the fields to the cells in the worksheet.
Notes
The mrScriptBasic example above uses a very simple SQL query. However, you can use any SQL query that is supported by the UNICOM Intelligence Data Model. For example, you could create a simple crosstabulation by replacing line 14 with the following:
sqlQuery = "SELECT groupby.col[0] AS Gender, SUM(visits) AS 'Sum of visits' FROM vdata WHERE visits > 0 GROUP BY gender ON gender.DefinedCategories()"
For details of the SQL syntax that is supported by the UNICOM Intelligence Data Model, see
SQL syntax.
Result
Here is the crosstabulation in Excel:
See also
