Workbook Close - For EXPERTS Only!

[Michael Pettinicchi]

I say for "For EXPERTS Only" because to-date, nobody has been able to figure this one out and those who tried have given up and never replied to my replies to their replies.

I have the following code in the "Workbook" of "ThisWorkbook". It works if I open the file directly, i.e., it does the job and closes. But, if I open the file via another Excel file, it does the job but doesn't close. What's wrong?

Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled
Range("a5").Select
Application.CommandBars("Worksheet Menu Bar").Enabled = True
ThisWorkbook.Close
End Sub

Thanks
Michael

[memicol]

Not tested, but try ActiveWorkbook.Close

[Tim Francis-Wright]

Curiouser and curiouser...

I could duplicate the problem not only when VBA opened the file from another workbook, but also whenever another workbook was open.

Stepping through the macro triggered the closing of the workbook. Simply running it did not. (This isn't the first time I've
seen Workbook_Open generate frustration.)

I do have a workaround for you:
Put the code that you want into a normal
module, under Auto_Open():

Private Sub Auto_Open()
Application.EnableCancelKey = xlDisabled
Range("a5").Select
Application.CommandBars("Worksheet Menu Bar").Enabled = True
ThisWorkbook.Close
End Sub

[memicol]

But will Auto_Open work when the file is opened by a macro in another workbook ?

[Tim Francis-Wright]

Use the following syntax:
Workbooks.Open "ANALYSIS.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen

[klb]

Yes Auto_open will work even when the workbook is opened by a macro in another file. Anyway mine do.



[ This Message was edited by: klb on 2002-04-22 07:31 ]

[Mark O'Brien]

OK, I ran your code and the first time I opened the workbook, the macro warning did not show. I didn't change the code, but I did change your code into a subroutine in a module. I then called that subroutine from the Open event and it seemed to work.

Here's what I did:

1. Insert a new module.
2. Stick this code in it:

Public Sub CloseBook()



  Application.EnableCancelKey = xlDisabled

  Range("a5").Select

  Application.CommandBars("Worksheet Menu Bar").Enabled = True

  ThisWorkbook.Close



End Sub

3. In "ThisWorkook" object, stick in this code:

Private Sub Workbook_Open()

  CloseBook

End Sub 

This doesn't really answer your question, but I don't know why having just the code in the Open event didn't trigger the macro warning.

PS:: I just tested your code by opening the workbook from another workbook. It works fine. I've not been able to duplicate my error of not getting the macro warning to come up when the code is in the Workbook_Open module. What is your code for opening this workbook from another workbook? This might be the real culprit and not this section of code. It could also explain why no one has been able to solve your problem.

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-04-22 07:45 ]

[Mark O'Brien]

Sorry Tim, I just read your post (again) and I realised I pretty much just duplicated what you said. I must not have read it properly the first time.

[Tim Francis-Wright]

I think I know (part of) the problem.

Using
Public Sub Workbook_Open,
I got the close command to fire when opening the file from the File menu, even with another workbook open. I still can't get the close command to fire if I open the
workbook within VBA.

A workaround for that behavior might be to remove the Close command from the Workbook_Open
code and put it elsewhere. For example, if
MySub in some other workbook is the sub that
opens up this ephemeral workbook, then that
code could do the closing, too.

When there is another file open,

[Michael Pettinicchi]

Ok you guys. You've got me.

I've tried everything you said (I think) and it DOES NOT WORK!

Here is exactly what I've got.

In WK1.XLS, Sheet 1, I have a button which when clicked executes the following from the Sheet 1 VBA code which is:

Private Sub CommandButton1_Click()
Workbooks.Open "WK2.XLS"
ActiveWorkbook.RunAutoMacros xlAutoOpen
End Sub

In WKB.XLS, Sheet 1 VBA code, I have:

Public Sub CloseBook()
Application.EnableCancelKey = xlDisabled
Range("a5").Select
Application.CommandBars("Worksheet Menu Bar").Enabled = True
ThisWorkbook.Close
End Sub

In WKB.XLS, ThisWorkbook VBA code, I have:

Private Sub Workbook_Open()
CloseBook
End Sub

I believe this is exactly as Mark said and all I get when executing WKB gets opened is a compile error message "Sub or Function not defined."

I tried Tim's way, but it doesn't work. I don't get an error message but WK2.XLS wont close automatically. What I might be missing is this "Auto" business. Am I supposed to place my sub in a special place. I tried it in Sheet 1 VBA and in ThisWorkbook VBA pages.

Best regards from
EXCELently Frustrated Michael