Event OnPrint error 423 oSheet=ThisComponent.CurrentController.ActiveSheet

English
,
#1

I have 3 spreadsheets. I need to know which spreadsheets will be printed.
Each spreadsheet will trigger a set of macro codes.
I tried to use the macro code “oSheet=ThisComponent.CurrentController.ActiveSheet”
But I get the error 423.
I tried to use “option VBASupport 1” and the code “If ActiveSheet.Name = “Sheet Name” then” but

I also get a error (error 91) "Type: com.sun.star.uno.RuntimeExceptionMessage: unsatisfied query for interface of type com.sun.star.sheet.XSpreadsheetView! " when I cancel the print job.

How do I know which sheet will be printed via macro without “ThisComponent.CurrentController.ActiveSheet”?

#2

Please post your macro here, at least in parts. There are better programmers than me, but usually some context is necessary.

For example

could be inside some WITH osheet
or being a global reference.

So at this stage it is mostly guesswork…

(You don’t even tell the language, even as I can deduct from VBASupport you try to script in BASIC.)

Also interesting: By wich event is your macro called…

#3

I think that it doesn’t matter the code…the error occurs in the beginning of the macro

Sub PrintEventSheet
Dim oSheet as Object

oSheet = ThisComponent.CurrentController.ActiveSheet
if oSheet.Name = “Name of Sheet” then
…
Elseif oSheet.Name = “Name of Sheet1” then
…
Else
…
Endif

The error occurs at the first line.

#4

Document events are handled like this:

Sub PrintEventSheet(oEvent)
  ' Your code here
End Sub

where oEvent is DocumentEvent Struct.
In particular, the reference to the source document can be obtained as oEvent.Source.

#5

I used “oEvent.source.model.name” and “oEvent.source.name” and I keep on getting error 423

#6

Try:

Sub PrintEventSheet(oEvent)
  Msgbox oEvent.Source.Title & " print"
End Sub
#7

I get the file name…not the sheet name (my file has 3 sheets)

#20

Thank you very much.

#8

Add the following code to a module. Then go to Tools > Customize > Events and set the macro to run for Print Document.

Sub PrintEventSheet(oEvent As Object)
	oDoc = oEvent.Source
	oSheet = oDoc.getCurrentController().ActiveSheet
	MsgBox(oSheet.Name)
End Sub
#9

same error…error 423 ActiveSheet

#10

It works for me in LO 7.1.2.2 x64 on Windows. Start with an empty document and a new module and add the code exactly as shown.

#11

image

#12

Could you upload your file?

#13

Please, wait a minute…I testing it…I create a Module2…I seems that it worked…I keep on testing it

#15

Here is the example I used: Print Event.ods (9.1 KB)

#16

Sub FazerTabelaGratuita(oEvent as Object)

Dim oDoc, oController as Object
Dim oSheet as object
Dim sName as String
oDoc = oEvent.Source
oSheet = oDoc.getCurrentController().ActiveSheet
MsgBox(oSheet.Name)

if oSheet.name = “Gratuita” then 'checar se é a planilha Gratuita
'if oSheet.Name = “Gratuita” then
Dim pVertW, pHoriW, pOuterW, pInnerW, pLiDist as Integer
Dim pRgb as Long
dIM lRow as Long

pVertW  = 20
pHoriW  = 20
pOuterW = 40
pInnerW = 20
pLiDist = 40
pRgb    = RGB(100, 80, 255)

Dim borderLine As New com.sun.star.table.BorderLine2
Dim tb2        As New com.sun.star.table.TableBorder2
Dim rng, oLastCol, oCursor as Object
Dim oRow as Object

oSheet = ThisComponent.Sheets.getByName("Gratuita")
oLastCol = oSheet.getCellRangeByName("C1") ' Coluna C
oCursor = oSheet.createCursorByRange(oLastCol)
oCursor.GotoEndOfUsedArea(false)
lRow = oCursor.RangeAddress.EndRow
if lRow<> 0 then
   lRow = lRow +1
   rng = oSheet.getCellRangeByName("A10:F" & lRow)
   rng.CharHeight = 12
   rng.CharFontName = "Calibri"
   oRow = rng.getRows()
   oRow.Height = 450
   
   oSheet.Rows.removeByIndex(lRow+1, 65500)
   
REM SheetCellRanges selectiopns make no sense, object (Shape e.g.) selections even less.
   borderLine.Color = RGB(0, 0, 0)
 '  borderLine.LineStyle = 1 ' apaga a borda da tabela
   borderLine.LineStyle = 0 
   borderLine.LineWidth = pVertW
   tb2.VerticalLine = borderLine
   tb2.IsVerticalLineValid = True
   borderLine.LineWidth = pHoriW
   tb2.HorizontalLine = borderLine
   tb2.IsHorizontalLineValid = True
  ' borderLine.LineStyle = 1 ' apaga o interior da tabela
   borderLine.LineStyle = 0
   borderLine.LineDistance = pLiDist

   REM With double borderlines having set different outer an inner widths.
   REM there is a bug for many years now. It was never resolved, and will most likely neverba.
   REM Too much code meanwhile relying on the bug - like this Sub!
   REM 'Outer' is wrongly interptreted as 'Top or Right', 'Inner' as 'Left or Bottom. 
   REM The problem is basically the same with LineStyles 4 through 9 which create predefined double lines.
   borderLine.OuterLineWidth = pOuterW
   borderLine.InnerLineWidth = pInnerW
   tb2.TopLine = borderLine
   tb2.IsTopLineValid = True
   tb2.RightLine = borderLine
   tb2.IsRightLineValid = True
   borderLine.OuterLineWidth = pInnerW  REM A little bit counterintuitive.
   borderLine.InnerLineWidth = pOuterW  REM A little bit counterintuitive.
   tb2.LeftLine = borderLine
   tb2.IsLeftLineValid = True
   tb2.BottomLine = borderLine
   tb2.IsBottomLineValid = True
   rng.TableBorder2 = tb2
End if
Call CriarAreaImpressaoGratuita

Else Call CriarAreaImpressaoPagaECorregedoria
End if ’ FIM DE checar se é a planilha Gratuita
End Sub

#17

Sorry. I am brazilian. Many words are Portuguese.

#18

Your code works for me. Did you try the example file I attached?

#19

I will check it. I don´t know why, but it is working now.
Thanks, thanks, thanks a lot.
You are GREAT.
Really, Thanks for your patience.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.