VBA routine: save a file as PDF and add a specific date

[Burrgogi]

Every week, I send out a schedule to various people. Some of the people have complained that they don't have Excel installed on their home computers so I've started sending out the schedule in PDF format. Problem easily solved. However, I'd like to add a specific date to the filename itself. I usually edit the schedule on Sat or Sun and email it to everyone. Cell A5 contains the first day of the schedule in mm-dd-yy format.

I'd like the filename to be: "Schedule for mm-dd-yy.xlsx"

I searched the forums and this thread is actually a very close fit to what I need. However, when I run it - I get a 400 error. Any help would be greatly appreciated.

P.S. And yes... i did change the C5 to A5 in VBA routine. I still get the error.

 

[Burrgogi]

Ooops, I meant to say that the filename should read as:

"Schedule for mm-dd-yy.pdf" (not xlsx)

 

[John_w]

How about:

Sub SavePDF()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & "\Schedule for " & Format(Range("A5").Value, "mm-dd-yy") & ".pdf", _
        Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

 

[Burrgogi]

Awesome! Thank you John_w. Just for my own sake of knowledge, do you know why I was getting a 400 error on that first routine I found?

 

[John_w]

Maybe because the path "C:\Users\Test\Desktop\" doesn't exist.

Or, without an explicit VBA Format (as in my code), the variable 'x' date string is formatted as the default system date format which may contain characters (e.g. forward slash) which are invalid in file names.

 

[Burrgogi]

Maybe because the path "C:\Users\Test\Desktop\" doesn't exist.

Or, without an explicit VBA Format (as in my code), the variable 'x' date string is formatted as the default system date format which may contain characters (e.g. forward slash) which are invalid in file names.

Makes total sense. Thanks.