Add date to pdf output

[ipbr21054]

Hi,
Currently when i save my excel sheet as pdf i use this line of code.

strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\PAUL GODDARD\" & "INVOICE " & Range("L4").Value & ".pdf"

So the above is saved like so INVOICE 1.pdf

Now i wish to include the date so the above becomes INVOICE 1 27/03/2024.pdf
So i added this extra piece of code to the previous mentioned above so now like so.

strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\PAUL GODDARD\" & "INVOICE " & Range("L4").Value & Range("L13").Value & ".pdf"

But when i now go to save i get a RTE 1004 & the line below is shown in yellow


FULL CODE.

Private Sub Generate_Pdf_Click()
    Dim strFileName As String
  
    strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\PAUL GODDARD\" & "INVOICE " & Range("L4").Value & Range("L13").Value & ".pdf"
  
    If Dir(strFileName) <> vbNullString Then
        MsgBox "GENERATED PDF INVOICE" & vbNewLine & vbNewLine & "INVOICE " & Range("L4").Value & vbNewLine & vbNewLine & "WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "GENERATE PDF FILE MESSAGE"
        Exit Sub
    End If
  
    With ActiveSheet
        .PageSetup.PrintArea = "$F$2:$N$61"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        pdf = MsgBox("GENERATED PDF INVOICE" & vbNewLine & vbNewLine & "INVOICE " & Range("L4").Value & vbNewLine & vbNewLine & "WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE")
                              
        Range("L4").Value = Range("L4").Value + 1 'INVOICE IS INCREMATED BY 1
        ActiveWorkbook.Save
      
    End With
      
End Sub

 

[Joe4]

Now i wish to include the date so the above becomes INVOICE 1 27/03/2024.pdf

You cannot use "/" in file names - those are illegal characters for file names. You could use dashes instead, i.e.
INVOICE 27-03-2024.pdf

If cell L13 has a valid date in it, to get it to format like that for your file name, you would refer to it like this in your VBA code:

... & FORMAT(Range("L13"),"dd-mm-yyyy") & ...

Also note: When posting VBA code, if you want to add formatting (like text color) to your VBA code, you need to use the RICH code tags, and not the VBA code tags.
You cannot use formatting within VBA code tags.
I fixed your last two code tag blocks in your previous post.

 

[ipbr21054]

Hi,
The line of code now in use is shown below.

strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\PAUL GODDARD\" & "INVOICE " & Range("L4").Value & Format(Range("L13"), "dd-mm-yyyy") & ".pdf"

It saves now as supplied screenshot.

Can you advise what i need to do so the output is saved like so,
INVOICE 2
27-03-2024.PDF

Where its on two lines.

With reference RICH / VBA i always used RICH but recently was told that i hadf to use VBA ???
I will just go back to using RICH
Thanks

 

[Joe4]

Can you advise what i need to do so the output is saved like so,
INVOICE 2
27-03-2024.PDF

Where its on two lines.

You cannot put formatting characters, like soft carriage returns, in the file names.
If you show it splitting over two lines, like on a Desktop shortcut, that is a function of the horizontal space the shortcut allows.
I don't think there is really a soft carriage return in there, I think it is more of an auto-wrap situation.

With reference RICH / VBA i always used RICH but recently was told that i hadf to use VBA ???
I will just go back to using RICH

You can use either code tag.
Just realize that you CANNOT use formatting features, like text color, bolding, underline, italics, etc within VBA code tags. It won't render properly.
So if you want to use those formatting features, use the RICH code tags in those situations.

 

[ipbr21054]

This worked for what i need Thanks.

strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\PAUL GODDARD\" & "INVOICE " & Range("L4").Value & " " & Format(Range"L13"), "dd-mm-yyyy") & ".pdf"

 

[Joe4]

You are welcome.
Glad I was able to help!