Help with a macro in LO Calc

English
, ,
#8

In this version, the macro works under Ubuntu. Notice the setting of the isMaximized property.

Option Explicit

' For a document with 2 sheets, opens two windows in place of the original. 
Sub Test
  Dim oDoc, oDisp, oWindow1, oWindow2, posSize
  oDoc=ThisComponent
  
  oDisp=createUnoService("com.sun.star.frame.DispatchHelper")
  
  With ThisComponent.CurrentController
    oWindow1=.frame.ContainerWindow
    posSize=oWindow1.getPosSize
    oWindow1.IsMaximized=False
    .SetActiveSheet oDoc.Sheets(0)    
    oDisp.executeDispatch(.frame, ".uno:NewWindow", "", 0, Array())    
  End With  
  
  ' 2-nd Controller
  With ThisComponent.CurrentController
    oWindow2=.frame.ContainerWindow
    oWindow2.IsMaximized=False      
    .SetActiveSheet oDoc.Sheets(1)
  End With  
  
  Wait 200
  ' arrange windows
  With posSize
    .width=Int(.Width / 2 -5)
    oWindow1.setPosSize .X, .Y, .width, .Height, 15  ' halved width of the oWindow1        
    oWindow2.setPosSize .X + .width + 5, .Y, .width, .Height, 15  ' halved width of the oWindow1            
  End With
End Sub
#9

@sokol92: Already better, that is, it works. However, it does not fill the screen completely in height. Values change arbitrarily during assignment of values. I’m working on it…
But thank you very much for your help.

#10

Made changes to the previous macro, let’s check.

#11

Executed the macro. Next, I manually opened the left window (Win+Left Arrow), and it became higher than the right one (see screenshot). Also, when I position the windows manually (Win+Left Arrow/Right Arrow), the windows are glued together and when the width of one is changed (drag the width change handle), the second resizes accordingly. What is the arrangeWindowsVertical() (depricated) method?

Снимок экрана от 2022-01-23 13-06-12
Снимок экрана от 2022-01-23 13-06-121213×199 59.5 KB

#12

Immediately after executing the macro, both windows have the “correct” position and size.
If the original window is maximized, then after the macro is executed, the screen will be completely filled with 2 new windows.
The commands you specify (Win+Left Arrow/Right Arrow) change the size and location of window.

1 Like
#13 
Option Explicit

Sub ArrangeWindowsVertical()
'''	For a document with 2 sheets, open two windows in place of the original.
'''	POSSIZE = 15: Flags the x- and y-coordinate, width and height.

	On Local Error GoTo HandleErrors
	Dim oDoc, oDisp, oWin1, oWin2
	Dim oPosSize As New com.sun.star.awt.Rectangle  'maximized window possize
	Dim nWidthHalved&  'oWin1 width halved

	oDoc = ThisComponent
	oDisp = createUnoService("com.sun.star.frame.DispatchHelper")

Rem	Xray StarDesktop
Rem	Xray ThisComponent.CurrentController.Frame

	' 1st Controller.
	With ThisComponent.CurrentController
		oWin1 = .Frame.ContainerWindow
		With oWin1
			.IsMaximized = True
			Wait 100: oPosSize = .PosSize
			.IsMaximized = False
		End With
		.ActiveSheet = oDoc.Sheets(0)
		oDisp.executeDispatch(.frame, ".uno:NewWindow", "", 0, Array())
	End With

	' 2nd Controller
	With ThisComponent.CurrentController
		oWin2 = .Frame.ContainerWindow
		oWin2.IsMaximized = False
		.ActiveSheet = oDoc.Sheets(1)
	End With

	' Arrange windows.
	With oPosSize
		'.Width = Int(fWidth / 2)
		nWidthHalved = .Width \ 2
		oWin1.setPosSize .X, .Y, nWidthHalved, .Height, 15
		oWin2.setPosSize .X + .Width - nWidthHalved, .Y, nWidthHalved, .Height, 15
	End With

	Exit Sub

HandleErrors:
	Msgbox "Error " & Err & " in line " & Erl & ": " & Error _
	 , MB_ICONEXCLAMATION, "macro:ArrangeWindowsVertical"
End Sub
#14

This is exactly what affects the positioning of windows (shifted to the top or bottom). But the height is incomplete in both cases.

See: A space at the bottom:
Снимок экрана от 2022-01-23 13-59-53

And the windows don’t stick to each other. The system method exists, but how to call it…

However, the resulting solution can be considered quite workable.

#15

