[RESOLVED] Macro to add today's date when saving the file in a different format

Hello!

I want to send specific worksheets in Excel to PDF and save in a specified folder. I have done this successfully using:

Code:

---

Sub SendPDF()

' SendToPDF Macro

    Sheets(Array("COVER", "In DAILY Summary", "Out DAILY Summary", _
        "Out DAILY by Time")).Select
    Sheets("COVER").Activate
    ChDir "C:\MyFileLocation"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:/MyOutputLocation\Stats.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End Sub

---

However, I'd like to take It one step further by adding today's date when it saves into the file name. So the above would become "Stats 11-03-2016.pdf".

I found this in an inferior forum for adding the date part. I've tried using part of the below in the above but doesn't work. Any ideas please?

Code:

---

Sub Save()

Dim dtDate As Date
dtDate = Date

Dim strFile As String
strFile = "X:\file" & Format(dtDate, "mm-dd-yyyy") & ".xlsm"

ActiveWorkbook.SaveAs Filename:=strFile, FileFormat _
    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

---

Thanks!

try like

Code:

---

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:/MyOutputLocation\Stats" & format(Date,"dd-mm-yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

---

though i am not sure you should have a / in your file path

The / was my typo when I was changing the file path to something non-identifying :) I'll give this a go now.

Thanks.

I used this but error 1004, Select method of sheets class failed.

Code:

---

Sub SendPDF()

' Send To PDF with Date Macro

    Sheets(Array("COVER", "In DAILY Summary", "Out DAILY Summary", _
        "Out DAILY by Time")).Select
    Sheets("COVER").Activate
    ChDir "C:\MyFileLocation"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\MyOutputLocation\Stats" & Format(Date, "dd-mm-yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

---

the error would appear to be for the first line of code?

the first 3 lines are possibly irrelevant to the task, try

Code:

---

Sub SendPDF()

    sheets("cover").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\MyOutputLocation\Stats" & Format(Date, "dd-mm-yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

---

Quote:

---

Originally Posted by westconn1

the error would appear to be for the first line of code?

the first 3 lines are possibly irrelevant to the task, try

Code:

---

Sub SendPDF()

    sheets("cover").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\MyOutputLocation\Stats" & Format(Date, "dd-mm-yyyy") & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

---

---

I need the first few lines because I have to nominate which worksheets I want printing or it prints all (there are 7 worksheets, I only want to export 4 to PDF).

Quote:

---

Originally Posted by VbtoMe

I need the first few lines because I have to nominate which worksheets I want printing or it prints all (there are 7 worksheets, I only want to export 4 to PDF).

---

Managed to work round my issue. I added

"worksheets("cover".visible = True" etc.

for the worksheets I wanted to print and used the same code as before and it works.