Autorefresh DataPilot Tables

[newbie_nathan]

Is it possible to set DataPilot tables to auto-refresh? I have an MS Excel 2007 file that I'm opening and every time I do I have to click on the DataPilot and manually refresh it in order for the dropdowns to appear. I'm not sure if what I'm asking for is even possible, but it seems like there should be a way to set Open Office to auto-refresh all DataPilots.

[newbie_nathan]

By the lack of response I'm guessing there's no way to do this?

I've looked through various posts and checked the help file of the application and can't find anything to assist...

[Villeroy]

The pilots should be refreshed on file open. Possibly there is some hick-up due to the file format.

[newbie_nathan]

Is it possible that the auto-refresh feature may be toggled off anywhere? There is very little info in the help file about DataPIlots.

[Villeroy]

There is no auto-refresh, thus no option fro it. Pilots should refresh on open and then manually when the source data have changed.

[Villeroy]

Copied some of my macros from the "old" forum:
Refresh all database ranges and pilots:

Code: Select all

REM  *****  BASIC  *****
Sub refresh_DBRanges_And_Pilots
REM disable auto-calculation:
   bAutoCalc = thisComponent.IsAutomaticCalculationEnabled
   thisComponent.enableAutomaticCalculation(False)
   oDBRangesEnum = thisComponent.DatabaseRanges.createEnumeration()
   refresh_Enumeration(oDBRangesEnum)
   oSheetsEnum = thisComponent.Sheets.createEnumeration()
   refresh_All_DataPilots(oSheetsEnum)
REM reset auto-calculation to previous state:
   thisComponent.enableAutomaticCalculation(bAutoCalc)
End Sub
Sub refresh_All_DataPilots(oSheetsEnum)
REM Bug: Disregards pilots created from datasource
   while oSheetsEnum.hasMoreElements()
      oSheet = oSheetsEnum.nextElement()
      oDPEnum = oSheet.DataPilotTables.createEnumeration()
      refresh_Enumeration(oDPEnum)
   wend
End Sub
Sub refresh_Enumeration(oEnum)
   while oEnum.hasMoreElements()
      oNext = oEnum.nextElement()
      oNext.refresh()
   wend
End Sub 

And another one for links:

Code: Select all

Sub refreshAllSheetLinks()
oEnum = thisComponent.AreaLinks.createEnumeration
while oEnum.hasMoreElements
   oLink = oEnum.NextElement
   oLink.refresh
wend
oEnum = thisComponent.SheetLinks.createEnumeration
while oEnum.hasMoreElements
   oLink = oEnum.NextElement
   oLink.refresh
wend
oEnum = thisComponent.DDELinks.createEnumeration
while oEnum.hasMoreElements
   oLink = oEnum.NextElement
   oLink.refresh
wend
End Sub 

[newbie_nathan]

Ok, this is really good... thank you. What I can't figure out is how to set this to be an Open Office standard. I'm opening a spreadsheet that is linked from a site. The spreadsheet is updated daily so it's not like I can just save a copy and embed this macro. Is there a way to set this as a standard for all open office spreadsheets?

[Villeroy]

Tools>Macros>Organize>Basic...[Organizer...]
Pick the userspecific container "My Macros", create a new library named, let's say "Calc", with a new module, let's say "Refreshables". Edit the new module. Replace it's default content with the above code.

If you need this for all users and/or other machines:
Go back to the organizer, pick the new library and push [Export...]
[X]Export as add-on, <specify path-name>

Call automatically when a spreadsheet gets opened (may be annoying in some cases).

Code: Select all

Sub onDocumentOpen()
If thisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument")then
  GlobalScope.BasicLibraries.loadLibrary("Calc")
  Calc.Refreshables.refresh_DBRanges_And_Pilots
end If
End Sub

Menu:Tools>Customize... tab:Events, save in: OpenOffice.org, Open Document [Macro...], Calc.Refreshables.refresh_DBRanges_And_Pilots

[ubundom]

I have a "cascade" of Datapilot tables; do I need to modify this macro to check for dependencies? I.e.: dp_table_01 on sheet2 refers to the data table on sheet1; dp_table_02 on sheet3 refers to dp_table_01. To get the correct results in dp_table_02, I must first refresh dp_table_01.