@sokol92:
See: .Height * 2
NOTE: Just significantly increased (without calculation, 2 times) the height of the window and filled the entire space in height. Bug, probably…

Sub ArrangeTwoWindowsVertical()
'''	Open two windows of the same document,
'''	displaying the first and second sheets in their windows (left and right).
'''	com.sun.star.awt.PosSize.POSSIZE = 15: Flags the x- and y-coordinate, width and height.

	On Local Error GoTo HandleErrors
	Dim oDoc, oDisp, oWin1, oWin2
	Dim oPosSize As New com.sun.star.awt.Rectangle  'maximized window possize
	Dim nWidthHalved&  'oWin1 width halved

	oDoc = ThisComponent
	oDisp = createUnoService("com.sun.star.frame.DispatchHelper")

	' 1st Controller.
	With ThisComponent.CurrentController
		oWin1 = .Frame.ContainerWindow
		With oWin1
			.IsMaximized = True
			Wait 100: oPosSize = .PosSize: nWidthHalved = oPosSize.Width \ 2
			.IsMaximized = False
		End With
		Wait 100
		With oPosSize
			oWin1.setPosSize .X, .Y, nWidthHalved, .Height * 2, 15
		End With

		.ActiveSheet = oDoc.Sheets(0)
		oDisp.executeDispatch(.frame, ".uno:NewWindow", "", 0, Array())
	End With

	' 2nd Controller
	With ThisComponent.CurrentController
		oWin2 = .Frame.ContainerWindow
		oWin2.IsMaximized = False
Rem		Wait 100
		With oPosSize
			oWin2.setPosSize .X + .Width - nWidthHalved, .Y, nWidthHalved, .Height * 2, 15
		End With

		.ActiveSheet = oDoc.Sheets(1)
	End With
	Exit Sub

HandleErrors:
	Msgbox "Error " & Err & " in line " & Erl & ": " & Error _
	 , MB_ICONEXCLAMATION, "macro:ArrangeWindowsVertical"
End Sub


But the placement of ‘Wait 100’ calls also affects the display result.

#16

In other words: a database. No matter how much Basic code you throw at the “problem” (a misconception actually), you will never find a satisfactory solution. Otherwise we could see any solutions like this. They would be extremely popular. I can not find one.

#20

Two windows side by side for same excel document.

#27

Without any doubt, there are tons of useful Excel features. I do even remember a special document type in MSO 2000, so called “work space documents” which saved the paths, the window sizes, z-order, cursor positions, all the view data of arbitrary MSOffice documents. When you opened such workspace document, you got multiple documents loaded, each with one or more windows at a setup position and size. Hundred spreadsheet features with thousands of lines of VBA will not turn a spreadsheet into a usable database with forms, reports, queries and true tables.

#17

The following macro creates instead of a document window (Calc, Writer, Impress, Draw) two windows of the same width displaying the same document.

Option Explicit

'-----------------------------------------------------------
' Split the document window in two vertically.
' - oDoc document LO (default ThisComponent).
' - sheet Calc Sheet (for Calc document only).
'   The parameter can be a number (sheet index) or text (sheet name). 
Sub DocSplitWindow(Optional ByVal oDoc, Optional ByVal sheet)
  Dim oDisp, oWindow1, oWindow2, posSize, oSheet
  If IsMissing(oDoc) Then oDoc=ThisComponent
  
  oDisp=createUnoService("com.sun.star.frame.DispatchHelper")
  
  With ThisComponent.CurrentController
    oWindow1=.frame.ContainerWindow
    posSize=oWindow1.getPosSize
    oWindow1.IsMaximized=False
    oDisp.executeDispatch(.frame, ".uno:NewWindow", "", 0, Array())    
  End With  
  
  ' 2-nd Controller
  With ThisComponent.CurrentController
    oWindow2=.frame.ContainerWindow
    oWindow2.IsMaximized=False
    If oDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") And (Not IsMissing(sheet)) Then
      oSheet=Nothing
      On Error Resume  Next
      If VarType(sheet)=V_STRING Then
        oSheet=oDoc.Sheets.getByName(sheet)        
      Else
        oSheet=oDoc.Sheets(sheet)
      End If
      If Not (oSheet Is Nothing) Then .SetActiveSheet oSheet
      On Error GoTo 0
    End If  
        
  End With  
  
  Wait 200
  ' arrange windows
  With posSize
    .width=Int(.Width / 2 -5)
    oWindow1.setPosSize .X, .Y, .width, .Height, 15  ' halved width of the oWindow1        
    oWindow2.setPosSize .X + .width + 5, .Y, .width, .Height, 15  ' halved width of the oWindow1            
  End With
End Sub
How to display 2 sections of Writer doc? (Split screen)
2 Pages side by side?
Opening the same file in two windows w/ Command Line Switch
#18

@sokol92, how do I move the focus to the first (left) window after the procedure?
XFrame.activate() does nothing.

#19

The easiest way is to add to the end of the macro

  oWindow1.toFront

(tested for MS Windows).
In general, you need to take into account that after adding a second window, the document already has two controllers (and not one, as usual).

#21

Yes, two controllers. But the toFront method doesn’t work (Ubuntu) because the windows don’t overlap. Focus does not move. Nothing happens.

So, here is the final code. @sokol92, thanks again.

REM  *****  BASIC  *****
Option Explicit


Sub ArrangeTwoWindowsVertical()
'''	Open two windows of the same document,
'''	displaying the first and second sheets in their windows (left and right).
'''
'''	Calls: ActivateCell
'''	Remarks:
'''	 ⁈	• The height of each window was simply increased significantly
'''		(without calculation, by a factor of 2), and only then did they fill
'''		the entire height space. Bug, maybe. See: .Height * 2
'''		• Const: com.sun.star.awt.PosSize.POSSIZE = 15: Flags the x- and y-coordinate, width and height.

	On Local Error GoTo HandleErrors
	Dim oDoc, oDisp, oWin1, oWin2
	Dim oPosSize As New com.sun.star.awt.Rectangle  'maximized window possize
	Dim nWidthHalved&  'oWin1 width halved
	Dim nRemainder%  'remainder after division by 2

	oDoc = ThisComponent
	oDisp = createUnoService("com.sun.star.frame.DispatchHelper")

	' 1st Controller.
	With oDoc.CurrentController
		.ActiveSheet = oDoc.Sheets(0)
		Call ActivateCell(.ActiveSheet.getCellByPosition(0, 1))
		oWin1 = .Frame.ContainerWindow
		With oWin1
			.IsMaximized = True
			Wait 100: oPosSize = .PosSize
			nWidthHalved = oPosSize.Width \ 2: nRemainder = oPosSize.Width - nWidthHalved * 2
			.IsMaximized = False
		End With
		Wait 100
		With oPosSize
