Create macro "save as" with filename + date
Solved
isakissss
Posted messages
2
Status
Membre
-
Vikled -
Vikled -
Hello,
I want to create an Excel document that will be made available to several people. The content of this document will be modified every day, but I want to keep a copy of all the changes in a separate document titled "filename + date," without altering my original document.
Is it possible to prevent saving modifications (thus keeping my original file "blank") but instead only allow "save as," while automatically inserting the modification date into the title of the newly saved document?
I wonder if it is possible to integrate a button directly into the spreadsheet (= macro??) to facilitate this option, but I admit I'm completely lost^^
Thank you in advance for your help.
I want to create an Excel document that will be made available to several people. The content of this document will be modified every day, but I want to keep a copy of all the changes in a separate document titled "filename + date," without altering my original document.
Is it possible to prevent saving modifications (thus keeping my original file "blank") but instead only allow "save as," while automatically inserting the modification date into the title of the newly saved document?
I wonder if it is possible to integrate a button directly into the spreadsheet (= macro??) to facilitate this option, but I admit I'm completely lost^^
Thank you in advance for your help.
3 réponses
Thank you for your response ;-)
Unfortunately, I wasn't able to get it to work with your file.. yes yes, I'm just barely discovering the macro function ^^
After tinkering with it and being inspired by your advice and other codes on the web, I ended up cobbling together a macro that I've associated with a command button.
This macro allows me to save the new file as a PDF, print it, and close the document (which prevents any modifications to my original document).
Here is the code, for those who are interested:
Sub SaveAndPrint()
'
' SaveAndPrint Macro
'
Dim Path As String, name As String
Path = ActiveWorkbook.Path & " \"
name = "Command" & "_" & Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-mm")
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & " \" & "Command" & "_" & Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-mm") & ".pdf"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Application.DisplayAlerts = False
MsgBox ("The file has been saved as: " & Path & name)
ActiveWorkbook.Close
End Sub
Thank you for your help :-)
Unfortunately, I wasn't able to get it to work with your file.. yes yes, I'm just barely discovering the macro function ^^
After tinkering with it and being inspired by your advice and other codes on the web, I ended up cobbling together a macro that I've associated with a command button.
This macro allows me to save the new file as a PDF, print it, and close the document (which prevents any modifications to my original document).
Here is the code, for those who are interested:
Sub SaveAndPrint()
'
' SaveAndPrint Macro
'
Dim Path As String, name As String
Path = ActiveWorkbook.Path & " \"
name = "Command" & "_" & Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-mm")
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & " \" & "Command" & "_" & Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-mm") & ".pdf"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Application.DisplayAlerts = False
MsgBox ("The file has been saved as: " & Path & name)
ActiveWorkbook.Close
End Sub
Thank you for your help :-)
Hello,
Here is a file that should meet your expectations:
http://cjoint.com/?0DkoeE03yGu
When you open the file, all the sheets are blank.
Enter some data on sheet 1 for example and click on save or save as. The file will automatically copy to the same directory as the original file but with the date added and will close immediately. The original has not been modified at all.
The code is located in "This Workbook" and part of it in "Module1"
Here is a file that should meet your expectations:
http://cjoint.com/?0DkoeE03yGu
When you open the file, all the sheets are blank.
Enter some data on sheet 1 for example and click on save or save as. The file will automatically copy to the same directory as the original file but with the date added and will close immediately. The original has not been modified at all.
The code is located in "This Workbook" and part of it in "Module1"
Hello Isakissss,
I would like to save an Excel file after entering data in a table.
For example, if cell (D5) = 201400320, I want to save it on my desktop under the name 201400320. If tomorrow I change the number in D5 to 20150088, I want to save it on my desktop under the new number.
Thank you for your help.
I would like to save an Excel file after entering data in a table.
For example, if cell (D5) = 201400320, I want to save it on my desktop under the name 201400320. If tomorrow I change the number in D5 to 20150088, I want to save it on my desktop under the new number.
Thank you for your help.
You need to change the expression ActiveWorkbook.ExportAsFixedFormat to ActiveSheet.ExportAsFixedFormat
The code would then become:
Sub SaveAndPrint()
'
' SaveAndPrint Macro
'
Dim Path As String, name As String
Path = ActiveWorkbook.Path & "\"
name = "Order" & "_" & Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-mm")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\" & "Order" & "_" & Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-mm") & ".pdf"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Application.DisplayAlerts = False
MsgBox ("The file has been saved as: " & Path & name)
ActiveWorkbook.Close
End Sub