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.

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?

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.

ubundom wrote: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.

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()

Villeroy wrote: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.

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.

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:

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

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"...

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...

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....

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...

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...