VBA Excel opens Word - Split Screen to Windows Planes

[noveske]

Currently Excel VBA creates and opens Word.
On Word open, Excel is sized to the left half of the screen and Word is sized to the right half of the screen.
It sizes the windows to the full resolution of the desktop. 1920x1080.
This causes the 2 windows to be cut off at the bottom as they are hidden under the task bar. Can't see the tabs.

I'd like to get it to where it will fit the windows like the split screen feature built into Windows.
Excel on the left plane.
Word on the right plane.
Then have both windows be active on top if there are other windows open.
Tried a bunch of different active commands and it hasn't worked.

I tried resizing the actual window size and failed.
My other attempt placed the windows on top and covered the task bar. Still couldn't see the taskbar.
I believe I did get it to work once, it was a longer code than just:
wordDoc.Activate

Was wordDoc.Activate then maybe WordApplication xl and maybe a 1 in there.
But I didn't save it and lost it. I changed it again and tinkered with it because I didn't like how it would place a thick white border around both windows.

Is there a way to do this and something more efficient? Been slowly throwing this one together and figuring things out.

Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hWnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal uFlags As Long) As Long

Sub CreateAndOpenWordDocument()
    On Error Resume Next
    Dim wordApp As Object
    Set wordApp = GetObject(, "Word.Application")
    
    ' If Word application is not open, create a new instance
    If wordApp Is Nothing Then
        Set wordApp = CreateObject("Word.Application")
    End If
    
    On Error GoTo 0
    
    Dim wordDoc As Object
    Dim filePath As String
    filePath = ThisWorkbook.Path & "\103.docx"
    
    ' Check if the Word document file exists
    If Dir(filePath) = "" Then
        Set wordDoc = wordApp.Documents.Add
        
        ' Save the Word document at the specified location
        wordDoc.SaveAs2 filePath
    Else
        ' Open the existing Word document
        Set wordDoc = wordApp.Documents.Open(filePath)
    End If
    
    ' Make sure the Word document is activated
    wordDoc.Activate
    wordApp.Visible = True
    
    ' Initialize Word event handler
    Dim WordEventObj As New WordEventHandler
    Set WordEventObj.wordApp = wordApp
        
    ' Get Excel window handle
    Dim excelHwnd As LongPtr
    excelHwnd = FindWindowA("XLMAIN", vbNullString)
    
    ' Get Word window handle
    Dim wordHwnd As LongPtr
    wordHwnd = FindWindowA("OpusApp", vbNullString)
    
    ' Set window positions and sizes for split view
    If excelHwnd <> 0 And wordHwnd <> 0 Then
        Dim screenWidth As Long, screenHeight As Long
        screenWidth = 1920 ' Width of the screen
        screenHeight = 1080 ' Height of the screen
        
        ' Excel window on the left, taking up the specified width and full height
        SetWindowPos excelHwnd, 0, 0, 0, 960, screenHeight, &H4
        
        ' Word window on the right, taking up the specified width and full height
        SetWindowPos wordHwnd, 0, 960, 0, 960, screenHeight, &H4
    End If
    
    ' Loop to keep the script running until Word is closed
    Do
        DoEvents
    Loop While WordEventObj.wordApp.Documents.Count > 0
    
    ' Run CopyTextFile immediately after Word is closed
    Application.Run "CopyTextFile"
    
    ' Clean up
    Set WordEventObj.wordApp = Nothing ' Disconnect the event handler first
    Set wordDoc = Nothing
    Set wordApp = Nothing
End Sub

 

[NdNoviceHlp]

Hi noveske. I messed around with your code and this seems to work unless I'm missing something...

' Excel window on the left, taking up the specified width and full height
        SetWindowPos excelHwnd, 0, 0, 0, 960, screenHeight - 20, &H4
        ' Word window on the right, taking up the specified width and full height
        SetWindowPos wordHwnd, 0, 960, 0, 960, screenHeight - 20, &H4

I also removed all the WordEventObj code as it wouldn't compile (probably needs a Word reference). The code seemed to work although I have no "CopyTextFile" code to trial it with. HTH. Dave

 

[noveske]

Hi noveske. I messed around with your code and this seems to work unless I'm missing something...

' Excel window on the left, taking up the specified width and full height
        SetWindowPos excelHwnd, 0, 0, 0, 960, screenHeight - 20, &H4
        ' Word window on the right, taking up the specified width and full height
        SetWindowPos wordHwnd, 0, 960, 0, 960, screenHeight - 20, &H4

I also removed all the WordEventObj code as it wouldn't compile (probably needs a Word reference). The code seemed to work although I have no "CopyTextFile" code to trial it with. HTH. Dave

It works. But not completely how I wanted.

I was able to get it done.

Locked Excel and Word where I wanted them. The script above left a small gap between the in the middle like diastema. While blocking off the sheet tabs at the bottom. It would do the whole screen and the task bar would block it. The Word window would sometimes be buried.
Wrote a script to tell me the window resolution size and set point of the windows.
Adjusted size and placement within the script.
Removed 40px or whatever off height.
Then changed the type or whatever from &H4 to &H40.

Took me a bit to realize not all 1920x1080 screen resolutions behave the same.
These were the resolutions and set points to mimic Windows Split Screen function on the left and right plane.

At 0, it showed a thin desktop border around the windows. Just confused and bothered me.
Home monitor:

    If excelHwnd <> 0 And wordHwnd <> 0 Then
        ' Resize and reposition Excel window
        SetWindowPos excelHwnd, 0, -7, 0, 974, 1039, &H4
       
        ' Resize and reposition Word window
        SetWindowPos wordHwnd, 0, 953, 0, 974, 1039, &H4
    End If

When I checked on a work monitor the sizing was off. Windows were larger so they overlapped.
So adjusted. Seems some monitors are more true to size?

    If excelHwnd <> 0 And wordHwnd <> 0 Then
        ' Resize and reposition Excel window
        SetWindowPos excelHwnd, 0, 0, 0, 960, 1040, &H40

        ' Resize and reposition Word window
        SetWindowPos wordHwnd, 0, 960, 0, 960, 1040, &H40
    End If

Solution was to get the resolutions. Manually set them. Changed from &H4 to &H40.
Was hoping for something that would utilize the Windows Split Screen so it would be universal.
It'll function, but dependant on monitors, it would overlap or gap between the 2 windows.

 

[NdNoviceHlp]

Glad you got it sorted out. Perhaps the DPI/ zoom / scale settings are different on the different pcs? Thanks for posting your outcome. Dave

 

[noveske]

Glad you got it sorted out. Perhaps the DPI/ zoom / scale settings are different on the different pcs? Thanks for posting your outcome. Dave

I thought that too. But when I checked they were both the same. I think it's manufacturer differences and the monitor size.
Home is a curved Asus 32" or something.
The two at work were a HP 24" and Dell 23.11" or whatever. Seemed to do what I wanted on both.


I'm guessing that perhaps the curved monitor has offset pixel placement due to the curvature or maybe instead of actual 1920x1080, they add a few pixels to account for the curvature. That's the only thing I can think of. Has to be.

 

[NdNoviceHlp]

Thanks for the further info. Dave