LiveOffice event handlers in VBA
Recently I’ve faced with an activities of a report formatting in LiveOffice.
Everything is ok when the reports are fixed-size. But when It’s not the case It needs an additional processing in VBA.
The next step-by-step tutorial explains how to intercept the processing of LiveOffice class events.
There is an class which represents LiveOffice Toolbar. Let’s look at it.
Run MS Excel (I use one 2010) then go to VBA editor (Alt+F11).
Go to “Tools\References” and find the “crystal_addin_framework 1.0 Type Library”. Tick it and press “OK”.
Than restrict the classes by selecting in dropdown menu of “Object Browser” (“View\Object Browser” or F2) the item “CRYSTAL_ADDIN_FRAMEWORKLib” and select the class “CrystalAddin”.
Look at right side of the window. There you can see their members and the events among them. Get two of them “BeforeFunction” and “AfterFunction”.
Now move on VBA coding.
In our VBAProject insert a Class Module “Class1” and define a variable “loevent” of class “CrystalAddin” like this
Public WithEvents loevent As CrystalAddin
Select variable “loevent” from first dropdown list and event “BeforeFunction” from the second one. And the next definition of the subroutine appears.
Private Sub loevent_BeforeFunction(ByVal addinName As String, ByVal functionName As String, ByVal Parameters As CRYSTAL_ADDIN_FRAMEWORKLib.IParameters) End Sub
Repeat the selection and create a definition of “AfterFunction” event handler subroutine.
Private Sub loevent_AfterFunction(ByVal addinName As String, ByVal functionName As String, ByVal Parameters As CRYSTAL_ADDIN_FRAMEWORKLib.IParameters) End Sub
You can place into these subroutins any code as you like or place a breakpoint to explore the abilities of the LiveOffice object handlers.
I wrote the following
Private Sub loevent_AfterFunction(ByVal addinName As String, ByVal functionName As String, ByVal Parameters As CRYSTAL_ADDIN_FRAMEWORKLib.IParameters) ' MsgBox ("AfterFunction") If functionName = "RefreshAllViews" Or functionName = "Refresh_View" Then Call Transform End If End Sub
The function names “RefreshAllViews” and “Refresh_View” respective to the buttons “Refresh All Object” and “Refresh Object” on LiveOffice Toolbar.
To make it work it needs to define a new variable of our new Class1. Double click to “ThisWorkbook” object and place code here like this
Dim lo As New Class1 Private Sub Workbook_Open() Set lo.loevent = Application.COMAddIns("CrystalOfficeAddins.CrystalComAddin.7").Object End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Set lo.loevent = Nothing End Sub
The variable “lo” is an instance of our Class1. In subroutine “Workbook_Open” the event handler is activated and in the next subroutine is disactivated.
If you need some handlings after opening the document you can experiment with event “AfterDocumentLoad” of the “CrystalAddin” class or something else.
It will be your homework 😉
Well, it can be done by the event WindowActivate of the Workbook instance. See example below.
Private Sub Workbook_WindowActivate(ByVal Wn As Window) Static runOnce As Boolean If Not runOnce Then Call Transform runOnce = True End If End Sub
Well, that’s all folks 🙂
Very good information! This is exactly what I need to do, but I've encountered an error.
When this runs in my case I get 'Type mismatch error' on the portion below.
Running this in Office 2010 with SAP BO Live Office 18.104.22.1680. I've added the crystal_addin_framework 1.0 Type Library as instructed.
I'm not sure where to start to fix this, any suggestions?
I've also faced with such error when I get installed some other COM addons and LiveOffice addon may not be in the first place in addons array. Therefore I have changed the reference to "CrystalOfficeAddins.CrystalComAddin.7" as you can see in this tutorial.