Context: |
General |
Library/Class: |
Sample Code |
Changing the order of Excel Workbook sheets As Integer, Read/Write
When Integra for Notes exports data to a Microsoft Excel spreadsheet, it allways pushes the data into the first sheet within the Excel template attached to the Integra profile. A need may arise to export data for further processing, for instance, the creation of table of totals and in addition a couple of charts to present the raw data in a more meaningful manner. This has to be done in the second sheet as the first sheet is reserved for Integra's data export. You can actually prepare the second sheet in the Excel template, so that the second sheet containing these tables & charts is automatically recalculated when the Excel spreadsheet is activated by Integra. This to avoid doing it on the fly everytime the report is created.
There is though one problem that remains: when Integra is activating the Excel application, it allways displays the first sheet with the raw data. You may want to show the second sheet first, as the user might not need to be bothered with the raw data. With the current version of Integra the Excel application is allways activated with the first sheet within the workbook, as this is the one used to push the raw data into. Therefore we need to change the property of the Integra stats runtime object that contains the Excel spreadsheet object to have the second sheet assigned rather then the first one. We have to do this in the CB_BFORCMACTION event:
Const CB_INITIALISE = 3
Const CB_ACTIONONLY = 16
Const CB_BFOREXPORT = 10
Const CB_INVALID = 12
Const CB_CATEGORY = 13
Const CB_TOTAL = 14
Const CB_CONFLICT = 15
Const CB_BFORREADNOTES = 6
Const CB_BFORWRITECOM = 1
Const CB_AFTERWRITECOM = 9
Const CB_BFORCOMACTION = 5
Const CB_BFORNOTESACTION = 2
Const CB_TERMINATE = 4
select case stats.cbstatus
case CB_INITIALISE
case CB_ACTIONONLY
case CB_BFOREXPORT
case CB_INVALID
case CB_CATEGORY
case CB_TOTAL
case CB_CONFLICT
case CB_BFORREADNOTES
case CB_BFORWRITECOM
case CB_AFTERWRITECOM
case CB_BFORCOMACTION
' Activate Excel with the second sheet rather then the default first data sheet.
Set stats.XlsSheet = stats.XlsWBook.Sheets(2)
' Or alternative...
' It is also possible to change the sequence of the sheets, making the second sheet the first one:
stats.XlsWBook.Sheets(2).Select
stats.XlsWBook.Sheets(2).Move(stats.XlsWBook.Sheets(1))
Set stats.XlsSheet = stats.XlsWBook.Sheets(1)
case CB_BFORNOTESACTION
if not stats.savdoc is nothing then
else
end if
case CB_TERMINATE
end select
|