In Before Close Sub ActiveWorkBook.Close(False) repeat procedure

[pat12]

Hi everyone


I tried to write code which should do actions like:
1. If user try to close file its should ask if the file should be
saved.
- If NO file should be closed immediately without any prompts (like "Do
You want to save the file")
- If YES it should be saved and after saved again but as csv file.

My problem is that to avoid "Do You want to save the file" which appear
automatically when file is being closed with some changes done I use
ActiveWorkBook.Close(False). But if I use it with procedure it does
something like restart of the procedure (I tried to put this code in
different places and its always the same)

I looks lik this. Procedure is asking "Save the file?"
User choose "NO" and the procedure asks again "Save the file?" After
repeating this question it goes on but I don't know why it repeats it

Below is the code. If it can be done more cleary show me how please.

Complete another question Is there a way to avoid questions:
"Do you want to overwrite the 1.csv file"
and
"Do you want to save the changes in csv file"

I tried without success.

Thanks
PAT

Procedure:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo ErrorHandler
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, NoMsg, NoStyle,
NoTitle, NoResponse, ErrMsg, ErrStyle, ErrTitle, ErrResponse,
SavResponse, SavMsg

SavMsg = "Do you want to save file?"

Msg = "Do you want to save file as csv?"
Style = vbYesNo + vbDefaultButton1
Title = "Saving csv"

NoMsg = "Data is not saved , YES - Return, NO - Close file"
NoStyle = vbYesNo + vbCritical + vbDefaultButton1
NoTitle = "Saving"

ErrMsg = "Error apeared. Probably you doesn't confirm saving csv file.
You will return to the file"
ErrStyle = vbYes + vbCritical
ErrTitle = "ERROR"



SavResponse = MsgBox(SavMsg, Style)
If SavResponse = vbNo Then
ActiveWorkbook.Close (False)
Exit Sub
Else
ActiveWorkbook.Save
End If

Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\1.csv", FileFormat:= _
xlCSV, CreateBackup:=False

Else

NoResponse = MsgBox(NoMsg, NoStyle, NoTitle)
If NoResponse = vbYes Then
Cancel = True

End If
End If

Exit Sub
ErrorHandler:
ErrResonse = MsgBox(ErrMsg, ErrStyle, ErrTitle)
Cancel = True



End Sub

 

[Tom Ogilvy]

If you do SaveAs with the same name, you will be prompted to overwrite the
existing file. You can do

application.DisplayAlerts = False
' do the save as
application.DisplayAlerts = True

If the user doesn't want to save, then

ThisWorkbook.Saved = True
exit sub

rather than closing it, which should kick off the close event again.

 

[pat12]

Tom Ogilvy napisal(a):

If the user doesn't want to save, then

ThisWorkbook.Saved = True
exit sub

That is what I needed Thanks a lot.

If you do SaveAs with the same name, you will be prompted to overwrite the
existing file. You can do

application.DisplayAlerts = False
' do the save as
application.DisplayAlerts = True

Here It is more complicated.
I tried your line and it worked with overwrite question but there are
two more questions which I can not control and the results are
different.

These question are:
1. Do you want to save the changes in csv file?
2. (sorry for translation) 1.csv can have some function which are not
ok with csv format.Do you want to save file in this format.

The problem is that if user choose yes or no answer for question1 the
file 1.csv is saved but with different separator. If he choose YES it
is semicolon, If he choose NO it is comma. and it is not the same for
link in access.

Is there a way to choose Yes for this question with the procedure?

Thanks
PAT

 

[pat12]

Here It is more complicated.
I tried your line and it worked with overwrite question but there are
two more questions which I can not control and the results are
different.

These question are:
1. Do you want to save the changes in csv file?
2. (sorry for translation) 1.csv can have some function which are not
ok with csv format.Do you want to save file in this format.

The problem is that if user choose yes or no answer for question1 the
file 1.csv is saved but with different separator. If he choose YES it
is semicolon, If he choose NO it is comma. and it is not the same for
link in access.

Is there a way to choose Yes for this question with the procedure?

Thanks
PAT

OK I think I foung the solution. I put your
ThisWorkbook.Saved = True
just after the
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\1.csv", FileFormat:= _
xlCSV, CreateBackup:=False


and now it doesn't ask me anything (except my questions of course) but
now save the csv file with commas as separators.


Please confim me is it the proper and stable solution.

Thanks Again
PAT

 

[pat12]

OK I think I foung the solution. I put your
ThisWorkbook.Saved = True
just after the
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\1.csv", FileFormat:= _
xlCSV, CreateBackup:=False


and now it doesn't ask me anything (except my questions of course) but
now save the csv file with commas as separators.


Please confim me is it the proper and stable solution.

Thanks Again
PAT

This is last question I promise
Speaking about
ActiveWorkbook.SaveAs Filename:= _

"C:\Documents and Settings\1.csv", FileFormat:= _
xlCSV, CreateBackup:=False

Is there any way to replace C:\Documents and Settings\1.csv" with the
same folder as 1.xls (the origunal excel file)

PAT

 

[Tom Ogilvy]

Activeworkbook.Path & "\"