Live Office Auto Refresh via VBA

system · August 24, 2012, 2:42pm

I know some others have already come up with the “one line of code” that does this “refreshing of a document” using Live Office with Microsoft Office 2003 but as I discovered during a recent customer visit this platform is becoming fast obsolete and many people need another solution.

Therefore I attach the following 2 spreadsheets in the attached ZIP archive file which are based on the Efashion universe which allow the user to refresh a Live Office report using a VBA macro.

Instead of using the Microsoft Office object model this uses the crystal_enterpriseframework_addin.dll which is installed with every Live Office deployment.

Therefore the code will work with every version of Microsoft Office (2003-2010) and also with Business Objects XI 3.1 and SAP BI 4.

The first file - execute_live_office_refresh_embedded_macro_vba_box31.xlsm is for use with Business Objects XI Live Office 3.1.

(The Live Office query can be refreshed in the above file via a CTRL+R keyboard shortcut that triggers the report refresh)

The second file - execute_live_office_refresh_embedded_macro_vba_sapbi4.xlsm is for use with SAP Business Intelligence Platform 4.0.

(The Live Office query is refresh automatically in the SAP BI 4.0 version)

Please feel free to upload this if you find it’s useful and also let me know if you have any questions.

Cheers,
Gary
live_office_vba_refresh_query.zip (57.0 KB)

gary_scott (BOB member since 2004-12-17)

system · August 31, 2012, 12:33pm

Hi,
Very interesting, but I dont see the zip file, could you upload?
Thanks in advance
Regards
Paco.

fjlg (BOB member since 2012-08-31)

system · August 31, 2012, 1:48pm

Paco,

Only moderators can view attachments in this forum. You will be able to download the files once this submission is approved and moved to the Downloads forum.

BoB LoblaW (BOB member since 2007-10-23)

system · September 14, 2012, 9:59am

Hi Gary,

Interesting and very useful tools that I have an immediate need of!

Any chance of also copying sample code into a message?

a. would impart some knowledge on method calls without having to download file (and associated security concerns surrounding this)

b. would allow people to use content whilst waiting for this to be moved to the download area…

Thanks!

Darth Services (BOB member since 2007-11-20)

system · September 14, 2012, 1:16pm

Moderator note:
Approved, and moved to BOB’s Downloads.

Marek Chladny (BOB member since 2003-11-27)

system · September 17, 2012, 11:37am

Yay! Thanks Marek

Darth Services (BOB member since 2007-11-20)

system · September 26, 2012, 1:33pm

This was exactly what I was looking for.
Where did you find the information to get you started? I’ll need some more VBA tricks interfacing with Live Office in a project of mine.

Kind regards

Guy

GuyDB74 (BOB member since 2010-01-04)

system · December 6, 2012, 3:19pm

To me the correct solution to refresh all liveoffice objects in excel at once from the macro vba code was:
Application.COMAddIns(1).Object.liveObjects.Refresh True

It works at least in Excel 2003 and 2007.

pleino (BOB member since 2007-12-19)

system · March 1, 2013, 4:54pm

Gary, I tried to copy and paste your code into my file to create a macro to refresh my workbook, but I am receiving this error:

“Compile Error: User-defined type not define” the error also highlights
“lo As New Class1”

Any idea what I am doing wrong, or any suggestions on how to create a macro to refresh my reports?

Thank You,

Max

HelpMePlease (BOB member since 2013-03-01)

system · March 4, 2013, 8:32am

The message you have got is a VBA error.

As you have copied and pasted it the code, you may have not set the appropriate reference in the VBE.

Googling your error message returns http://support.microsoft.com/kb/858611/en-us which includes the steps to reference the “Microsoft Active X Data Object Library”.

To solve your message you will need to set the reference to the library that contains the object which the error message is complaining about.

If you do not know which library this is, you could open the original project from where you copied the code and see which libraries are referenced there.

Darth Services (BOB member since 2007-11-20)

system · March 5, 2013, 7:41pm

Darth,

That was the problem, and I’m getting closer to the solution. I found the library the code referenced, but that library isn’t in my other workbooks. I searched online to try to figure out how to copy the library over, but I can’t figure it out. Do you know how?

Thank again! I really appreciate the help

HelpMePlease (BOB member since 2013-03-01)

system · March 6, 2013, 8:49am

I would say that the libraries should not be copied, but are installed from the Live Office, or Business Objects client tools SAP software downloads.

Do you know what BO software (and versions) you already have installed on your machine and which library does it say it is missing the reference for?

Darth Services (BOB member since 2007-11-20)

system · March 21, 2013, 7:45pm

I am trying to use this macro with a live office object that has certain prompts binded to excel cells. It refreshes the document, but it is unable to account for the prompts that I bound. Do you know how I can fix this?

HelpMePlease (BOB member since 2013-03-01)

system · May 6, 2014, 10:39am

Hi,

I’ve tried the solution above, but it doesnt work for me.

—code in ThisWorkbook
Dim lo As New Class1

Private Sub Workbook_Open()

Set lo.loevent = Application.COMAddIns("CrystalOfficeAddins.CrystalComAddin.7").Object

   Application.EnableEvents = True
   
   Application.ScreenUpdating = True
   
   MsgBox "opened"


     Application.OnTime Now() + TimeValue("00:00:10"), "loevent_AfterFunction"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set lo.loevent = Nothing

End Sub

Public Sub Refresh_All()
Set lo.loevent = Application.COMAddIns(“CrystalOfficeAddins.CrystalComAddin.7”).Object
lo.loevent.LiveObjects.Refresh

End Sub

------code in Class Module
Public WithEvents loevent As CrystalAddin

Private Sub loevent_BeforeFunction(ByVal addinName As String, ByVal functionName As String, ByVal Parameters As CRYSTAL_ADDIN_FRAMEWORKLib.IParameters)

End Sub

Private Sub loevent_AfterFunction(ByVal addinName As String, ByVal functionName As String, ByVal Parameters As CRYSTAL_ADDIN_FRAMEWORKLib.IParameters)

If functionName = “RefreshAllViews” Or functionName = “Refresh_View” Then

End If

End Sub

Private Sub loevent_RefreshViews()

loevent.LiveObjects.Refresh

End Sub

Could any one help on this.

Thanks,

Youqin[/code]

youqinweng (BOB member since 2014-05-06)

system · May 10, 2014, 8:57am

Hi All,

I wrote this solution around 2 years or so ago now when I was still working with SAP Business Objects.

Unfortunately for those asking me to tweak the solution so it works for them or so that it works with the latest versions of SAP BO I am unable to do so as even though I still work in the BI arena I no longer work with SAP BO so am unable to even view/amend the original solution I wrote.

Thanks for your understanding.

Cheers,
Gary

gary_scott (BOB member since 2004-12-17)