Call macro on Exit or Close
Call macro on Exit or Close
(OP)
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!
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!





RE: Call macro on Exit or Close
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.
RE: Call macro on Exit or Close
TTFN
RE: Call macro on Exit or Close
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?
RE: Call macro on Exit or Close
Try searching or asking your question here:
http://www.tek-tips.com/threadminder.cfm?pid=707
It's the computer equivalent of eng-tips.
RE: Call macro on Exit or Close
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!
RE: Call macro on Exit or Close