Rem			oWin1.setPosSize .X, .Y, nWidthHalved, .Height * 2, 15
			oWin1.setPosSize .X, .Y, nWidthHalved + nRemainder, .Height * 2, 15
		End With

		oDisp.executeDispatch(.frame, ".uno:NewWindow", "", 0, Array())
	End With

	' 2nd Controller
	With oDoc.CurrentController
		oWin2 = .Frame.ContainerWindow
		oWin2.IsMaximized = False
		Wait 100
		With oPosSize
Rem			oWin2.setPosSize .X + nRemainder + nWidthHalved, .Y, nWidthHalved, .Height * 2, 15
			oWin2.setPosSize .X + nWidthHalved, .Y, nWidthHalved + nRemainder, .Height * 2, 15
		End With
		.ActiveSheet = oDoc.Sheets(1)
		Call ActivateCell(.ActiveSheet.getCellByPosition(0, 1))
	End With

	oWin1.toFront
	Exit Sub

HandleErrors:
	Msgbox "Error " & Err & " in line " & Erl & ": " & Error _
	 , MB_ICONEXCLAMATION, "macro:ArrangeWindowsVertical"
End Sub

Sub ActivateCell(oCell As Object)
	Dim oRanges As Object

	With ThisComponent
		.CurrentController.select(oCell)
		' NOTE: Same with .SheetCell or .SheetCellRange doesn’t work.
		oRanges = .createInstance("com.sun.star.sheet.SheetCellRanges")
		' Remove the highlight by passing the empty range collection.
		.CurrentController.select(oRanges)
	End With
End Sub


Well, I’m a perfectionist, so I took into account one pixel lost from division (nRemainder).
My screen width is 1853 = 926 + 1 + 926. OS Linux makes so: 927 + 927, with an overlap of 1 pixel in the middle of the screen. However, the windows are glued (stuck together) there.

#22

Try pressing the up/down arrows right after running the macro. In which window does the active cell change?

#23

Right window

#24

My left window becomes active in Ubuntu. You didn’t forget at the end of the macro:

oWindow1.toFront

?

#25

Sorry. It works. Let’s stop there. Decision received. Thank you.

#26

Thanks for the interesting topic!

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