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
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
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
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.
For i = 0 to oSheets.getCount()-1
oSheet = oSheets.getByIndex(0)
...
DataPilot1 $Sheet1.$A$16:$B$24
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
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.
rmb9 wrote:Am I missing something obvious?
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
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
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
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
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
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
Users browsing this forum: No registered users and 8 guests