VBA code to save gives error 1004???

[Richard m]

I have the following code that creates a folder then saves th
spreadsheet with the same name that is in A1. The problem I have is ho
to handle the error message when I save the same file again and selec
the NO option (Yes to replace works fine)and get the error code 100
that wants me to end or debug. Here is the code: The case statemen
did not work. Can I have the saveas dialog box appear on error 1004?

error is at the Activeworkbook..........


Code
-------------------

Sub Macro2()
On Error GoTo Errhandler
' Macro2 Macro
' Macro recorded 11/24/2003 by na
'
Dim ans As String
Dim ans2 As String
Dim number
ans = "C:\" & Range("playsave").Value
ans2 = Range("playsave").Value & ".xls"
MkDir ans 'makes new folder
saveitnow:

ChDir ans 'sets path to new folder

ActiveWorkbook.SaveAs Filename:=ans & "\" & ans2, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Exit Sub
Errhandler:
Select Case Err.number ' Evaluate error number.
Case 1004 ' "File already open" error.
MsgBox "OK"
Resume saveitnow
Case Else

GoTo saveitnow 'takes care of error from when folder already
End Select

exists.



End Sub

 

[D.S.]

Try adding these lines to your code


Application.DisplayAlerts = False 'turns off error checking

ActiveWorkbook.SaveAs Filename:=ans & "\" & ans2, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = False 'turns error checking back on


D.S.



I have the following code that creates a folder then saves the
spreadsheet with the same name that is in A1. The problem I have is how
to handle the error message when I save the same file again and select
the NO option (Yes to replace works fine)and get the error code 1004
that wants me to end or debug. Here is the code: The case statement
did not work. Can I have the saveas dialog box appear on error 1004?

error is at the Activeworkbook..........


Code:
--------------------

Sub Macro2()
On Error GoTo Errhandler
' Macro2 Macro
' Macro recorded 11/24/2003 by na
'
Dim ans As String
Dim ans2 As String
Dim number
ans = "C:\" & Range("playsave").Value
ans2 = Range("playsave").Value & ".xls"
MkDir ans 'makes new folder
saveitnow:

ChDir ans 'sets path to new folder

ActiveWorkbook.SaveAs Filename:=ans & "\" & ans2, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Exit Sub
Errhandler:
Select Case Err.number ' Evaluate error number.
Case 1004 ' "File already open" error.
MsgBox "OK"
Resume saveitnow
Case Else

GoTo saveitnow 'takes care of error from when folder already
End Select

exists.



End Sub


--------------------