[Villeroy]

I have a "cascade" of Datapilot tables; do I need to modify this macro to check for dependencies? I.e.: dp_table_01 on sheet2 refers to the data table on sheet1; dp_table_02 on sheet3 refers to dp_table_01. To get the correct results in dp_table_02, I must first refresh dp_table_01.

Now, this is a simple question I can not answer for sure. The enumeration access seems to loop through the sheets from first sheet to last sheet and I believe it loops through pilots on the same table in order of creation.
Instead of interface com.sun.star.container.XEnumerationAccess one could use XIndexAccess which loops from the first to the last sheet-tab.

Code: Select all

For i = 0 to oSheets.getCount()-1
oSheet = oSheets.getByIndex(0)
...

Still not sure about the order of pilots, particulary if you replace the first one with another one.

[Villeroy]

Quick&Dirty Basic hack. The first routine allows you to specify the refresh order by the pilot-names (yes, they have names). The second routine prints a report of all pilots in document. Place the cell cursor at a top-left cell of a free range and run Sub report_Pilots()

Code: Select all

DataPilot1	$Sheet1.$A$16:$B$24

Code: Select all

REM  *****  BASIC  *****
Sub refresh_Pilots()
REM Define a nested array of pairs with (SheetName, DPName) in the order of the desired refresh:
sDPNames() = Array( _
Array("Sheet1","DataPilot1"), _
Array("Sheet1","DataPilot2"), _
Array("Sheet1","DataPilot3"), _
Array("Sheet1","DataPilot4"))
for i = 0 to uBound(sDPNames)
	a() = sDPNames(i)
	oSh = thisComponent.Sheets.getByName(a(0))
	oDP = oSh.DataPilotTables.getByName(a(1))
	oDP.refresh()
next
End Sub

Sub report_Pilots()
REM print names and output-addresses of all pilots starting at the active cell
'calls: getActiveCell, getRangeByAddress, bas_PushArray
Dim iCount%
REM load helper libraries for calling the above routines
Globalscope.basiclibraries.loadlibrary("Calc")
oView = thiscomponent.getCurrentController()
oCell = getActiveCell(oView)
eSheets = thisComponent.Sheets.createEnumeration()
addr = oCell.getRangeAddress()
addr.EndColumn = addr.EndColumn +1
addr.EndRow = addr.EndRow -1
Dim a()
while eSheets.hasMoreElements()
	oSh = eSheets.nextElement()
	eDPs = oSh.DataPilotTables.createEnumeration()
	while eDPs.hasMoreElements()
		addr.EndRow = addr.EndRow +1
		oDP = eDPs.nextElement()
		oArea = oDP.OutputRange()
		oRg = getRangeByAddress(oSh, oArea)
		bas_PushArray(a(), Array(oDP.getName(), oRg.AbsoluteName))
	wend
wend
oRg = getRangeByAddress(ThisComponent, addr)
oRg.setDataArray(a())
End Sub

[rmb9]

There is no auto-refresh, thus no option fro it. Pilots should refresh on open and then manually when the source data have changed.

My Data Pilots are refreshing on file open but Calc is not asking me to save the updates when I close it and it seems to be discarding the updates unless I force a save.

