VBA error handling - File already exists in location

[willow1985]

Below is part of a code that saves an excel document to a certain directory under a specified name and format. However sometimes a user will create the file manually instead of using the macro but will try and use the macro later causing the below error: file named _____ already exists (see below picture).

Is there a way to save a copy of the document to the same directory without overriding and instead of the below message box, have a new message box that says "file already exists and a copy of the document will be saved". And then save a copy to the same directory without overriding.

Currently if the user says no to replacing it, they get a Run-time error 1004 Method SaveAs of object workbook failed and if they say yes to replacing it, it overrides the original document (which we also do not want either).

Thank you to anyone who can help!

Dim path As String
Dim filename As String
path = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("I5")
filename = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("G1")


ActiveWorkbook.SaveAs filename:=path & filename & ".xlsx", FileFormat:=51
  Range("A1").Select

Kill Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("I7")
Windows("Customer Concern - Warranty Request Log.xlsm").Activate
Sheets("List").Select

Set Target = Range("G16")
If Target.Value = "AeroDept" Then
Call RR_Warranty
Call Warranty_Email
End If

Windows("Customer Concern - Warranty Request Log.xlsm").Activate

Worksheets("List").Visible = False
Sheets("CC Database").Select

   Case vbNo
        GoTo Quit:
    End Select

Quit:
End Sub

Private Sub Transfer_CC()
Dim fso
Dim Source_File As String
Dim Source_Folder As String
Dim Destination_Folder As String

Source_File = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("M1").Value & ".xlsx"
Source_Folder = Workbooks("Customer Concern - Warranty Request Log").Sheets("List").Range("I8").Value
Destination_Folder = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("I5")
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile (Source_Folder & Source_File), Destination_Folder, True

 

[rlv01]

You basically need to avoid the system messages and code it all yourself.
An example (not tested).

    Dim path As String
    Dim filename As String
    Dim FileCopyName As String, TimeStamp As String
    Dim FSO As Object
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    path = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("I5")
    filename = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("G1")
    
    If FSO.FileExists(path & filename & ".xlsx") Then
        MsgBox "File already exists and a copy of the document will be saved", vbOKOnly Or vbInformation, Application.Name
        TimeStamp = Format(Date, "yyyymmdd") & Format(Time, "hhmm")
        FileCopyName = filename & "_Copy_" & TimeStamp
        ActiveWorkbook.SaveAs filename:=path & FileCopyName & ".xlsx", FileFormat:=51
    Else
        ActiveWorkbook.SaveAs filename:=path & filename & ".xlsx", FileFormat:=51
    End If

 

[willow1985]

You basically need to avoid the system messages and code it all yourself.
An example (not tested).

    Dim path As String
    Dim filename As String
    Dim FileCopyName As String, TimeStamp As String
    Dim FSO As Object
  
    Set FSO = CreateObject("Scripting.FileSystemObject")
  
    path = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("I5")
    filename = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("G1")
  
    If FSO.FileExists(path & filename & ".xlsx") Then
        MsgBox "File already exists and a copy of the document will be saved", vbOKOnly Or vbInformation, Application.Name
        TimeStamp = Format(Date, "yyyymmdd") & Format(Time, "hhmm")
        FileCopyName = filename & "_Copy_" & TimeStamp
        ActiveWorkbook.SaveAs filename:=path & FileCopyName & ".xlsx", FileFormat:=51
    Else
        ActiveWorkbook.SaveAs filename:=path & filename & ".xlsx", FileFormat:=51
    End If

at Timestamp = Format

I am getting the below error

 

[MARK858]

I don't get that error with that line, did you copy/paste the code (I isolated the line to test)?
If I put a message box after the line I get

 

[willow1985]

I don't get that error with that line, did you copy/paste the code (I isolated the line to test)?
If I put a message box after the line I get
View attachment 111050

See for yourself:

 

[MARK858]

Do you have another macro called Format?

 

[rlv01]

Like @MARK858 said, you should not be getting an error like that. I did test that part before I posted. Do you perhaps have another function you have named "Format"? Does it work any better if you use this instead?

TimeStamp = VBA.Format(Date, "yyyymmdd") & VBA.Format(Time, "hhmm")

 

[willow1985]

See for yourself:

View attachment 111051

I just changed it to TimeStamp = Date and that worked just fine.

 

[willow1985]

You basically need to avoid the system messages and code it all yourself.
An example (not tested).

    Dim path As String
    Dim filename As String
    Dim FileCopyName As String, TimeStamp As String
    Dim FSO As Object
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
   
    path = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("I5")
    filename = Workbooks("Customer Concern - Warranty Request Log.xlsm").Sheets("List").Range("G1")
   
    If FSO.FileExists(path & filename & ".xlsx") Then
        MsgBox "File already exists and a copy of the document will be saved", vbOKOnly Or vbInformation, Application.Name
        TimeStamp = Format(Date, "yyyymmdd") & Format(Time, "hhmm")
        FileCopyName = filename & "_Copy_" & TimeStamp
        ActiveWorkbook.SaveAs filename:=path & FileCopyName & ".xlsx", FileFormat:=51
    Else
        ActiveWorkbook.SaveAs filename:=path & filename & ".xlsx", FileFormat:=51
    End If

Thank you very much rlv01. After one modification this worked great

 

[NateSC]

Too little too late, but here is another solution using DIR. I used it to add a " -2" to the end of a file if it already existed. (It goes -3, -4, and so on until it finds a file name not already used.) I am only posting as an option.

    n = 2
    While Not Dir(path & filename & ".xlsx") = vbNullString
        filename2 = filename & " -" & n 
        MsgBox "The file [" & filename & ".xlsx] already exists." & Chr(13) & "This report will be saved under [" & filename2 & ".xlsx]", vbOKOnly, "File already exists"
        filename = filename2
        n = n + 1
    Wend