Filename Saving Questions

[ummjay]

Hi! I have code which saves a filename based off of a cell value + today's date. How can I adjust it so, if it already exists with value + today's date, it saves as value + date 1, value + date 2, etc? thanks!

'set Range
Dim lr&: lr = ActiveSheet.Cells.Find("*", [A1], _
    xlFormulas, SearchDirection:=xlPrevious).Row
Set WBrng = Range("A1:J" & lr)
WBrng.Copy

Set Wb = Workbooks.Add(1)
    With Wb.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0

    Set Wb = ActiveWorkbook ' or a specific open workbook
    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    sFile = TraderName & Format(Now(), " mmddyyyy") & ".xlsx"
    Wb.SaveAs Filename:=sPath & "\" & sFile

    End With
''end

 

[I_know_nuffin]

replace Wb.SaveAs Filename:=sPath & "\" & sFile

with

you might need to add
Dim sfilename As String, filecount As Integer
to your dim statements

    sfilename = sPath & "\" & sFile
    
    If Dir(filepathname) <> "" Then
        filecount = 1
        sfilename = Replace(sfilename, ".xlsx", "_1.xlsx")
        Do While Dir(filepathname) <> ""
          sfilename = Replace(sfilename, "_" & filecount & ".xlsx", "_" & filecount + 1 & ".xlsx")
          filecount = filecount + 1
        'Debug.Print sfilename
        Loop
    End If
        'Debug.Print sfilename
    wb.SaveAs filename:=sfilename

 

[ummjay]

Thanks, so it works the 1st time, I tried to run it a second time, to see if it would generate a new filename (with 1) after it, and got an error on this:
Wb.SaveAs Filename:=sFilename

 

[I_know_nuffin]

Replace filepathname in the dir statement with sfilename .
Sorry i forgot to update this from my test code

 

[ummjay]

still not working, same thing.

Set Wb = ActiveWorkbook ' or a specific open workbook
    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    sFile = TraderName & Format(Now(), " mmddyyyy") & ".xlsx"
    
    sFilename = sPath & "\" & sFile
    
    If Dir(sPath) <> "" Then
        filecount = 1
        sFilename = Replace(sFilename, ".xlsx", "_1.xlsx")
        Do While Dir(sFilename) <> ""
          sFilename = Replace(sFilename, "_" & filecount & ".xlsx", "_" & filecount + 1 & ".xlsx")
          filecount = filecount + 1
        'Debug.Print sfilename
        Loop
    End If
        'Debug.Print sfilename
    Wb.SaveAs Filename:=sFilename
    
    'Wb.SaveAs Filename:=sPath & "\" & sFile

    End With
''end

 

[I_know_nuffin]

The first dir has spath not sfilename

 

[ummjay]

that worked! project creep here, but i then take that file and attach it to an email via outlook vba, it was working before I added the logic to add _1, _2 etc.. Seems like it doesnt know which to attach. any ideas?

currently i have:
.Attachments.Add sFile

should it be sFilename?

 

[I_know_nuffin]

that would work as that's the latest file name and path

 

[ummjay]

thanks! works perfectly..