MACRO in Calc: Output result to specified range

[am2del]

Greetings,

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.

Result example:

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”

@$A$2 “TestSheetA”

@$A$3 “TestSheetB”

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.

Cheers!

EDIT: Nov 10th 2017

@ratslinger

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.

[Ratslinger]

@am2del First an FYI - you should just use a comment (add a comment) under answer you are responding to (may be more than one answer) instead of placing the response in your original question. Additionally, you can use Python in LO but it is a different method - there is no built-in IDE for it. Personally started using Python in LO earlier this year.

[am2del]

@Ratslinger

I’m aware Python works in LO, and did look into using it but turned out Basic is favoured for this project. My oppinion was ignored, due to the timeframe… but originally wanted to do the whole thing as a specialized stand-alone, modulized, serverbased solution which clients connected to simultaniously - which clearly had been way more time-efficient on user-end. Not to mention ease of administration and adapted extentions later. LO calc nice, but not optimum for huge data sets.

[Ratslinger]

Hello,

Here is a simple approach not storing the sheet names in an array. Using oSheets for retrieval of names and oTargetSheet for where the list is to be inserted.

Remember Sheets, columns and Rows are all indexed starting with 0 (zero). There are more things you can check & do but I’ll leave that for you to experiment with.

REM NOTE: changed Sheet to iSheet
Function SheetNamesList(Optional iSheet as Integer, Optional StartRow As Integer, Optional StartCol As Integer, Optional HoriVert As Integer) 
  Dim oSheets 			As Object
  Dim oTargetSheet		As Object
  Dim oTargetCell		As Object
  Dim iSheetCount		As Integer
  Dim sSheetName		As String
  IF IsMissing(iSheet) 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
REM Get Sheets
  oSheets = ThisComponent.getSheets()
REM Get the sheet you are going to print the list on
  oTargetSheet = oSheets.getByIndex(iSheet)
Rem Get total # of sheets
  iSheetCount = oSheets.Count
Rem loop for getting sheet names (index starts at 0)
  for x = 0 to iSheetCount - 1
REM Get Cell where next string is to be placed
	  oTargetCell = oTargetSheet.getCellByPosition(StartCol,StartRow)
Rem Get name of next sheet in sequence
	  sSheetName = oSheets.getByIndex(x).Name
Rem Move the sheet name into the cell
	  oTargetCell.setString(sSheetName)
REM Determine if list is Horiz or Vert & increment accordingly
	  If HoriVert = 0 then
	  	StartRow = StartRow +1
	  Else
	  	StartCol = StartCol +1
	  EndIf
  next
End Function

If this answers your question please click on the (upper left area of answer).

[Ratslinger]

FYI - You cannot print the array in the manner you pose. It appears you are trying to utilize syntax of other languages (i.e. Python). You need to learn/use Basic syntax or if you actually prefer Python, you’d have to learn how to apply that with LO - different process (and not for discussion in this question).

Here is a link to a book on LO macros - OOME.