# MACRO in Calc: Output result to specified range

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

'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


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

edit retag close merge delete

1

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

( 2017-11-10 21:51:41 +0200 )edit

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

( 2017-11-11 01:53:14 +0200 )edit

Sort by » oldest newest most voted

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


more

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.

( 2017-11-10 04:49:11 +0200 )edit