BusinessObjects Board

Excel: Refresh from macro / VBA

other Live Office
#1

Hello all,

I have an Excel sheet containing several Live Office queries. I would like to build a macro that performs some actions and refresh all Live Office queries.
Does anyone know if it is possible to call the Live Office refresh function from VBA and if so, how to do it?
I can get a reference to the Live Office add-in through Application.COMAddins, but I don’t know what public members this object has (if any).

Any insights are appreciated.

Marnix

MYH (BOB member since 2009-02-27)

Refresh Some... not all Objects
#2

Hello,

  1. Refreshing LO objects. You can use quite a generic method of executing LO menu items. This should work:
Application.CommandBars(1).Controls("&LiveOffice").Controls(6).Execute

It’s equal to “Refresh All Objects”.

  1. Members of LO COMAddin object. You can view its structure in object browser, however the addin itself isn’t available in Tools->References list. You should browse for crystal_addin_framework.dll (in my case it was hidden in “C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86”) and add it to references. Then press F2, select CRYSTAL_ADDIN_FRAMEWORKLib and research the object named crystalAddin. I was happy to find out its events and methods :slight_smile:

Good luck.

DDrazdou (BOB member since 2010-05-17)

#3

Thanks DDRazdou! I’ve been looking for some VBA for a while now and this works perfectly! :lol:

Lemo (BOB member since 2009-04-09)

#4

I’m going to ask an ignorant question but using vba code would it be possible to automate live office connection refreshes , lets say over night? (so that when a user opens their excel file in the morning they have fresh data)

SAE09 (BOB member since 2009-03-25)

#5

Hi DDRazdou ,

If you have any macro to run automatically live office , can you please share with me .

Thanks in advance

patriot3029 :india: (BOB member since 2006-12-08)

#6

this appears to only work in Excel 2003 and prior. Does anyone know how to do this in 2007 or 2010?

Puertorekinsam (BOB member since 2010-09-20)

#7

Hi All,

I had the same problem as some of you above and I have a solution to this submitted which can be found in BOB’s Downloads that will allow you to refresh Live Office queries via a macro in newer versions of Microsoft Office (post 2003).

See here for the link :-

Cheers,
Gary

gary_scott (BOB member since 2004-12-17)

#8

Even I am looking for an answer to the question asked by SAE09.

I would appreciate if anyone can provide some insight into it.

vep (BOB member since 2013-07-29)

#9

Hi all,

I’ve been looking for a way to do this recently. Most solutions seemed to attempt to reference the Crystal VB code libraries, and none of these worked for me. I have a much simpler approach that works.

There is a setting in LIVE office under application options called ‘Refresh Live Office object when binding cell changes’. Essentially when a cell in Excel that is linked to a prompt in your live office query changes then refresh it.

You can use this to force Live office to refresh by simply using VBA to change a binding cell (prompt) value. It does require that there is a prompt in your query but this can be a dummy value that would never be false. Even if you repeatable set the cell value to the same value it will still be enough to force a refresh.

An example of the code I am using is below - in this example, my binding cell is E5 and will always run the Live Office query every 2 minutes using a value of “Y”.

Sub RunEveryTwoMinutes()

Worksheets("Sheet1").Range("E5").Value = "Y"
Application.OnTime Now + TimeValue("00:02:00"), "ThisWorkbook.RunEveryTwoMinutes"

End Sub

Private Sub Workbook_Open()

RunEveryTwoMinutes

End Sub

jaymo (BOB member since 2011-09-28)