Minimise Workbook

[donwb]

I have 2 workbooks open in Excel.
I am trying to write code to minimise the active workbook
to the taskbar, then, when it gets there, save any changes made.
I'm using:-
Application.Workbooks(MyName).WindowsState = xlMinimised
Workbooks(MyName).Close Savechanges:=True
The first line does not work, but the second one does.
Is this just syntax, or am I trying the impossible?
donwb

 

[Office_Novice]

Try this

Sub Shink()
With ActiveWorkbook
Application.WindowState = xlMinimized
End With
End Sub

 

[Office_Novice]

This worked perfectly for me

Sub Shrink()
With ActiveWorkbook
Application.WindowState = xlMinimized
End With
Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook"
End Sub

 

[donwb]

Hi Office_Novice
The problem with
"Application.WindowState = xlMinimized"
is that it minimises both workbooks.

Say my WorkBook #1 is active & visible
and Workbook #2 is inactive, not visible.

As soon as I run my code, I would like it to
straightaway make WB #2 visible & active then
save and close WB #1 in the "background"
so that I can look at WB #1 while that's going on.
donwb

 

[Tom Hutchins]

Instead of Application, use the Window object. Maybe something like

Public Sub Shrink()
'Activate the workbook to be minimized
Workbooks("Book4").Activate
'Windows(1) ia always the active window
Windows(1).WindowState = xlMinimized
Workbooks("Book4").Close SaveChanges:=True
End Sub

Hope this helps,

Hutch

 

[donwb]

Hi Hutch
I tried your suggestion as below:-

Windows(1).WindowState = xlMinimized
Workbooks(MyName).Close SaveChanges:=True

When I ran the code it appeared to ignore the minimise statement,
the active window remained visible and the close/save statement was
executed.
The minimise was actually done, because when I reopen the WB it appears
minimised.

If I step thru the code however, the active window IS minimised,
and the "inactive" window becomes visible, while the close/save is done.
Any ideas??
donwb

 

[Tom Hutchins]

Do I understand correctly what you want to do? You have two workbooks open.
You designate one of them as MyName. MyName is minimized, saved, and closed.
The other workbook is left open as the active workbook. As far as I can tell,
the following code accomplishes all that. It doesn't matter which of the two
workbooks has the macro or which workbook is active when you run it. You just
have to assign the name of the workbook (as it appears in the title bar) to
the string variable MyName.

Public Sub Shrink()
'Activate the workbook to be minimized
Workbooks(MyName).Activate
'Windows(1) ia always the active window
Windows(1).WindowState = xlMinimized
'Maximize the active window
ActiveWindow.WindowState = xlMaximized
'Save & close MyName
Workbooks(MyName).Close SaveChanges:=True
End Sub

I'm not sure why you want to go to the extra trouble of miinimizing a
workbook, if you are going to immediately close it anyway.

Hutch

 

[donwb]

Hi Hutch

Many thanks for the response.
Yes, your understanding is correct.
I have two WBs open.
Both contain much data.
I use the 2 in conjunction, switching between them.
I reach a point when I need to save & close one.
Because the time it takes to save (either) is considerable (10 - 15
seconds),
I would like it to do it's saving "in the background", or "off screen",
and while that's going, look at the other (active) WB.

I agree your suggested should do what is required,
but it still seems to ignore the minimise statement until the save is
finished.
I'm still playing with it.
donwb

 

[Tom Hutchins]

I don't think I tested with a large enough file, so I made a big workbook.
When I run the macro, it seems to minimize the intended workbook, but then
un-minimize it while it saves it. In any case, I don't think you can save a
workbook "in the background" while you work with another workbook in the same
instance of Excel. Maybe it could work if the workbooks were opened in
separate sessions (instances) of Excel, but then it would be harder to
manipulate the workbook in the other session programmatically.

Hutch

 

[donwb]

Hi Hutch
Yes. I did find the same. The un-minimising while the save occurs.
That lead me to your conclusion - I can't do what I'm trying to do in the
same instance of Excel.
As someone who posts answers in this Forum once said,
"Sometimes the dragon wins"
Thanks for your help.
donwb