The following code is a complete COM automation controller that exports a data table to Microsoft Excel™ and then displays a pie chart of the data. The code is heavily commented, and does not use a single API call, but does rely on the #INCLUDE file that contains the Interface definitions and numeric equates for Excel. This file was generated by the PowerBASIC COM Browser utility, and used without any editing.
This code can be used as the basis of a COM automation controller for your own applications.
Requires Excel 97 or later to be installed.
#COMPILE EXE
#INCLUDE "ExcelApp.inc"
FUNCTION PBMAIN
' Object Variables
DIM oExcelApp AS ExcelApplication
DIM oExcelWorkbook AS ExcelWorkbook
DIM oExcelWorkSheet AS ExcelWorkSheet
DIM oExcelChart AS ExcelChart
' General Object param variables
DIM vBool AS VARIANT
DIM vVnt AS VARIANT
DIM oVnt AS VARIANT
DIM vText AS VARIANT
DIM vRange AS VARIANT
DIM vX AS VARIANT
DIM vY AS VARIANT
' Chart Object param variables
DIM vGallery AS VARIANT
DIM vFormat AS VARIANT
DIM vPlotBy AS VARIANT
DIM vCatLabels AS VARIANT
DIM vSerLabels AS VARIANT
DIM vHasLegend AS VARIANT
DIM vTitle AS VARIANT
' SaveAs Object param variables
DIM vFile AS VARIANT
DIM vFileFmt AS VARIANT
' Open an instance of EXCEL
LET oExcelApp = ExcelApplication IN _
$PROGID_ExcelApplication8
IF ISFALSE ISOBJECT(oExcelApp) THEN _
LET oExcelApp = NEW ExcelApplication IN $PROGID_ExcelApplication8
' Could EXCEL be opened? If not, terminate this app
IF ISFALSE ISOBJECT(oExcelApp) THEN EXIT FUNCTION
' Create a new workbook in EXCEL
OBJECT CALL oExcelApp.WorkBooks.Add TO oVnt
' Copy the Interface reference into an Object Variable
LET oExcelWorkbook = oVnt
' Create a new worksheet in the workbook and use this
' worksheet reference to pump data into EXCEL
OBJECT CALL oExcelWorkBook.WorkSheets.Add TO oVnt
' Copy the Interface reference into an Object Variable
LET oExcelWorkSheet = oVnt
' Format and send data for cells A1:L1
LET vRange = "A1:G1"
' Create a Day of the Week array for A1:G1
DIM a$(1 TO 7)
a$(1) = "Monday" : a$(2) = "Tuesday"
a$(3) = "Wednesday" : a$(4) = "Thursday"
a$(5) = "Friday" : a$(6) = "Saturday"
a$(7) = "Sunday"
LET vText = a$()
OBJECT LET oExcelWorkSheet.Range(vRange).Value = vText
' Center the column title text
LET vVnt = %xlHAlignCenter
OBJECT LET oExcelWorkSheet.Range(vRange).HorizontalAlignment = vVnt
' Set the font attributes and border for this header line
OBJECT LET oExcelWorkSheet.Range(vRange).Font.Bold = vBool
' Color the text Blue
LET vVnt = RGB(0,0,255)
OBJECT LET oExcelWorkSheet.Range(vRange).Font.Color = vVnt
' Set the column width to approx 11 characters
LET vVnt = 11
OBJECT LET oExcelWorkSheet.Range(vRange).ColumnWidth = vVnt
' Enclose the table with a border
LET vRange = "A1:G2"
LET vVnt = %xlMedium ' Medium density border
OBJECT CALL oExcelWorkSheet.Range(vRange).BorderAround(Weight = vVnt)
' Format and send data for cells A2:G2
LET vRange = "A2:G2"
' Create a sales value array for each day of the week
DIM b@(1 TO 7)
RANDOMIZE TIMER
FOR y& = 1 TO 7
b@(y&) = RND(1,100000)
NEXT y&
LET vVnt = b@()
OBJECT LET oExcelWorkSheet.Range(vRange).Value = vVnt
' Format these cells in money (currency) format, ie: "$#.##"
LET vText = "Currency"
OBJECT LET oExcelWorkSheet.Range(vRange).Style = vText
' Now we can make EXCEL visible to the user
LET vBool = 1
OBJECT LET oExcelApp.Visible = vBool
' Prepare the chart
OBJECT GET oExcelApp.Charts.Add TO oVnt
' Copy the Interface reference into an Object Variable
LET oExcelChart = oVnt
' Set the range of cells to use in the table. Here it's 7x2
LET vRange = "A1:G2"
OBJECT GET oExcelWorkSheet.Range(vRange) TO vRange
' Set the Chart parameters
LET vGallery = %xl3DPie
LET vFormat = 7
LET vPlotBy = %xlRows
LET vCatLabels = 1
LET vSerLabels = 1
LET vHasLegend = 2
LET vTitle = "Sales percentages"
' Launch the Chart using named parameters
OBJECT CALL oExcelChart.ChartWizard( _
Source = vRange, _
Gallery = vGallery, _
Format = vFormat, _
PlotBy = vPlotBy, _
CategoryLabels = vCatLabels, _
SeriesLabels = vSerLabels, _
HasLegend = vHasLegend, _
Title = vTitle)
#IF %DEF(%PB_CC32)
PRINT "Press a key to continue the demo"
WAITKEY$
#ELSE
MSGBOX "Click to continue the demo", &H00002000& ' = %MB_TASKMODAL
#ENDIF
' un-Rem the next line for a Print Preview of the table
' Object call oExcelWorksheet.PrintPreview
' un-REM next line to stop "save workbook" prompting
' OBJECT LET oExcelWorkBook.Saved = vBool
' Save the Worksheet to disk - may trigger an "Overwrite?"
' prompt if the file already exists. We could either
' delete the file ahead of time or supply a unique name
LET vFile = "Test.Xls"
OBJECT CALL oExcelWorkSheet.SaveAs(vFile)
' Close the current Worksheet and close EXCEL
OBJECT CALL oExcelApp.ActiveWindow.Close
OBJECT CALL oExcelApp.Quit
' Release the Interfaces. We could omit this since the
' app is about to close, but "best practice" states we
' should clean our house before moving out.
LET oExcelApp = NOTHING
LET oExcelWorkbook = NOTHING
LET oExcelWorkSheet = NOTHING
LET oExcelChart = NOTHING
END FUNCTION
See Also