'Select' multiple sheets (tabs) via macro

[joshua4]

A printing question popped up again from last year here lately, and it led me to a question. I figured, if you want to print multiple sheets, easiest way is just to simulate ctrl-clicking multiple tabs then use the dispatcher for printing. Done.

But…I’m a little stumped about how to simulate selecting multiple sheets in a macro. Recording doesn’t work, and setting multiple args doesn’t seem to work. I looked into the arguments for uno:jumpToTable, and that didn’t ring any bells for me, either.

It feels like it would come off the Frame/Controller…but I’m missing it.

[Zizi64]

Not need visually select the sheets when you want to print more than one sheet by your macro. (Except when you want to print various User selected sheets.)
You can GET them by name or by index, before you call the Print function.
When you want to print the sheets selected by the User, then you can GET the selection array.

There are sample codes on the OpenOffice forum, but unfortunately that is not available (today, from Hungary)

[sokol92]

You can select sheets in this way (select sheets with index 0, 2, 3):

Sub TestSel
 Dim aSel(0) as new com.sun.star.beans.PropertyValue	
 With createUnoService("com.sun.star.frame.DispatchHelper")  
    aSel(0).Name="Tables"
    aSel(0).Value=Array(0, 2, 3)
    .executeDispatch(ThisComponent.CurrentController.Frame, ".uno:SelectTables", "", 0, aSel)
 End With   
End Sub

Unfortunately, the selection may not appear on the screen. You can make sure that the selection has happened like this: Ctrl+Shift+O (Preview mode), then Esc.

[joshua4]

@Zizi64, thank you. But specifically, the request was based on

1. not having to attend to the print job after pressing the first Print button in the print dialog
2. having sides run together in the print job…so, say, 1-page Sheet1 and a 1-page Sheet3 will print front and back without having to re-queue the job using the OS print utility, etc.

@sokol92 read my mind on this one…yes…having the tabs show is not important, what I was looking for was just the functional selection before calling dispatcher to show the print dialog.

[Lupp]

Regard what @Zizi64 wrote.

However, the subject may attract visitors actually looking for multipüle-sheet-selection by macros independent of printing.

I don’t know how to simulate the “Ctrl+Click” on a sheet’s tab directly, but we can take SheetCellRange addresses from a first sheet, change them to a different sheet, add them to the SheetCellRanges, and finally select what we got.

When I tried this I even was slightly surprised that it worked, and the multi-sheet selection also visualized the result correctly.

A .uno: fopr the purpose (not yet exiosting) would probably need to do it the same way.

See demo code below.

Function expandSheetCellRangesIncludingAdditionalSheet(_
           Optional pRanges As Object, _
           Optional pAdditionalSheetIndex1based As Long) As Object
REM Very raw!! No error catching.
If IsMissing(pAdditionalSheetIndex1based) Then pAdditionalSheetIndex1based = 3 REM Demo!!
index0based = pAdditionalSheetIndex1based - 1
doc = ThisComponent
cCtrl = doc.CurrentController
If IsMissing(pRanges) Then pRanges = cCtrl.Selection
If pRanges.supportsService("com.sun.star.sheet.SheetCellRanges") Then
  myRgs = pRanges
Else
  myRgs = doc.createInstance("com.sun.star.sheet.SheetCellRanges")
  myRgs.addRangeAddress(pRanges.RangeAddress, False)
EndIf
u0 = myRgs.Count - 1
For r = 0 To u0
  r_rg = myRgs(r)
  r_ra = r_rg.RangeAddress
  r_ra.Sheet = index0based
  myRgs.addRangeAddress(r_ra, False)
Next r
expandSheetCellRangesIncludingAdditionalSheet = myRgs
REM For demo only
cCtrl.select(myRgs)
newSel = cCtrl.Selection
End Function

[joshua4]

I’ll look at this…I can see it having some real power to do things like print common subregions across monthly sheets (in fact, I do something pretty close to that…). It looks like @sokol92 hit the nail on the head on how to get UNO to select multiple sheets.