Call macro on Exit or Close 2

[zappedagain]

I have a simple macro set up to automatically save a file in CSV format. How do I automatically call this macro when Save, Exit, or Close is selected?

I found the Workbook_beforeClose event but what do I do with it? The following doesn't do anything:


Sub SaveAsCSV()
'
' SaveAsCSV Macro
' Macro to save as CSV on exit.
'

'
ActiveWorkbook.SaveAs Filename:= _
"C:\file.csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Me.Save: SaveAsCSV
End Sub


Where do I need to put the Workbook_BeforeClose part?

Thanks for helping this Visual Basic newbie!

 

[handleman]

Looks like you need to change

If Me.Saved = False Then Me.Save: SaveAsCSV

to

If Not Me.Saved Then call SaveAsCSV

If you want to confirm that the BeforeClose event code is running, drop the line

msgbox "BeforeClose was triggered"

in your Workbook_BeforeClose sub.

If you save your Excel workbook normally before you close then you won't call the CSV save routine because Excel Me.Saved will return true, even though your CSV wasn't created. If you want to call SaveAsCSV every time then just put "call SaveAsCSV" as the only line in Workbook_BeforeClose.

 

[IRstuff]

You need to name your macro "Workbook_BeforeClose(Cancel As Boolean)" which is the default empty macro in your workbook

TTFN

 

[zappedagain]

Thanks, handleman. I didn't realize I needed the 'call' command. I added the message so it looks like the BeforeClose event does not run. Any suggestions?

I see in Excel's VBA help 'Using Events with the Application Object' that it states I need a new class module. I see how to do that. Now I have the following in a Class Module called 'MyEventClassModule':


Public WithEvents App As Application

Dim MyExcelClass As New MyEventClassModule

Sub InitializeApp()
Set MyExcelClass.App = Application
End Sub
Public Sub Class_Initialize()
Call InitializeApp
End Sub

Sub SaveAsCSV()
'
' SaveAsCSV Macro
' Macro to save as CSV on exit.
'

'
ActiveWorkbook.SaveAs Filename:= _
"C:\File.csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub

Public Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "BeforeClose was triggered"
Call SaveAsCSV
End Sub



and it still doesn't work. Am I getting close?

 

[MintJulep]

I played with the class defs too, but couldn't get it to work.

Try searching or asking your question here:

http://www.tek-tips.com/threadminder.cfm?pid=707

It's the computer equivalent of eng-tips.

 

[handleman]

I think I figured it out. Any of the Workbook_ event routines have to be stored on the Workbook. If they're stored elsewhere, such as in a Module, they won't be triggered.

On the left-hand side of the screen in the VBA editor you have the project explorer that shows Microsoft Excel Objects in one folder (Sheet1, Sheet1, Sheet3, ThisWorkbook), and I'm guessing in your case also a "Modules" folder containing "Module1". That gets created automatically when you create a macro by recording, which is the easiest way to start. If you double-click on "ThisWorkbook" you will get another code window where all your Workbook_ event procedures go.

So basically the final question in your first post was totally the correct question and totally unanswered by my reply. Put the Workbook_BeforeClose part in the ThisWorkbook section of the Project Explorer.

Sorry for the roundabout!

 

[zappedagain]

Yes!!!! That works. Thank you.