Macro Save to PDF with date stamp

[ReaSCH]

Dear all,

I unfortunately have no clue how to build macros. I need a Save to PDF macro that saves my dashboard following the document path, takes the worksheet name and puts the date next to the worksheet name when saving the PDF.
I have been looking through multiple blogs and forums, but I seem not able to put the right bits and pieces together.

My brother built the following macro for me, which already does 80 % of what I would need.

Sub Save_as_pdf()

Dim FSO As Object
Dim s(1) As String
Dim sNewFilePath As String
Dim cut_date As String
Dim add_date As String
Set FSO = CreateObject("Scripting.FileSystemObject")
s(0) = ThisWorkbook.FullName
cut_date = Left(s(0), Len(s(s0)) - 5)
add_date = cut_date & "_" & Format(Date$, "dd.mm.yy")

If FSO.FileExists(s(0)) Then
'//Change Excel Extension to PDF extension in FilePath
s(1) = FSO.GetExtensionName(add_date)
If s(1) <> "" Then
s(1) = "." & s(1)
sNewFilePath = Replace(add_date, s(1), ".pdf")
'//Export to PDF with new File Path
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=sNewFilePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End If
Else
'//Error: file path not found
MsgBox "Error: this workbook may be unsaved. Please save and try again. A team of highly traind monkeys is working on a solution for this problem!"
End If

Set FSO = Nothing

End Sub

However, in the saved document name, the year stamp (.yy) is not visible. Other than that I would need to save the file with the worksheet name and not the workbook name, since I have more than one dashboard in the same file. Would it also be possible to add a time stamp next to the date stamp in the saved document name?

If this helps I could also include the document name and date/timestamp from cell values. (Cell: B2 and the date/timestamp Cell; BI2) as I have seen many solutions trying it that way.

I really appreciate your help!
Cheers,
Rea

 

[DeBeuz]

Hi,

Try this instead:

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

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

Succes,

Paul

 

[ReaSCH]

Dear Paul!

Thank you so much. You made my day...
The macro does exactly what I wanted and even looks a lot better.
Thanks!
Rea

 

[elynoy]

I know this is an old post but, is it possible to set it to all sheets in the file instead of the active sheet?

 

[dnorm]

Hi,

Try this instead:

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

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

Succes,

Paul

Cheers DeBeuz

I changed the highlighted text to suit my requirements, works a treat!

ThisWorkbook... to required directory path
Now() changed to Range()