Auto create PDF at set time AND have it change the file name each time

[MARK1111]

Hi all,

Hopefully this is possible. I have a workbook containing around 100 or so sheets. The first sheet is the summary sheet, that contains amounts owed by each customer. These are displayed as a list from top to bottom, with the amounts owed beside each name in the adjacent column.
What I wish to do is to automatically create a PDF, containing that data within that summary page (or at least those two important columns) that saves at a particular time of day (say 17:00 hours) but in addition to this, each time it saves, ideally the file name would change slightly, to avoid it overwriting the previous days pdf.
Is this sort of thing possible in Excel (2016 edition)?

As always, I'd be grateful for your input and assistance.

Thanks,

Mark

[/URL][/IMG]

 

[John_w]

Yes, this is possible. Record a macro of you saving the active sheet (Summary) as a PDF. Then the code can be changed to include the current date in the file name. And look at Application.OnTime to run the macro at a specific time.

 

[Logit]

.

Option Explicit


Sub Save_ActSht_as_Pdf()
    ' Saves active sheet as PDF file.


    Dim Name As String
    
    Name = Environ("UserProfile") & "\Desktop\PDF\" & ActiveSheet.Name & " " & _
        Format(Now(), "mm.dd.yy hh.mm.ss") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

 

[MARK1111]

.

Option Explicit


Sub Save_ActSht_as_Pdf()
    ' Saves active sheet as PDF file.


    Dim Name As String
    
    Name = Environ("UserProfile") & "\Desktop\PDF\" & ActiveSheet.Name & " " & _
        Format(Now(), "mm.dd.yy hh.mm.ss") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Hi Logit,

Thanks for providing the code. I'm assuming that I add it to to the macro i created, as per John_w's advice? I gave it a try and came across two issues. When I record a macro of myself selecting and printing the summary page, running it gets as far as the save box and it doesn't name the file or click save. I also notice that if I run it on a different sheet, it uses that sheet rather than the summary sheet.
Then when I added your code (perhaps it wasn't intended to be added to the above) it came up with an error and didn't progress any further.
Here is the code generated when I recorded the macro. I've not yet got as far as adding the part about 'Application.OnTime' to get it to execute the macro at a set time.

Sub TESTprintPDF()
'
' TESTprintPDF Macro
'
 
'
    Sheets("!! SUMMARY SHEET !!").Select
    Range("D6:F200").Select
    Selection.PrintOut Copies:=1, Collate:=True
End Sub

 

[John_w]

Try this code, which uses Application.OnTime to save the first sheet as a PDF in the same folder as the workbook at 17:00:00. Note - if you close the macro workbook, but keep Excel open, perhaps opening another workbook, Excel will reopen the macro workbook at 17:00:00 and run the Save_Sheet_As_PDF procedure.

Put this in the ThisWorkbook module:

Private Sub Workbook_Open()
    Start_Timer
End Sub

Put this in a normal module (e.g. Module1):

Public RunWhen As Double

Public Sub Start_Timer()
    RunWhen = TimeValue("17:00:00")
    Application.OnTime EarliestTime:=RunWhen, Procedure:="Save_Sheet_As_PDF", Schedule:=True
End Sub

Public Sub Stop_Timer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:="Save_Sheet_As_PDF", Schedule:=False
End Sub

Public Sub Save_Sheet_As_PDF()

    ThisWorkbook.Worksheets(1).ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\Summary " & Format(Date, "yyyy-mm-dd") & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    MsgBox "Saved sheet as PDF"
        
End Sub

 

[Logit]

.
The macro is to be pasted into a regular module. It will save whatever is the ACTIVE SHEET as a PDF file to a folder located on your
desktop named PDF.

The macro does not create the folder PDF ... it must exist prior to running the macro.

It will save the ACTIVE SHEET with the current date and time. That way, when a new PDF is created the current date and time will be different each time.

If you want to have the macro auto-run at 17:00 hrs each day, you will need to create a timer that will activate the macro to accomplish this. Otherwise,
you will need to place a command button on the ACTIVE SHEET to be saved, having the button connected to the macro.

You can edit 'ActiveSheet.Name' to the name of a specific sheet if that is your desire.

 

[MARK1111]

Try this code, which uses Application.OnTime to save the first sheet as a PDF in the same folder as the workbook at 17:00:00. Note - if you close the macro workbook, but keep Excel open, perhaps opening another workbook, Excel will reopen the macro workbook at 17:00:00 and run the Save_Sheet_As_PDF procedure.

Put this in the ThisWorkbook module:

Private Sub Workbook_Open()
    Start_Timer
End Sub

Put this in a normal module (e.g. Module1):

Public RunWhen As Double

Public Sub Start_Timer()
    RunWhen = TimeValue("17:00:00")
    Application.OnTime EarliestTime:=RunWhen, Procedure:="Save_Sheet_As_PDF", Schedule:=True
End Sub

Public Sub Stop_Timer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:="Save_Sheet_As_PDF", Schedule:=False
End Sub

Public Sub Save_Sheet_As_PDF()

    ThisWorkbook.Worksheets(1).ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\Summary " & Format(Date, "yyyy-mm-dd") & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    MsgBox "Saved sheet as PDF"
        
End Sub

You are a genius! This worked exactly as required, thank you.

Thank you too, Logit, for your support. I find it mildly frustrating that I don't have the knowledge to work these things out for myself but equally, I really appreciate the help and support I receive from this forum. Thanks again.

 

[Logit]

You are welcome.