Basic help with BASIC please

English
#1

I simply ask for help in understanding LO Basic. I do not wish to explore ‘Monty Python’, or that Indonesian island - just BASIC.

Without studying other peoples’ code snippets it is very difficult to learn the ‘magic’ words etc needed in BASIC to call the various functions in APIs. There is a lack of simple code around for the print spreadsheet macros.

I have taken this macro code from the LO Basic guide. ‘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()
Dim PrintProperties(1) As New com.sun.star.beans.PropertyValue
PrintProperties(0).Name="Pages"
PrintProperties(0).Value="1-2"
PrintProperties(1).Name="Wait"
PrintProperties(1).Value=True
Doc2.print(PrintProperties())
end sub

If I replace Doc2 with oDoc I get 'Property or method not found: ‘print’. So, how do I print a selected sheet/s in a workbook with a simple piece of LO Basic code? Just being able to open the LO ‘Print’ screen would be a start!

#2

Oh no - I don’t think that would be the best start.

Let’s start by clarifying the problem. Print from a spreadsheet everything that is prepared for printing? I mean, in a spreadsheet, some or all of the pages already have a print area defined, right? Or are the print areas not defined and are you going to print everything that is there?
There is a line
Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
in your code. Does this mean that you have already installed this wonderful MRI Extension? Have you already tried using it?

1 Like
#3

@JohnSUN excellent! one of the rare valueable YT-Videos :+1:

1 Like
#4

Yes, indeed. MRI installed and used.

#5

You can use the following macro.
Make sure Menu Tools / Options / LibreOffice Calc / Print “Print only selected sheets” is checked.

Option Explicit

' Print 1st and 2nd sheets of C:\temp\foo.ods.
Sub TestPrint
  Dim oDoc As Object, url As String
  url=convertToUrl("C:\temp\foo.ods") 
  oDoc = StarDeskTop.loadComponentFromUrl (Url, "__blank", 0, Array())  
   
  oDoc.CurrentController.Select oDoc.Sheets.GetByIndex(0)   ' Select 1st sheet 
  Printit(oDoc)                                             ' Print 1st sheet 
  
  oDoc.CurrentController.Select oDoc.Sheets.GetByIndex(1)   ' Select 2nd sheet 
  Printit(oDoc)                                             ' Print 2nd sheet 
End Sub

Sub Printit(ByVal oDoc As Object)
  Dim PrintProperties(0) As New com.sun.star.beans.PropertyValue
  PrintProperties(0).Name="Wait"
  PrintProperties(0).Value=True
  oDoc.print(PrintProperties())
End Sub
#6

sokoi - that works, thank you. But here I flounder again! Why have you used

oDoc.CurrentController.Select oDoc.Sheets.GetByIndex(0) + (1)
when the LO Basic guide says to use  PrintProperties(0).Name="Pages"
PrintProperties(0).Value="1-2"

what is wrong with that solution? Can I use a for/next loop iterating 'sheet n' based on sheetcount rather than the two lines of code??

...and another source I found suggests

oDoc = Doc2.Sheets.getByName(“Sheet1”)

how on earth is the 'beginner' supposed to find out? This is a big mystery which would usefully be sorted. It seems that everyone and his dog has away of doing something and that is not a good learning environment. There is a big dark gap in LO between the API and the 'words' needed to work it. It would be be good to see it filled in.
#7

Property Pages

specifies which pages to print. This range is given as at the user interface. For example: “1-4;10” to print the pages 1 to 4 and 10.

Print pages and spreadsheet sheets are different concepts.

Sure you can.

#8

I did wonder, but could I find anywhere a definition of what a ‘page’ is? Where is it?

#9

Perhaps read this.

#10

sokoi - thanks. I am reasonably happy with Calc operation - it is the macros I am struggling with. I did actually try many ‘alternatives’ to ‘pages’ (including ‘sheets’ in the array, but nothing worked properly. Is it possible to do so, and if so what are the parameters for the number of sheets to replace ```
PrintProperties(0).Name=“Pages”
PrintProperties(0).Value=“1-2”
??

Where would I look to find the necessary words for the codes for use in BASIC with calc?

If I could ask you for more assistance please, can I allocate print area ‘on the fly’ via the parameters? Is is something like PrintProperties(0).Name=“Print Range” or what, and would it be given in the R1C1 format?

#11

OK - while waiting for one of our experts to answer my query I trawled some of Andrew’s old stuff and elsewhere and have put this together. It is by no means , and I’m sure there are poor examples of coding, but it works. I have left ‘PrintArea’ in the array as an option should anyone come up with the answer.

This should give me the option to choose which file I print and the print areas of that file.

REM ***** BASIC *****

OPTION EXPLICIT

Public Doc2
Public oDoc
Public oSheetCount
Public Sht

Sub Main

Dim Url
Dim arg() As New com.sun.star.beans.PropertyValue

Globalscope.BasicLibraries.LoadLibrary( “MRILib” )

DialogLibraries.loadLibrary(“Standard”)
Url = convertToUrl(“C:\temp\foo.ods”)
Doc2 = starDeskTop.loadComponentFromUrl (Url, “__blank”, 0, arg())
oSheetCount = Doc2.Sheets.Count
SetPrintArea(Sht,2,0,9,60)
End Sub

sub Printit(ByVal Sht As Object)
Dim PrintProperties(1) As New com.sun.star.beans.PropertyValue
'PrintProperties(0).Name = “PrintArea”
'PrintProperties(0).Value = “$A$1:$A$2”
PrintProperties(1).Name=“Wait”
PrintProperties(1).Value=True
Sht.print(PrintProperties())
end sub

Sub SetPrintArea (Sht, StC, StR, EndC, EndR)
Dim selArea(0) as new com.sun.star.table.CellRangeAddress
Dim oSheet as object
Dim i

selArea(0).StartColumn = StC
selArea(0).StartRow = StR
selArea(0).EndColumn = EndC
selArea(0).EndRow = EndR

For i = 0 to oSheetCount - 1
oSheet = Sht.Sheets.getByIndex(i)
oSheet.setPrintareas(selArea())
Sht.CurrentController.Select oSheet
Printit(Sht)
Next i

End Sub

#12

A closing note in case this helps anyone else with a similar problem. The routine above is now incorporated in my main programme and works fine. It enables me to set the print area for several different print tasks I have and execute the printing.

I have awarded myself a chocolate nose award :slight_smile:

1 Like

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