Macro to Save As

[JustinN1]

I need a macro that will save a workbook that will be updated daily as the original name and just add the current date to it. I need it saved to the location the original workbook is located. I dont want the save as dialog box to open or the user to have any option to change the name or location it will be saved to unless there was already a file saved for that day...and in that case I just want them to be able to answer if they want to replace the exsiting file for that day. I would like a message box telling the user that the file has been saved and show the location of the file.

Any help would be greatly appreciated.

 

[Jerry Sullivan]

Hi Justin,

This isn't difficult, but some additional information will be helpful and impact the code:

1. What will be the file format of the file saved by the user (.xls, .xlsx, or .xlsm)?
2. Will the macro be saved in and run from the file being saved, or some other file like Personal.xlsb?
3. What date format do you want? IE: MyFile - MM-DD-YYYY.xls
4. Will the macro just add the date, or will it have to remove a previous date? For example if the original starts as C:\MyPath\MyFile.xls and the macro saves it as C:\MyPath\MyFile 04-15-2011.xls. On the next day, will the user be working with C:\MyPath\MyFile 04-15-2011.xls and the macro will need to remove the previous date and save as C:\MyPath\MyFile 04-16-2011.xls instead of just adding a date?

 

[JustinN1]

The file will be saved as .xls

The macro will be saved in the workbook and will run using a command button.

The format I need it saved as is myfile041411

The file will start new every day. So it just needs the date added to myfile

If u need any further info let me know. Thank you!

 

[AlphaFrog]

Sub Save_MyFile()

    Dim strMyFile
    
    strMyFile = "MyFile" & Format(Date, "mmddyy") & ".xls"
    
    If Dir(ThisWorkbook.Path & "\" & strMyFile) = vbNullString Then
    
        ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & strMyFile
        MsgBox "File saved as """ & strMyFile & """", vbInformation, "Save Complete"
    Else
    
        If MsgBox("Today's file """ & strMyFile & """ alrady exists." & vbLf & _
                  "Do you want to overwrite it with a new save?", vbExclamation + vbOKCancel, _
                  "Today's File Already Saved") = vbOK Then
                  
            ThisWorkbook.Save
            MsgBox "File saved.", vbInformation, "Save Complete"
        End If
    End If
        
End Sub