Solved: Before Close

[sukumar.vb]

I created a VBA program, which :-

(1) Alerted me through Msgbox --- Do you want to create back up?

(2) If, I clicked yes, it saved current sheet as CSV file in C drive.

(3) If I clicked no, it saved current sheet in its whatever current drive.

There was a problem.

On first instance of running of this program, File was saved in C drive, if I selected Yes.

On Second instance, , it asked me to overwrite CSV file. I would want to ignore that alert which asked me to overwrite CSV file.

It should save CSV file by overwriting it, but without alerting user. How can this be done?

[austenr]

I would comment....never mind.

[GTO]

PLease test in a junk copy of your wb.

In ThisWorkbook Module:
[VBA]Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wb As Workbook

If Not bolInProcess Then
'// Cancel the Close, set a Public flag and kill alerts in case a pre-existing //
'// wb/csv //
Cancel = True
bolInProcess = True
Application.DisplayAlerts = False
If Not ThisWorkbook.Saved Then
Select Case MsgBox(Me.Name & " is not saved." & vbCrLf & _
"Would you like to Save a backup and the file, save just the workbook," & vbCrLf & _
"or close without saving?" & vbCrLf & vbCrLf & _
"Select <Yes> to save a backup copy and the workbook, <No> to" & vbCrLf & _
"save just the workbook, or <Cancel> to close w/o saving.", _
vbYesNoCancel Or vbInformation, _
vbNullString)

Case vbYes
'// Save a copy to disk and open the copy. //
Me.SaveCopyAs Me.Path & "\New Folder\Temp.xls"
Set wb = Workbooks.Open(Me.Path & "\New Folder\Temp.xls")
'// Run procedure in copy, in order to set flag there. //
Application.Run wb.Name & "!Module1.Defeat"
'// Save the copy as a .csv and close it. //
wb.SaveAs Me.Path & "\New Folder\MyCSV.csv", xlCSV
wb.Close False
DoEvents
'// Kill the temp copy; save and close this wb. //
Kill Me.Path & "\New Folder\Temp.xls"
ThisWorkbook.Close True
Case vbNo
ThisWorkbook.Close True
Case vbCancel
ThisWorkbook.Close False
End Select
Else
If MsgBox("Would you like to save a backup copy?", vbYesNo Or vbQuestion, vbNullString) = vbYes Then

Me.SaveCopyAs Me.Path & "\New Folder\Temp.xls"
Set wb = Workbooks.Open(Me.Path & "\New Folder\Temp.xls")
Application.Run wb.Name & "!Module1.Defeat"
wb.SaveAs Me.Path & "\New Folder\MyCSV.csv", xlCSV
wb.Close False
DoEvents
Kill Me.Path & "\New Folder\Temp.xls"
ThisWorkbook.Close False
Else
ThisWorkbook.Close False
End If
End If
Application.DisplayAlerts = True
bolInProcess = False
End If
End Sub[/VBA]

In a Standard Module: (named Module1)
[VBA]Option Explicit

Public bolInProcess As Boolean

Sub Defeat()
bolInProcess = True
End Sub[/VBA]

Hope that helps,

Mark

[sukumar.vb]

I would comment....never mind.

Looking to learn programming Without being SMART.
To improve this forum, you can ask "members" to Quote version of Excel used as following: -

Last used Excel Version: 2002

[sukumar.vb]

I wanted to thank for your help.
Was this program written in specific VBA for Excel 2002?
Request you to please define "Me" in program below.

PLease test in a junk copy of your wb.

In ThisWorkbook Module:
[VBA]Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wb As Workbook

...............

Hope that helps,

Mark

[sukumar.vb]

Awaiting correct responses ...

[GTO]

Sukumar.vb,

Reference your last:

You are welcome.

If you are asking whether I wrote in a later year's version, presuming the post date/time is accurate, I believe I was writing in 2010. That said, I do not see anything unavailable in 2000 or later.

Reference "Me":

Workbook_BeforeClose is in the ThisWorkbook module, which is a Class/Object module. 'Me' refers to the Object; in this case, the workbook Object. Quite frankly, I should have used 'ThisWorkbook' for clarity's sakes. However, it is the same thing in this case. Here is a simple test:
[vba]
Sub exa1()
MsgBox Me.Name
End Sub
[/vba]

In a blank/new wb, place the above in a worksheet's module and run it. Then place the code in ThisWorkbook module and run it there. You will see that it returns the name of the sheet in the first instance, and the name of the workbook in the latter.

Hope that helps,

Mark

[sukumar.vb]

Same code were inserted into a workbook and it performed well.

[GTO]

If solved, you can mark the thread as such, under Thread Tools, which is atop your first post.

[sukumar.vb]

Hi GTO,

Could you please help me in other questions. Your help is required.