How to close the active workbook in Excel 2011?

[audax]

I'm using VBA in Excel 2011 on a Mac in macOS 10.13.4 (High Sierra). I can't find the right code to close the file in which the macro resides - I keep getting an error message, RunTime error 1004 based on the close command. I've tried:

ActiveWorkbook.Close
Workbook.Close
ThisWorkbook.Close

and they all fail. What should I be doing?

[6StringJazzer]

Hmm. The first and third should work, but I have never done this on a Mac. Could you attach the file so we can see if it works in Windows?

Alternatively, try the attached. This works for me. If it causes an error for you then it may be that the Mac version will not allow you to close a file with a running macro.

[DonDiego]

You are leaving out an argument. You have to specifiy whether or not you want the book to Save
So it should be
ActiveWorkbook.Close False ' if you want to close without saving and

ActiveWorkbook.Close True 'in the other case.

[audax]

I assume I should click in the 'Close Me' box. Doing so just put handles on the box.

'Application.Quit' works but it's not the solution I'd prefer.

[audax]

I tried the 'False' argument on each option - no difference.

[TMS]

@DonDiego: strictly speaking, the argument is optional.

See: https://docs.microsoft.com/en-us/off...workbook.close

Syntax
expression. Close( _SaveChanges_ , _Filename_ , _RouteWorkbook_ )
expression A variable that represents a Workbook object.

Parameters

Name Required/Optional Data type Description
SaveChanges Optional Variant If there are no changes to the workbook, this argument is ignored. If there are changes to the workbook and the workbook appears in other open windows, this argument is ignored. If there are changes to the workbook but the workbook doesn't appear in any other open windows, this argument specifies whether changes should be saved. If set to True , changes are saved to the workbook. If there is not yet a file name associated with the workbook, then FileName is used. If Filename is omitted, the user is asked to supply a file name.

Filename Optional Variant Save changes under this file name.

[rorya]

Is there any code in the workbook_beforeclose event in the ThisWorkbook module?

[audax]

Yes:

Please Login or Register  to view this content.

[6StringJazzer]

You are leaving out an argument. You have to specifiy whether or not you want the book to Save

@DonDiego: strictly speaking, the

I believe TMS intended to say that strictly speaking the argument is optional.

[6StringJazzer]

Moderator's note: audax, please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you don't have many posts, even though you have been a member for 9 years. --6StringJazzer

[6StringJazzer]

Is there any code in the workbook_beforeclose event in the ThisWorkbook module?

Yes:

audax, the question was specifically about the workbook_beforeclose event, which is not shown in your code. Do you have the following in the ThisWorkbook module:

Please Login or Register  to view this content.

[TMS]

@6SJ: exactly so Thank you for the spot.

[audax]

audax, the question was specifically about the workbook_beforeclose event, which is not shown in your code. Do you have the following in the ThisWorkbook module:

Please Login or Register  to view this content.

No - I have never previously encountered the 'Workbook_BeforeClose' structure. Where should it be located and what does it do?

[audax]

Moderator's note: audax, please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you don't have many posts, even though you have been a member for 9 years. --6StringJazzer

My humblest apologies - I hadn't realised they were obligatory. And, although I've been a member for nine years, I've written no VBA code in that time which is why I'm now exhibiting great confusion and asking very naive questions.

[6StringJazzer]

No - I have never previously encountered the 'Workbook_BeforeClose' structure. Where should it be located and what does it do?

If you don't already have it, you don't need it. It's code that executes just before the file closes. If you did have it, it might mean that the error was happening in there someplace.

Did you try running my sample file? Click the button and the file will close. If that works with no error, then we can dig farther into your code.

Is the sub you showed us running on the workbook that contains it, or some other workbook? That is, when this code runs what is ActiveWorkbook?

[audax]

All solved thank you. One of the purposes of the exercise was to use a .xlsm file to open a new .xlxs file, import a text file and do a bit of fiddling with it. I discovered that I can close the .xlsm file if I have another file open so all is well.

It appears certain to me that it is impossible to close the last file standing in VBA on a Mac running High Sierra.

Mark this issue as solved.

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.