"Clean" close of an Excel Workbook. 2

[WaterSprite]

I have a workbook that opens at 12:50 am daily by use of Task Scheduler. When it opens, I have it do a full calculation of the active sheet, autofit some columns and then save. This is all done via a Workbook "Open" macro.

When this workbook opens, there are already 4 or 5 workbooks open on this computer. They are open for various reasons, either providing information, or recording info, but they stay up 24-7.

Now my problem. I need a "Clean" close of my workbook at some point after it opens, say 12:55 am. The easiest way would be via "Application.Quit", but then I get into the whole thing of having to reopen the other workbooks, they do not belong to me, ETC, you get the picture.

What I have tried so far is: Did another spreadsheet, Named it "Close.xls" and saved it to a different folder,

Recorded the following macro for WorkBook.Open:
ChDir "C:\"
Workbooks("ChemicalBalanceSheet.xls").Activate
Workbooks("ChemicalBalanceSheet.xls").Close
ThisWorkbook.Close

That seems to work sort of, but the results are very ugly.
The pre-existing workbooks are halfway minimized, there is still a screen that says "ChemicalBalanceSheet.xls", although there is nothing there.

I have finally resorted to, for now, just have the on duty lead person go to File, then close. That works, but any "Automation" of trying to close it leaves an ugly mess on the screen.

 

[taupirho]

You need to open up the ChemicalBalanceSheet in a completely separate excel instance. That way an application.quit will NOT affect your ather open workbooks

 

[Loomah]

If you know how long it will take to perform the required tasks you could try using the OnTime method in your open macro. Having said that, why wouldn't using thisworkbook.close work after it's done its required calculating and formatting?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​