PDA

View Full Version : Error if file exists



Knet59
02-01-2008, 03:47 AM
With the code below i will get a VB error with yellow highligt on "ActiveWorkbook.SaveAs Filename:=file_name" when the file exists and i pick No or Cancel to replace it.
What is wrong or missing?

Private Sub CmdSpara_Click()

' Save as
Dim file_name As Variant
Dim FName As String
FName = Sheets("Sheet 1").Range("A1").Value & ".xls"
' Get the file name.
file_name = Application.GetSaveAsFilename(FName, _
FileFilter:="Excel Files,*.xls,All Files,*.*", _
Title:="Save As File Name")
' See if the user canceled.
If file_name = False Then Exit Sub
' Save the file with the new name.
If LCase$(Right$(file_name, 4)) <> ".xls" Then
file_name = file_name & ".xls"
End If
ActiveWorkbook.SaveAs Filename:=file_name
End Sub

xld
02-01-2008, 03:57 AM
Try this



Private Sub CmdSpara_Click()
' Save as
Dim file_name As Variant
Dim FName As String
FName = Sheets("Sheet 1").Range("A1").Value & ".xls"
' Get the file name.
file_name = Application.GetSaveAsFilename(FName, _
FileFilter:="Excel Files,*.xls,All Files,*.*", _
Title:="Save As File Name")
' See if the user canceled.
If file_name = False Then Exit Sub
' Save the file with the new name.
If LCase$(Right$(file_name, 4)) <> ".xls" Then
file_name = file_name & ".xls"
End If
If Dir(file_name) <> "" Then
If MsgBox(file_name & " already exists, replace it (Y/N)", vbYesNo) = vbNo Then
Exit Sub
End If
End If
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=file_name
Application.DisplayAlerts = True
End Sub

Knet59
02-01-2008, 04:38 AM
Wonderful! Now it works perfect.

Thanks for your fast answer!