Close ALL Workbooks and Save Changes WITHOUT asking do I want to save.

[clemsoncooz]

I have a routine that opens excel, does stuff, and then I want to do is close ALL instances of excel down (all workbooks) and SAVE all changes - but I dont want the system to ask me if I want to SAVE.

How do I close and save without the prompt?

The VBA code will reside in ACCESS. So want to close all instances of excel - meaning there is no need to leave the "activework" open.

Since this questions is dealing with excel, I thought I would post my question here.

An example (not exactly) of what I have is below.

Please Login or Register  to view this content.

[patel45]

start macro with

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[HaHoBe]

Hi, clemsoncooz,

this code is unreliable as may be. You want to close all workbooks down you would need to make clear which path and name should be used for all workbooks which arenīt saved in the Excel application. The Code patel45 delivered wonīt be of any use if there is an unsaved workbook which always display the SaveAs display and should be used as xlApp.DisplayAlerts and not Application.DisplayAlerts.

I would use the CreateObject, open the relevant workbook, do my action, save and close the workbook, quit the application and release all objects. And since there was more than one workbook open I had to specify which workbook to use as well as the worksheet on which to perform the action.

I think both posters missed the line

The VBA code will reside in ACCESS.

Ciao,
Holger

[Norie]

Try this.

Please Login or Register  to view this content.

[HaHoBe]

Hi, Norie,

just for me to get it sorted: Application - Workbook - Worksheet - Range?

Please Login or Register  to view this content.

isnīt clear for me at first sight as neither a workbook nor a worksheet is given for the Application (resulting in RTE 1004 for a newly created instance).

Ciao,
Holger

[Norie]

Holger

I kind of ignored those lines, perhaps wrongly, as the question was about closing workbooks.

This isn't quite right but will work - Range("A1") will refer to A1 on the active sheet

Please Login or Register  to view this content.

[HaHoBe]

Hi, Norie,

thanks for coming back on that. I know it sounds a bit out of the box in an Excel Forum but please close down Excel and start the procedure from Access. No workbook is open, the RTE will occur no matter if the lines may work with an open book or not. And I think my comment about not being to stable is not so far out of the world as no workbook is specified (what about a personal macrobook), no worksheet.

I know Iīm pretty oldfashioned but I like to know what I am working on which I canīt guarantee with this kind of code.

Ciao,
Holger

[Norie]

Holger

I'm kind of assumed it's not 'real' code.

It would work if this code was removed, even if no instance of Excel was open.

Please Login or Register  to view this content.

PS When testing the 'code' I was using Access.