Pre Populate saveas dialogue

[fialko]

I use a spreadsheet at work that autosaves a copy of a spreadsheet using certain cells and current date to a default location.

There is an issue where for some unknown reason the saved file occasionally saves to some random folder working back down the network folder path or the users My Documents.

To get around this i would like the save as options to show when user presses save button with relevant name format already populated (just so they can see if it saving in right location)

Example

In the worksheet Sheet1 the cells B2, B3 & B5 and todays date would be the save name e.g. B2 = John B3 = Smith (b5 = BS then todays date in format dd/mm/yy)

Could anyone help please?





Thanks

 

[John_w]

Try this. Put the code in the ThisWorkbook module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Static saving As Boolean
    Dim saveAsFileName As Variant
 
    If saving Then Exit Sub
 
    saveAsFileName = Sheets("Sheet1").Range("B2").Value & " " & Sheets("Sheet1").Range("B3").Value & " " & Sheets("Sheet1").Range("B5").Value & " " & Format(Now, "mm-dd-yyyy")
    saveAsFileName = Application.GetSaveAsFilename(InitialFileName:=saveAsFileName, FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")
    If saveAsFileName <> False Then
        saving = True
        ActiveWorkbook.SaveAs fileName:=saveAsFileName
        saving = False
    End If
    Cancel = True

End Sub

PS - the slash is an invalid character in a file name so I've used hyphens in the date.

 

[fialko]

Hi John

Thank you so much. Works perfectly and will solve the problem of losing random files

Much appreciated

Barry