View original (French)

Create macro "save as" with filename + date

Solved
isakissss Posted messages 2 Status Member -  
 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.

3 answers

  1.
    isakissss Posted messages 2 Status Member 1
     
    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 :-)
    1
    1. kamikase Posted messages 2 Status Member
       
      Bonsoir, j'ai utilisé ce code, mais quand on enregistre en PDF, c'est tout le classeur qui s'enregistre. Que faut-il ajouter pour n'enregistrer que la page active ? Merci.
      0
      1. Vikled > kamikase Posted messages 2 Status Member
         
        Hello,

        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
        0
  2. Weapon EDGE
     
    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"
    0
  3. mimi13580 Posted messages 72 Status Member
     
    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.
    0