VBA Code to add to a save as file name

[chrissmarlow]

Hi Guys,
I am using the follwoing code on the end of my macro to save a copy of the spreadsheet with todays date, is there a way of adding a "v1" or "v2" to the filename if there is already a file with that name (if someone has already run the macro on that day) rather than just overwritting it? Thanks for the help

ActiveWorkbook.SaveAs "T:\SupplyChain\Order Book Management\Reports\" & "SRS Sheet - " & Format(Date, "dd-mm-yyyy-") & ".xlsm", FileFormat:=52

 

[gsbelbin]

Hi Chris
Here's some code that demonstrates the principle of how you could do this

Sub test()
    Dim x As String
    Dim i As Integer
 
    x = ""
    i = 0
    x = Dir("c:\test.xls")
    If x <> "" Then
        Do
            i = i + 1
            x = Dir("c:\test v" & i & ".xls")
        Loop While x <> ""
        'end of versioned files found, save with new version number
        ActiveWorkbook.SaveAs "c:\test v" & i & ".xls"
 
    Else
        'file not found so save as base name
        ActiveWorkbook.SaveAs "c:\test.xls"
 
    End If
End Sub

Hope it make sense

Cheers

Gordon

 

[chrissmarlow]

Hi Chris
Here's some code that demonstrates the principle of how you could do this

Sub test()
    Dim x As String
    Dim i As Integer
 
    x = ""
    i = 0
    x = Dir("c:\test.xls")
    If x <> "" Then
        Do
            i = i + 1
            x = Dir("c:\test v" & i & ".xls")
        Loop While x <> ""
        'end of versioned files found, save with new version number
        ActiveWorkbook.SaveAs "c:\test v" & i & ".xls"
 
    Else
        'file not found so save as base name
        ActiveWorkbook.SaveAs "c:\test.xls"
 
    End If
End Sub

Hope it make sense

Cheers

Gordon

Thanks very much Gordon, I will give it a go