I’m quite new to writing these macros, but trying to create a macro for listing the names of all sheets in the document, then printing the list in a cell-range (either horizontal or vertical). Printing it as a list using a separator in the cell the formula is in work fine when I try that… See the commented line in the “Until”-loop in code block. But what I want is to print it in a cell range - which, after hours, I still cannot figure out how to place these into a cell range where each cell contains the name of one sheet.
Sheet$0 is named “TestSheetA”
Sheet$1 is named “TestSheetB”
@$A$1 "=SheetNamesList(SHEET(),ROW()+1,COLUMN(),1) "
Should at current sheet print:
@$A$1 “Sheet Names”
Function SheetNamesList(Optional Sheet as Integer, Optional StartRow As Integer, Optional StartCol As Integer, Optional HoriVert As Integer) 'USAGE: SheetNamesList(TargetSheet,StartRow,StartCol,Horizontal=0|Vertical=1) - TargetSheet, StartRow and StartCol MUST BE INTEGER! Horizontal|Vertical is optional, but MUST BE BINARY! DEFAULT = VERTICAL. IF IsMissing(Sheet) OR IsMissing(StartRow) OR IsMissing(StartCol) THEN SheetNamesList = "USAGE: SheetNamesList(TargetSheet,StartRow,StartCol,Horizontal=0|Vertical=1) - TargetSheet, StartRow and StartCol MUST BE INTEGER! Horizontal|Vertical is optional, but MUST BE BINARY! DEFAULT = VERTICAL." Exit Function ENDif Dim oDoc As Object Dim nSheets As Integer Dim range As New com.sun.star.table.CellRangeAddress 'Define CURRENT DOCUMENT: oDoc = ThisComponent 'Get TOTAL SHEET COUNT: nSheets = oDoc.Sheets.Count 'Setting the SHEET to PRINT in: range.sheet = Sheet 'Setting the ROW & COLUMN to START at: range.StartRow = StartRow range.StartColumn = StartCol 'Setting the ROW & COLUMN to END at: (Based on Horizontal or Vertical direction request.) range.EndRow = StartRow + nSheets range.EndColumn = StartCol IF (HoriVert = 0) THEN range.EndRow = StartRow range.EndColumn = StartCol + nSheets ENDif Dim A(nSheets) as Long n = 1 SheetNamesList = oDoc.Sheets(0).GetName Do Until n >= oDoc.Sheets.Count A(n) = oDoc.Sheets(n).GetName ' SheetNamesList = SheetNamesList & ", " & oDoc.Sheets(n).GetName n = n+1 Loop 'All is good until here... Where neither "Sheets()" nor "CurrentController.getActiveSheet()" for "aSheet" works. Dim aSheet As Object 'For PRE-DEFINED SHEET use: aSheet = oDoc.Sheets(Sheet) 'For CURRETLY ACTIVE SHEET use: 'aSheet = oDoc.CurrentController.getActiveSheet() 'Setting the user-defined range: 'This is where it hangs up resulting in ERROR, see below code block. aSheet.setPrintAreas(range()) 'Printing the Array "A". oDoc.Print(A()) SheetNamesList = "Sheet Names" End Function
The ERROR reads:
BASIC runtime error. An exception occurred Type: com.sun.star.lang.IllegalArgumentException Message: cannot coerce argument type during corereflection call: arg no.: 0 expected: "com.sun.star.table.CellRangeAddress" actual: "void".
Am I defining the “range” in a bad way or am I using the wrong methods?
Would appriciate guidance.
If this could get working, then it’d save a lot of manual editing further down the road as it could be used to generate other lists as well.
EDIT: Nov 10th 2017
A good solution, thanks a lot for your help and tips. Just a little tweaking and it’s good to go.
As you suspected - my background is, as of last ten years, heavily influenced by Python as well as ShellScript. Did some Basic in DOS-environment back in the 90’s but forgotten more than I thought when I started this project. Not even possitive that old knowledge matches todays Basic… Wouldn’t surprise me if there were a lot of changes the past 20 years.