VBA Save as help

[Vladgs]

Hi everyone,
I need some help to achieve a task, I’ve tried getting code and adapting it but unfortunately couldn’t.

What I want to do is the following:

1 Check to see if file name already exists in a specific folder.

2 Filename is not the “Activewirkbook” but instead something like: Range("D2") & " " & Range("C3") & ".xlsx", FileFormat:=51 So I need to check if that file name exists.

3 If that file name exists add something like “V2”,”V3” etc

4 Hide all dialog boxes and warning from user.
I think this can be achieved by “Application.DisplayAlerts = False”

Looking forward to any help!

Regards,

 

[GTO]

Is D2 the Path and C3 the proposed filename (minus extension)?

Mark

 

[Vladgs]

Hi Mark,

No, the file name is composed by a concatenation between D2 and C3 and "xlsx", the file path is fixed, say "C\finance\Financial Operations\Special Access\PO model\".

Regards,

 

[GTO]

Okay, then try like:

Option Explicit
  
Public Sub Example()
Dim FSO     As Object ' Scripting.FileSystemObject
Dim fsoFile As Object ' Scripting.File
  
Dim sFixedPath  As String
Dim sFileName   As String
Dim n           As Long
  
  '// Alter to suit ///
  sFixedPath = ThisWorkbook.Path & "\"
  Set FSO = CreateObject("Scripting.FileSystemObject")
  
  '// Ensure folder exists  //
  If FSO.FolderExists(sFixedPath) Then
    
    Application.DisplayAlerts = False
    
    '// Leave the extension off of the filename //
    sFileName = Trim$(Sheet2.Range("D2").Value) & Trim$(Sheet2.Range("C3").Value)
    
    '// If no "version" is needed...      //
    If Not FSO.FileExists(sFixedPath & sFileName & ".xlsx") Then
      ThisWorkbook.SaveAs sFixedPath & sFileName, 51
    Else 'Else add "_ver" to the filename and...  //
      sFileName = sFileName & "_ver"
      n = 1
      '// ...loop until we don't find the proposed filename.  //
      Do While FSO.FileExists(sFixedPath & sFileName & Format(n, "000") & ".xlsx")
        n = n + 1
      Loop
      ThisWorkbook.SaveAs sFixedPath & sFileName & Format(n, "000"), 51
    End If
    
    '// Turn alerts back on as soon as possible //
    Application.DisplayAlerts = True
    
  End If
  
End Sub

Please note that I qualified the .Range with the worksheet's CodeName which is safer that using the name on the tab.

Hope that helps,

Mark

 

[Vladgs]

Thank you Mark,

That is brilliant!

And simple enough for a rookie to play with.

 

[GTO]

You are very welcome and thank you for the kind feedback.