VBA: Multiple monitors , Opening a workbook on a specific monitor using VBA

dougsdir24

New Member
Joined
May 27, 2019
Messages
2
Using Excel 2016 32 bit on WIndows 7 or Windows 10, I have a an Excel application that runs on 4 monitors. In Windows they are numbered:

[1] [2]
[3] [4]

I have a a master workbook (say that opens on Monitor 1) that I would like to open 3 other workbooks on monitors 2, 3 & 4 using VBA

So either using the Workbooks.Open method and moving the Workbook to the correct monitor number or opening the Workbook directly on a monitor's number

Is there a way to accomplish this in VBA?

Thanks
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi

I know that you are looking to do this using VBA, but I have a similar setup and use Python to open workbooks, "take control" of them and move them to a specific screen or location.

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]# Python 3.x
# OpenWorkbook
# Opens and manipulates Excel spread sheets
# Requires following libraries installed:
# win32gui, win32con, win32api
# Authored by ATL-IT 04/05/2019
# example:
# "C:\Users\xxx\AppData\Local\Programs\Python\Python37-32\python.exe" OpenWorkbook.py -no -f "C:\Users\xxx\Documents\book1.xlsx" "C:\Users\xxx\Documents\book2.xlsx"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]import win32gui, win32con, win32api, sys
from win32com.client import Dispatch, DispatchEx # Dispatch opens in existing instance if available, DispatchEx opens in new instance[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]# Constants
display_count = len(win32api.EnumDisplayMonitors()) # Counts number of displays
title_endings = [' - Saved', ' - Excel'] # Window titles can end in either depending on if saved or not[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]# variables
instance_number = 0 # for giving focus to just one window if many opened[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]# Set all switches to false
new_instance_all = False
new_instance_once = False
bring_to_front = False
open_only = False
pos = ''[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]print('Constants:\n\tDisplays: ' + str(display_count) + '\nSwitches:')
workbooks = [][/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]# Run through all arguments
for argv in sys.argv[1:]:
 if argv[0] == '-':
  # switches
  if argv == '-na': # Create a new instance for each workbook
   new_instance_all = True
   print('\tNew instance all')
  elif argv == '-no' or argv == '-n': # Create a one new instance for all workbooks
   new_instance_once = True
   print('\tNew instance once')
  elif argv == '-f': # Give focus to first workbook opened
   bring_to_front = True
   print('\tBring to front')
  elif argv == '-o': # Only open workbooks without resizing or moving
   open_only = True
   print('\tOpen only')
  elif argv[:2] == '-p': # Custom position for all workbooks (eg. -p0,0,1920,1080) - could probably do with different positions for each workbook if required.
   if argv[2:].count(',') == 3:
    pos = argv[2:]
   else:
    print('\tWarning: Invalid use of -p switch')
  else:
   print('\tUnknown switch ' + argv)
 else: # No preceding '-' so not switch and presumed to be a workbook
  workbooks.append(argv)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]# Run through all workbooks to be opened
for workbook in workbooks:
 
 print('Workbook: ' + workbook)
 # Get the window name for workbook
 for title_ending in title_endings:
  workbook_name = workbook.split('\\')[-1] + title_ending
  print('\tLooking for and attempting to grab workbook window name: ' + workbook_name)
  hwnd = win32gui.FindWindow(None, workbook_name)
  if hwnd != 0:
   print('\t\t"' + workbook_name + '" found and grabbed.')
   break
  else:
   print('\t\t"' + workbook_name + '" not found.')[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] # check if already open, open if not
 if hwnd == 0:
  print('\t"' + workbook_name + '" not found so opening...', end=(''))
  if new_instance_all or (new_instance_once and instance_number == 0):
   xl = DispatchEx('Excel.Application')
   instance_number += 1
  else:
   xl = Dispatch('Excel.Application')
  xl.Visible = 1
  try:
   xl.Workbooks.Open(workbook)
  except Exception as e:
   print('FAILED! ' + str(e))
   sys.exit(0)
  else:
   print('Successful.')
  
  # If not open only switch, attempt to grab window to resize/move
  if not open_only:
   print('\tAttempting to grab window "' + workbook_name + '"...', end=(''))
   hwnd = win32gui.FindWindow(None, workbook_name)
   if hwnd == 0:
    print('FAILED!')
    sys.exit(0)
   else:
    print('Successful.')[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] if not open_only:
  if pos == '':
   # use display count to determine what to do with the window
   if display_count == 3:
    # 3 display_count, move to top right leaving gap for Outlook
    print('\tMoving and resizing window...', end=(''))
    try:
     win32gui.MoveWindow(hwnd, 1191, -1080, 1689, 1042, True)
    except Exception as e:
     print('FAILED! ' + str(e))
    else:
     print('Successful.')
   elif display_count == 4:
    # if 4 display_count, move to top centre diplay and maximise
    print('\tMoving and maximizing window...', end=(''))
    try:
     win32gui.MoveWindow(hwnd, 0, -1080, 1920, 1042, True)
     win32gui.ShowWindow(hwnd, win32con.SW_MAXIMIZE)
    except Exception as e:
     print('FAILED! ' + str(e))
    else:
     print('Successful.')
   else:
    # for all other display counts, just maximize
    print('\tMaximizing window...', end=(''))
    try:
     win32gui.ShowWindow(hwnd, win32con.SW_MAXIMIZE)
    except Exception as e:
     print('FAILED! ' + str(e))
  else:
   pos_x, pos_y, pos_w, pos_h = pos.split(',')
   print('\tMoving window...', end=(''))
   try:
    win32gui.MoveWindow(hwnd, int(pos_x), int(pos_y), int(pos_w), int(pos_h), True)
    if pos_x == '0':
     win32gui.ShowWindow(hwnd, win32con.SW_MAXIMIZE)
   except Exception as e:
    print('FAILED! ' + workbook + '\n' + str(e))
   else:
    print('Successful.')
    
 if instance_number <= 1 and (bring_to_front or open_only):
  print('\tBringing window to front...', end=(''))
  try:
   win32gui.SetForegroundWindow(hwnd)
  except Exception as e:
   print('Error bring window to front (' + workbook_name + ')' + '\n\t\t\t' + str(e))
  else:
   print('Successful.')[/FONT]

It is some code that I quickly put together, then tried to tidy it up and make it better. I know it is far from perfect, but it does what I need it to so I am happy with it for now. You would need to install Python for Windows (32-bit is better for opening and manipulating 32-bit applications) and install the Python [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]win32gui, win32con & win32api libraries[/FONT]. There is code that you probably don't need, so just modify it to suit. As the code is just now, you would be better running the program once for each workbook if using the -p position argument to place the window where you want it.
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top