The problem I have is that the sheet is extracting useful information with the GETPIVOTDATA function. I have multiple files (e.g. one for 2008, one for 2007 etc.) and a separate file that has links to this useful information in order to generate comparison graphs and trends. If I go into the current file (2008) and add new financial information then the Data Pilots do not update unless I do them manually (there's a lot) or I save, close and open again. I then have to make a minor modification somewhere unimportant in order to enable the save function so that the updated Data Pilots and the corresponding GETPIVOTDATA information is updated and available for the graphs file when it is next opened.

Am I missing something obvious?

Thanks in advance,

R.

[Villeroy]

Am I missing something obvious?

Yes. Never split your data into separate lists. Put everything into one list, create one large data pilot with dates as row field, then group (F12) the dates by months and years.

[oousersym]

Hi all.
Is it possible to diable refresh programtically? I do not want to refresh DataPilot.

[dech]

Villeroy:
Hello, Im interested in your macro that refreshes specific pivot table (by name):

Code: Select all

REM  *****  BASIC  *****
Sub refresh_Pilots()
REM Define a nested array of pairs with (SheetName, DPName) in the order of the desired refresh:
sDPNames() = Array( _
Array("Sheet1","DataPilot1"), _
Array("Sheet1","DataPilot2"), _
Array("Sheet1","DataPilot3"), _
Array("Sheet1","DataPilot4"))
for i = 0 to uBound(sDPNames)
   a() = sDPNames(i)
   oSh = thisComponent.Sheets.getByName(a(0))
   oDP = oSh.DataPilotTables.getByName(a(1))  'when error occure,this line is selected
   oDP.refresh()
next
End Sub

Im not very skilled with macros,but Im trying understand. I have copied macro to my document. I have pivot table in sheet1 - I have selected it ,then menu-data-define array- I write "DataPilot1". I started macro and error occured: "BASIC - running error . There is an exception. Type: com.sun.star.container.NoSuchElementException. Message: ."
Maybe it is more complicated then I think. If somebody knows what is wrong,tell me,thx.

[Villeroy]

Use the correct pilot names or access them by index rather than name. My code simply refreshes all of them.

[dech]

wow! it started working! catch was that I had wrong names of pivottables(or datapilot) in that macro as you said - but to figure what are correct names(or indexes) of PT is hard- I had to try writing in that macro DataPilot1,DataPilot2,DataPilot3 ect. till DataPilot20 (I have many PT) and watch which PT changed to match name to correct PT-which is exhausting... It was harder to figure it out due to another thing:the names or indexes of PT are not all from "0" to "20" - PT 3# for example does not exists(because I have erased it earlier and there is more PT that were erased)-that is why your macro is still saying errors. - So I need to "clean" the list of PT names in macro that do not exist any more. But actually I do not need refresh PT in pre-set order.I just need to refresh specific PT-so I have siplified your macro:

Code: Select all

Sub refresh_specific_Pilot(s)
   oSh = thisComponent.Sheets.getByName("name_of_sheet")
   oDP = oSh.DataPilotTables.getByIndex(?)    'instead of "?" put correct number of desired PT
'OR refresh DP according to its name:
   oDP = oSh.DataPilotTables.getByName("DataPilot?")  'instead of "?" put correct number of desired PT
   oDP.refresh()
End Sub

So refreshing works fine-just the way of figuring which PT has which name or index is soo bad.
Is there any other convenient way to figure what is the name or index of specific PT?

[Villeroy]

Simply use the facilities of the Basic editor (stop marks, variable watcher)
Have a look at module Tools.Debug in the OOo macros.
Use the print command like print join(objPilotTables.getElementNames())
Use one of the frequently discussed object inspectors.
The following snippet has been generated by http://extensions.services.openoffice.o ... roject/MRI

Code: Select all

Sub Snippet(Optional oInitialTarget As Object)
  Dim oSheets As Object
  Dim oObj_1 As Object
  Dim oDataPilotTables As Object
  Dim oElementNames As Object

  oSheets = oInitialTarget.getSheets()
  oObj_1 = oSheets.getByIndex(0)
  oDataPilotTables = oObj_1.getDataPilotTables()
  
  oElementNames = oDataPilotTables.getElementNames()
End Sub

[masavini]

hi,
i have a large complex datasheet with 3 large complex datapilots.

a macro changes some values of the datasheet, then refreshes the 3 datapilots using villeroys' macros.

if i let the macro change some values and AUTOMATICALLY refresh the datapilots, sometimes it works, sometimes openoffice crashes. sometimes it needs 3 "cycles" to crash, sometimes 20, but it will.
if i modify the macro so that it only changes those values in the datasheet and i MANUALLY refresh the 3 datapilots on by one, everything goes ALWAYS fine.

how can i try to improve datapilots refresh stability? please don't answer "simplify the datapilots or the datasheet"...

thanks

[dech]

hi, I had this problem too, but solved it long time ago. im not sure what was the problem but I suppose that it was the macro code to refresh pivot tables. So the code Im using now and it is working is this:
http://www.oooforum.org/forum/viewtopic.phtml?t=100239

Code: Select all

Sub update_PT

   sheet = thisComponent.Sheets.getByName("Sheet1") 
   PT = sheet.DataPilotTables.getByIndex(0)   ' put (1) (2) etc to update second,third etc. pivot table in sheet 
   PT.refresh()

end sub

well...maybe...if I remember it now more clearly...I have updated OO and the problem stopped. So maybe it was not the code...
Im running OO 3.2.1 Win 7

[masavini]

great, great, great suggestion...
i guess the problem was that my document has a "ghost" datapilot, that caused the crashes when trying to refresh it...
now with your macro i can choose which datapilot should be refreshed and it works great...

here is my macro:

Code: Select all

Sub update_PT

   sheet = thisComponent.Sheets.getByName("dati.fat")
   PT = sheet.DataPilotTables.getByIndex(0)
   PT.refresh()
   PT = sheet.DataPilotTables.getByIndex(1)
   PT.refresh()
   PT = sheet.DataPilotTables.getByIndex(3)
   PT.refresh()

end sub

as you can see, the macro doesn't refresh the datapilot n.2, as it is not present in the document...
is there some kind of PT.delete() command?

[dech]

Im not aware of such command.
But I have a little brainstorm here which may pusch you forward:
If you run this macro with the "...getbyindex(2)" and it will not return an error, you can be sure that you have somewhere in sheet this PT,but it is possibly too small and you have overlooked it.

Or you can exit your sheet and open it again(for sure quit quick OO start,or restart PC) because I SUPPOSE that If you have 3 PT and you delete the 2nd PT,indexes of 2 PT left are NOT 1 and 3. but 1 and 2 - OO reorganizes indexes of PT so there is no gap in their indexing - all this after opening OO again. If this is true - pay attention to deleting PT because indexes set in your macros will be reffering to another PT everytime you delete some PT.(if you delete the earlier PT the bigger mass in indexing vs. macros it does)

and another hint:
Often happend to me that even if I had 2 PT in sheet, my macro didnt find the 2nd PT -error ocurred when running code above with indexes 0 and 1. reason was that the 2nd PT was created by "copying"(cloning) the 1st PT. "Copying" means: RMB on 1st PT - "beginning", "result to" - you change address to put new PT in,OK. New(2nd) PT occures in destination you set. But this copyed (cloned) PT is missing its unique index....so you must create new PT from beginning - "Data" - "data guide"(im not sure about the english translation due to my native language I have in OO) and now the code works....

[masavini]

dech,
what you wrote in the last hint is true and that's the reason for i spent a ween-end, last year, to recreate all of my copy & pasted datapilots from scratch.
given that, how can i "find" the datapilot indexed as n. 2? like the "ghost" one, i mean... i tried with the navigator, but i can't find it...
i'd like to clean up my files a bit...

[dech]

How to find desired PT - I have no idea

[dech]

Hi guys,
this code that refreshed desired pivot tables stopped working in FO 3.4 (maybe also in OO 3.3 or FO 3.3)

Code: Select all

Sub update_PT

   sheet = thisComponent.Sheets.getByName("Sheet1") 
   PT = sheet.DataPilotTables.getByIndex(0)   ' put (1) (2) etc to update second,third etc. pivot table in sheet 
   PT.refresh()

end sub

why? it was the best and easiest way how to refres PT by macro! now it doest work...I have to stay at OO 3.2.1 because of this ability.
Is there any new way how to refresh PT in OO 3.3/FO3.3/FO3.4???

Btw. in OO 3.3 my PT totaly colabsed! they have lost they link to source area, it is not possible to edit their source area in their properties, the only way how to make PT work in OO 3.3 is to create whole new PT - but I will do this only if I decide to leave this world by painful way - which this is. What is wrong with PT in OO 3.3 that were created in OO 3.2. ?????

Btw.II- When I opened my document in FO 3.4. (which was created in OO 3.2) and only clicked LMB in any PT - the whole FO program colabsed!! I cant beleive. I thought that things will be only better and not worse...

Btw.III - the FO 3.4 colabses also when: create simple pivot table that consists of min 2 columns. Now drag by mouse one column and pull it away-as If you wanted to remove this column....FO 3.4 crashes...

what is going on???