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

[DougRobertson]

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.

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

Many thanks in advance,

~ DWR

 

[Zot]

Try this

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

 

[DougRobertson]

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]

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]

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]

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.

 

[DougRobertson]

Okay, we are making progress using the 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:

iTeam = "VANCOUVER"

iMAINMACROWorkbookName= iTeam & "  -  BCR  &  INVOICE"

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]

Okay, we are making progress using the 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:

iTeam = "VANCOUVER"

iMAINMACROWorkbookName= iTeam & "  -  BCR  &  INVOICE"

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]

Maybe something like this:

Dim oXL  As Application

Set oXL = New Excel.Application

oXL.WindowState = xlMinimized

oXL.Visible = True

oXL.Workbooks.Add

 

[Zot]

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.