Windows 7 Home Professional
Problem as Title
Hi
The attached code takes source data from a fixed columns, variable rows table and writes a DP table to another sheet. The DP layout info is held in a named range (Weekly Averages) and is used to set the Orientation, Function and Position properties within the DP table.
The code works fine until I try to set the Position of the field within the DP table.
Cannot find any reference within documentation except in Developer Guide which shows how to do this in Java but not OO Basic.
Can anyone see where I am going wrong?
Thanks in advance
P.S. If my code is naive, am fairly new to OO basic!
Code: Select all
REM ***** BASIC *****
Sub DataPilot
Dim Doc as Object
Dim SourceSheet As Object
Dim DestinationSheet As Object
Dim DataCellRange As Object
Dim RangeAddress As Object
Dim Tables As Object
Dim Descriptor As Object
Dim Fields As Object
Dim Field As Object
Dim LayoutCellRange As Object
loadingLibraries 'load xray
Doc = ThisComponent
' Reference sheets
SourceSheet = Doc.Sheets.getByName("Data")
DestinationSheet = Doc.Sheets.getByName("Pivot")
LayoutSheet = Doc.Sheets.getByName("Layouts") ' 4 rows-FieldNames-Orientation-Function-Position
' Size of source data table
rows = Ubound(SourceSheet.Data, 1)
cols = Ubound(SourceSheet.Data(0),1)
' Layout data
LayoutCellRange = LayoutSheet.getCellRangeByName("WeeklyAverages")
DataCellRange = SourceSheet.getCellRangeByPosition(0, 0, cols, rows)
RangeAddress = DataCellRange.RangeAddress
Tables = DestinationSheet.DataPilotTables() 'Tables has all the DataPilot Tables in the Active Sheet
'This part of the code just removes the table if it already exists. Prevents error from running the code several times
'If Tables.hasByName("NewDataPilot") THEN
' Tables.removeByName("NewDataPilot")
'End If
' Just delete the lot ' heavy
PilotEnumeration = Tables.createEnumeration
While PilotEnumeration.hasMoreElements()
Tables.removeByName(PilotEnumeration.nextElement.getName) ' = ParagraphEnumeration.nextElement()
Wend
Descriptor = Tables.createDataPilotDescriptor() 'Descriptor contains the description of a DataPilot Table
Descriptor.ShowFilterButton = False 'Don't show the Filter Button
Descriptor.setSourceRange(RangeAddress) 'Set source data range
Descriptor.RowGrand = "FALSE" 'Turn off the Total line of the Table
Descriptor.IgnoreEmptyRows = "TRUE" 'Ignore empty rows
'xray descriptor
Fields = Descriptor.getDataPilotFields 'Get the DP fields
'xray fields
For colNo = 0 To Fields.Count - 2
Field = Fields.getByIndex(colNo)
'XRay Field
tmp = LayoutCellRange.getCellByPosition(colNo, 1).getValue()
Field.Orientation = LayoutCellRange.getCellByPosition(colNo, 1).getValue()
Field.Function = LayoutCellRange.getCellByPosition(colNo, 2).getValue()
'Works to here
'!! How do I set the 'Position' property?!!
' ?Field.setPropertyValue("Position", LayoutCellRange.getCellByPosition(colNo, 3).getValue() )
' ?this = Field.getPropertyValue("Position")
'Field.Position = LayoutCellRange.getCellByPosition(colNo, 3).getValue()
Next
Cell = DestinationSheet.getCellrangeByName("A1")
Tables.insertNewByName("WeeklyAverages", Cell.CellAddress, Descriptor)
End sub