Save worksheet as pdf with date as name

[fattyhuman]

I am a complete novice at VBA's so apologies straight up. I thought I had this sorted but cannot get it to compile and work correctly.
I am trying to write a macro that will allow me to print a worksheet in an excel file as a pdf and then save it to a pre determined folder with the file name of the printed work sheet being "Daily Report yyyy-mm-dd"

I then need to send this sheet as an email, I have this part all but sorted, but just cannot get the saving right.

Any help would be greatly appreciated.

 

[Logit]

The following will allow you to preselect the folder where you want the pdf to be saved. If you truly want the location "hard coded" let me know.

Option Explicit

Sub PrintTableToPDF()
Dim strfile As String
Dim myfile As Variant
Dim strTable As String, r As Range
Application.ScreenUpdating = False


        'Prompt for save location
        strfile = "Daily Report " & _
        Format(Now(), "yyyy-mm-dd") & _
        ".pdf"
        strfile = ThisWorkbook.Path & "\" & strfile
        
        myfile = Application.GetSaveAsFilename _
        (InitialFileName:=strfile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and File Name to Save as PDF")
        
            If myfile <> "False" Then 'save as PDF
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myfile, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            Else
                MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
            End If

Application.DisplayAlerts = False

LetsContinue:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
Exit Sub
End Sub

 

[fattyhuman]

The following will allow you to preselect the folder where you want the pdf to be saved. If you truly want the location "hard coded" let me know.

Option Explicit

Sub PrintTableToPDF()
Dim strfile As String
Dim myfile As Variant
Dim strTable As String, r As Range
Application.ScreenUpdating = False


        'Prompt for save location
        strfile = "Daily Report " & _
        Format(Now(), "yyyy-mm-dd") & _
        ".pdf"
        strfile = ThisWorkbook.Path & "\" & strfile
       
        myfile = Application.GetSaveAsFilename _
        (InitialFileName:=strfile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and File Name to Save as PDF")
       
            If myfile <> "False" Then 'save as PDF
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myfile, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            Else
                MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
            End If

Application.DisplayAlerts = False

LetsContinue:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
Exit Sub
End Sub

If it can be hard coded so I can eliminate people saving it wherever they want.
The code is awesome thanks.

 

[Logit]

This code saves the pdf to the users desktop in a folder named "test" :

Sub PrintTableToPDF()
Dim strfile As String
Dim strTable As String, r As Range
Application.ScreenUpdating = False

        'Fixed save location
        strfile = Environ("UserProfile") & "\OneDrive\Desktop\Test\Daily Report " & _
        Format(Now(), "yyyy-mm-dd") & _
        ".pdf"
        
        'Save as PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strfile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
Application.DisplayAlerts = False

LetsContinue:
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
Exit Sub
End Sub

 

[fattyhuman]

Legend. Thanks SSSOOO much!!!!

 

[Logit]

You are welcome.