Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
Try this
Sub Shink() With ActiveWorkbook Application.WindowState = xlMinimized End With End Sub "donwb" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
This worked perfectly for me
Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub "donwb" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
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 "Office_Novice" wrote in message ... This worked perfectly for me Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub "donwb" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
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" wrote: 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 "Office_Novice" wrote in message ... This worked perfectly for me Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub "donwb" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
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" wrote in message ... 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" wrote: 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 "Office_Novice" wrote in message ... This worked perfectly for me Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub "donwb" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
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" wrote: 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" wrote in message ... 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" wrote: 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 "Office_Novice" wrote in message ... This worked perfectly for me Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub "donwb" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "Office_Novice" wrote in message ... This worked perfectly for me Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub "donwb" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "Office_Novice" wrote in message ... This worked perfectly for me Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub "donwb" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimise Workbook
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 "Tom Hutchins" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "Office_Novice" wrote in message ... This worked perfectly for me Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub "donwb" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minimise/Restore | Excel Programming | |||
Minimise worksheets | Excel Programming | |||
Minimise unwanted windows | Excel Programming | |||
Disabling the minimise button in Excel workbook | Excel Programming | |||
Minimise Maximise Problem | Excel Programming |