Have Macro open another Excel file, in a second instance, and in a Minimized state - not Hidden

DougRobertson

Active Member
Joined
Sep 22, 2009
Messages
320
Office Version
  1. 365
Platform
  1. Windows
Hello,

WINDOWS 10 / OFFICE365

I have Macros that will open various other Excel files in other instances (panes) and interact with them.

Is there a way to have the new Excel file open in a Minimized state? I don't want it Hidden, just Minimized.


VBA Code:
Workbooks.Open Filename:="C:\TEST.xlsx"


Many thanks in advance,

~ DWR
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,175
Office Version
  1. 2016
Platform
  1. Windows
Try this

VBA Code:
Workbooks.Open Filename:="C:\TEST.xlsx"
ActiveWindow.WindowState = xlMinimized
 

DougRobertson

Active Member
Joined
Sep 22, 2009
Messages
320
Office Version
  1. 365
Platform
  1. Windows
Thanks Z-man!

That makes sense, but is it possible to join the two lines, so that when the new Excel file opens, it will open in a Minimized state simultaneously? I'm thinking that without that, it could cause a quick flash on the screen as it goes from Normal to Minimize.

Appreciate your help!
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,175
Office Version
  1. 2016
Platform
  1. Windows
Thanks Z-man!

That makes sense, but is it possible to join the two lines, so that when the new Excel file opens, it will open in a Minimized state simultaneously? I'm thinking that without that, it could cause a quick flash on the screen as it goes from Normal to Minimize.

Appreciate your help!
To suppress flash, add

Application.ScreenUpdating = False

your code here

Application.ScreenUpdating = True
 

DougRobertson

Active Member
Joined
Sep 22, 2009
Messages
320
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That used to work like a charm when Excel only operated out of 1 instance. But since I think it was the 2007 version, which then and now use multiple instances, it doesn't seem to work at all. Did I miss a memo along the way somewhere?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,175
Office Version
  1. 2016
Platform
  1. Windows
That used to work like a charm when Excel only operated out of 1 instance. But since I think it was the 2007 version, which then and now use multiple instances, it doesn't seem to work at all. Did I miss a memo along the way somewhere?
That is right. Same case like UserForm as well. Each workbook is now independent or so called SDI (single-document interface) vs older version MDI (multiple-document interface). You can find code to add to keep UserForm, regardless from any workbook to stay on top. If not it get hidden by new workbooks.

I'm not sure for individual workbook though. Maybe @Fluff or @Peter_SSs has idea how to overcome this.
 
Solution

DougRobertson

Active Member
Joined
Sep 22, 2009
Messages
320
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Okay, we are making progress using the code:

VBA Code:
ActiveWindow.WindowState = xlMinimized

But I usually like to be specific when working with the windows, and try to avoid using the ActiveWindow method. Using code something like this:

VBA Code:
iTeam = "VANCOUVER"
VBA Code:
iMAINMACROWorkbookName= iTeam & "  -  BCR  &  INVOICE"
VBA Code:
Windows(iMAINMACROWorkbookName).WindowState = xlMinimized

Sometimes the code works perfectly, but other times I get the Error Code: Run-time error '9': Subscript out of range

Any ideas what I'm doing wrong?

Thanks
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,175
Office Version
  1. 2016
Platform
  1. Windows
Okay, we are making progress using the code:

VBA Code:
ActiveWindow.WindowState = xlMinimized

But I usually like to be specific when working with the windows, and try to avoid using the ActiveWindow method. Using code something like this:

VBA Code:
iTeam = "VANCOUVER"
VBA Code:
iMAINMACROWorkbookName= iTeam & "  -  BCR  &  INVOICE"
VBA Code:
Windows(iMAINMACROWorkbookName).WindowState = xlMinimized

Sometimes the code works perfectly, but other times I get the Error Code: Run-time error '9': Subscript out of range

Any ideas what I'm doing wrong?

Thanks

I guess it is for the same reason why ActiveSheet can be tricky. This most likely to happen when you are running code across workbooks or even even multiple worksheet. When you tried to call a sheet or an object that does not exist, you normally get this error.

Well, sometimes certain code need to use ActiveSheet I believe or it will not work. It is always good practice to be specific like you said :) . Maybe other have better reasoning. I'm just learning VBA from Internet by Googling 😁
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows
Maybe something like this:
VBA Code:
Dim oXL  As Application

Set oXL = New Excel.Application

oXL.WindowState = xlMinimized

oXL.Visible = True

oXL.Workbooks.Add
 
  • Like
Reactions: Zot

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,175
Office Version
  1. 2016
Platform
  1. Windows
I found another way to be specific on workbook

Dim wb as Workbook
Dim wbName as Window

Set wb = ActiveWorkbook
Set wbName = Windows(<workbook name>)
or
Set wbName = Windows(wb.Name)

wb.ScreenUpdating=False
wbName.WindowState = xlMinimized

.... your code .....

wb.ScreenUpdating=True

I have not tried if there is difference with or without ScreenUpdating.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,126
Messages
5,640,274
Members
417,133
Latest member
caaronh85

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
Top