VBA - add number at the end if file exists

[Yulyo]

Hello all,

Short question from me if you have a few seconds:
- I have the following VBA that saves the active sheet in a folder on the desktop

 With Application.FileDialog(msoFileDialogSaveAs)
        .InitialFileName = Path & "\" & Format(Range("K2"), "YYMMDD") & "_" & Range("L12") & "_(" & NM & " )" & "_" & Range("P3") & ".xlsx"
        .Show
        .Execute
 End With

- the problem that I am facing is that if I try to save the file again (it might happened that the file saved by other colleagues have the same name), it tells me that the file already exists. I am trying to change this code and make it add a "-1", "-2"... at the end, if file exist.
So if the initial name of the file is test.xlsx and I try to save the file again with the same name, the new file should be named test-1.xlsx, if I save it again the name should be named test-2.xlsx and so on.

Thank you

 

[Jeffrey Mahoney]

Here is the code I use to check for existing files with the same name. You could change the name of the variable RptPath

RptPath = RptPath & " " & Format(Now(), "YYYYMMDD") & "_"
  For X = 1 To 20
    S = RptPath & X & ".xlsx"
    A = Dir(S)
    If A = "" Then
      RptPath = S
      Exit For
    End If
  Next X

 

[DanteAmor]

Hi @Yulyo. Thanks for posting on the forum.

Try the following. Fit the missing data in the nm and sPath variables

Sub savesheet()
  Dim sPath As String, nm As String, prefix As String, sExt As String, NewName As String
  Dim n As Long
  
  nm = "test"                 'Here the value of the variable nm
  sPath = "c:\desktop"        'Here your intial path
  
  prefix = sPath & "\" & Format(Range("K2"), "YYMMDD") & "_" & Range("L12") & "_(" & nm & " )" & "_" & Range("P3")
  sExt = ".xlsx"
  NewName = prefix & sExt
  Do While True
    If Dir(NewName) = "" Then Exit Do
    n = n + 1
    NewName = prefix & "-" & n & sExt
  Loop
  
  With Application.FileDialog(msoFileDialogSaveAs)
    .InitialFileName = NewName
    .Show
    .Execute
  End With
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Yulyo]

Thank you both for your answers.
@ DanteAmor, your solution is working just perfect. Thanks.