 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2008Minimise 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 Try this Sub Shink() With ActiveWorkbook Application.WindowState = xlMinimized End With End Sub > I have 2 workbooks open in Excel. > I am trying to write code to minimise the active workbook [quoted text clipped - 5 lines] > Is this just syntax, or am I trying the impossible? > donwb This worked perfectly for me Sub Shrink() With ActiveWorkbook Application.WindowState = xlMinimized End With Workbooks("Book1").Close SaveChanges:=True, Filename:="MyWorkbook" End Sub > I have 2 workbooks open in Excel. > I am trying to write code to minimise the active workbook [quoted text clipped - 5 lines] > Is this just syntax, or am I trying the impossible? > 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 > This worked perfectly for me > [quoted text clipped - 14 lines] >> Is this just syntax, or am I trying the impossible? >> donwb 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 > Hi Office_Novice > The problem with [quoted text clipped - 28 lines] > >> Is this just syntax, or am I trying the impossible? > >> 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 > Instead of Application, use the Window object. Maybe something like > [quoted text clipped - 42 lines] >> >> Is this just syntax, or am I trying the impossible? >> >> donwb 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 > Hi Hutch > I tried your suggestion as below:- [quoted text clipped - 59 lines] > >> >> Is this just syntax, or am I trying the impossible? > >> >> 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 > Do I understand correctly what you want to do? You have two workbooks > open. [quoted text clipped - 89 lines] >> >> >> Is this just syntax, or am I trying the impossible? >> >> >> donwb 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 > Hi Hutch > [quoted text clipped - 108 lines] > >> >> >> Is this just syntax, or am I trying the impossible? > >> >> >> 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 >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 [quoted text clipped - 127 lines] >> >> >> >> Is this just syntax, or am I trying the impossible? >> >> >> >> donwb |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.