I need to be able to select and print the sheets in a calc sheet. I have been all over Google and A P’s guides and found at least 6 different ways of creating a print function macro, some quite ‘dated’ and some of which do not seem to work! The API is not much help.
Is there now a simple construct for a macro to do this, let’s say with ‘foo.ods’?
24 hours now and no-one seems able to suggest a simple block of code!?
Let’s try something simpler: Why is this not working? Copied straight out of Andrew’s guide pp 343,344, only ranges changed.
Dim oRanges(1) As New com.sun.star.table.CellRangeAddress
oRanges(0).Sheet = 0
oRanges(0).StartColumn = 0 : oRanges(0).StartRow = 0 'A1
oRanges(0).EndColumn = 10 : oRanges(0).EndRow =10 'D5
oRanges(1).Sheet = 0
oRanges(1).StartColumn = 0 : oRanges(1).StartRow = 0 'A9
oRanges(1).EndColumn = 10 : oRanges(1).EndRow = 10 'D11
ThisComponent.CurrentController.getActiveSheet().setPrintAreas(oRanges())
ThisComponent.Print(Array())
The loaded file has two sheets, all the data on both sheets is within the ranges 0,0,10,10. No sign of any print area appear on the loaded calc sheet nor the hosting ods. The print function produces 4 printed sheets, all of Sheet 1 only, some blank. Sheets 1 and 3 APPEAR to reflect the print area. Sheets 2 and 4 are blank.
It surely cannot be that arcane?``
10 views and no-one able to offer any assistance - is there a big hole in LO Basic with sheet printing?
The closest I have got is by using the ‘Record Macro’ and using ‘DispatchHelper’
which produces
`document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = “Nr”
args1(0).Value = 2
dispatcher.executeDispatch(document, “.uno:JumpToTable”, “”, 0, args1())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = “ToPoint”
args2(0).Value = “$A$1:$J$14”
dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args2())
dispatcher.executeDispatch(document, “.uno:Print”, “”, 0, Array())`
but still prints a completely blank second sheet for 'NR" /1 or a single for “NR”/2 ???
For anyone else who is struggling to get to grips with some aspects of OO/LO, debugpoint.com has some excellent tutorials which have been a great help.
If any of the 10 ‘viewers’ have found another site where help is available, please share!
Hi, I just saw your post and unfortunately it’s a bit old. (I’m not here so often … ) Hope you have got a solution. If you not have read Pitonyaks Macros explained yet then you have missed something. Very useful. OpenOffice.org Macros Explained. Page 343 is what I think you are/were looking for. Regards.
Here’s the basic solution…but it still requires pressing “Print” for each print dialog that shows up. Automated, but not fully.
Sub PrintLoop
Rem To use, set a Named Range 'AutoPrint' equal to 1 (any value) **local** to
Rem any sheet you want to print from this macro
Dim Sheets As Object
Dim Sheet As Object
Dim RangeNames As Variant
Dim RangeName As String
Sheets = ThisComponent.getSheets()
For Each Sheet in Sheets
RangeNames = Sheet.NamedRanges.ElementNames
For Each RangeName in RangeNames
If RangeName = "AutoPrint" Then
PrintDoc
Exit For
EndIf
Next
Next
End Sub
Sub PrintDoc
Rem From chin/AskLO
Dim Document As object
Dim Dispatcher As Object
Document = ThisComponent.CurrentController.Frame
Dispatcher = createUNOService("com.sun.star.frame.DispatchHelper")
Dispatcher.executeDispatch(Document,".uno:Print", "", 0, Array())
End sub
Sorry for delay both. Had to re-register to reply! Thanks to both - Joshua - will try that.
Here’s a more sophisticated version that lets you print all sheets as a single job. This lets you walk away sooner, and it lets you have sheets print double-sided even if the sides of a page are two different sheets. You might give it a try. The Print part works as expected. The Preview part does not return you to your original location after printing, so, that part isn’t really finished. There may be another whole way of doing things…so stay tuned.
PrintMultipleSheets.ods (16.3 KB)
Thanks to the last two contributors, but not really what I was looking for.
After many hours seeking to de-cypher the ‘secret’ language of OO/LO and the print macro, using APIs and other sources, I thought I had cracked it! However, this (simple) piece I wrote does not work properly and I seek help - please? Is there anywhere a guide to calling print functions in a macro?
Foo.ods is a simple workbook containing two sheets, ‘Sheet1’ and ‘Sheet2’. Unfortunately the code only seems to print the sheet on which the Foo.ods was last saved as far as I can see, so sometimes I get Sheet1 and sometimes Sheet2. Print areas are set on both sheets.
Public Doc2
Public oDoc
Sub Main
Dim Url
Dim arg() As New com.sun.star.beans.PropertyValue
Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
DialogLibraries.loadLibrary("Standard")
Url = convertToUrl("C:\foo.ods")
Doc2 = starDeskTop.loadComponentFromUrl (Url, "__blank", 0, arg())
'oDoc = Doc2.Sheets.getByName("Sheet1") OR
'oDoc = Doc2.Sheets(1)
Printit()
End Sub
sub Printit()
Doc2.print(Array())
end sub
If I replace Doc2 with oDoc in 'Printit() I get 'Property or method not found: ‘print’. So, how do I print a selected sheet in a workbook with a simple piece of LO Basic code?