I need to open a new window programmatically. Then I have to select sheet 1 in the first window, and sheet 2 in the second window, and place window 1 on the left half of the screen and window 2 on the right half of the screen, i.e. next to each other.
Explanation: The data are entered in window 1 and the results are immediately displayed in window 2 as I enter the data.
In my opinion it is not possible in the LO Calc.
The Window handling method of the LibreOffice is basicly different than the Window handling of the Excel (MSO).
You can split one sheet of a Calc spreadsheet document, or you can open a copy of the original document in an another window, but the two document will not be “synchronized” automatically. If you modify the one, it will not appeared in the another.
You can use a link to external data (link or reference to a data located in an another FILE, but it is not same what you want to achieve.
@Zizi64 It seems to be about two sheets of the same spreadsheet. Perhaps the first sheet is an input form, and the second sheet is linked to the first sheet by formulas. In this case, it is really possible to “synchronize” the display in two windows of the same document. (The solution at the link does not align the windows on the screen, we still have to think about this)
Exactly. It is long and inconvenient to set it up manually before starting work every time.
Synchronization and alignment is not necessary. The second sheet only displays the grand total.
The “.uno:NewWindow” command adds another document controller.
You can get the enumeration of document controllers using the getControllers method.
For each controller, you can set the active sheet of the document and get the object .Frame.ContainerWindow.
It remains only to arrange the windows on the screen.
Example.
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
.SetActiveSheet oDoc.Sheets(0)
oDisp.executeDispatch(.frame, ".uno:NewWindow", "", 0, Array())
End With
' 2-nd Controller
With ThisComponent.CurrentController
oWindow2=.frame.ContainerWindow
.SetActiveSheet oDoc.Sheets(1)
End With
' arrange windows
posSize=oWindow1.getPosSize
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
Thank you. But the windows are not located side by side (however, an attempt is made, blinking). I need time to figure it out.
Edit: Window shrinks, blinks and expands to full screen (Ubuntu 18.04).
Perhaps, this (arrangeWindowsVertical). I don’t know how to apply.
I have refined and shortened the text of the macro. In MS Windows the macro works correctly.
Ubuntu seems to have a problem with the setPosSize method - it doesn’t work if the window is maximized. See also here and tdf#120620.
Version: 7.2.5.2 / LibreOffice Community
Build ID: 20(Build:2)
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.2.5~rc2-0ubuntu0.20.04.1~lo1
Calc: threadedIn 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
@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.
Made changes to the previous macro, let’s check.
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?
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
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
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:
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.
@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.
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.
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.
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
@sokol92, how do I move the focus to the first (left) window after the procedure?
XFrame.activate() does nothing.