Ask Your Question
0

MACRO in Calc: Output result to specified range

asked 2017-11-10 01:35:11 +0200

am2del gravatar image

updated 2017-11-10 21:25:56 +0200

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 ... (more)

edit retag flag offensive close merge delete

Comments

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.

Ratslinger gravatar imageRatslinger ( 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.

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

1 Answer

Sort by » oldest newest most voted
1

answered 2017-11-10 03:15:02 +0200

Ratslinger gravatar image

updated 2017-11-10 06:06:51 +0200

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

edit flag offensive delete link more

Comments

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.

Ratslinger gravatar imageRatslinger ( 2017-11-10 04:49:11 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-11-10 01:35:11 +0200

Seen: 482 times

Last updated: Nov 10 '17