VBA how to use date to write file name in PDF

[wdg1]

How do I use "date document" for a PDF file name?
The declaration of "document date" causes an error:

Dim datedoc As Date
Then I use datedoc to my PDF-file name which does not work.


Sub InvoiceToPdf()
Dim volgnr As Long
Dim slo As String
Dim Toganr As Long
Dim bedrag As Currency
Dim fname As String
Dim datedoc As Date
volgnr = Range("B1")
slo = Range("B2")
Toganr = Range("B3")
bedrag = Range("B4")
Path = "C:\Users\gebruiker\factuur"
' this works not, the datedoc causes error
fname = Toganr & " - " & slo & " - " & volgnr & " - " & datedoc
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=Path & fname
End Sub
___________
Thanks, Ward

[Paul_Hossler]

I don't see where you're putting a value into 'datedoc'

[jolivanes]

As Paul alluded to, put the following line after the "bedrag = Range("B4")" line

datedoc = Format(Date, "yyyy-mm-dd")

[jolivanes]

Format(Now(), "yyyy-mmmm-dd")
'or maybe
Format(Now(), "mmmm-dd-yyyy")

[wdg1]

Dim volgnr As Long
Dim slo As String
Dim Toganr As Long
Dim bedrag As Currency
Dim fname As String
Dim datedoc As Date
volgnr = Range("B1")
slo = Range("B2")
Toganr = Range("B3")
bedrag = Range("B4")
datedoc = Range("B5")
Path = "C:\Users\gebruiker\factuur"
' fname = Toganr & slo & volgnr & datedoc
fname = Toganr & " - " & slo & " - " & volgnr & " - " & datedoc
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=Path & fname

______
Here I did put the value of cell B5 into "datedoc".
The macro does not work.

[wdg1]

Thanks, but adding this line causes an error.



Format(Now(), "yyyy-mmmm-dd")
'or maybe
Format(Now(), "mmmm-dd-yyyy")

[Aussiebear]

Thanks, but adding this line causes an error. Ward, please tell us the type of error? Oh by the way please enclose your code by using the # tag

[Paul_Hossler]

Dim volgnr As Long
Dim slo As String
Dim Toganr As Long
Dim bedrag As Currency
Dim fname As String
Dim datedoc As Date
volgnr = Range("B1")
slo = Range("B2")
Toganr = Range("B3")
bedrag = Range("B4")
datedoc = Range("B5")
Path = "C:\Users\gebruiker\factuur"
' fname = Toganr & slo & volgnr & datedoc
fname = Toganr & " - " & slo & " - " & volgnr & " - " & datedoc


MsgBox Path & fname ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=Path & fname

______
Here I did put the value of cell B5 into "datedoc".
The macro does not work.


Not being a mind reader or having a workbook to test, I'm guessing that if you added the marked line, there's no backslash before fname

Otherwise it depends on the contents of the ranges that are used to construct the fname

[wdg1]

Not being a mind reader or having a workbook to test, I'm guessing that if you added the marked line, there's no backslash before fname

Otherwise it depnds on the contents of the ranges that are used to construct the fname

The error is 1004:
The document has not been saved. The document may still be open or an error may have occurred while saving the document.

this is a strange error because, if I delete the "date", the macro works fine:
(A) fname = Toganr & " - " & slo & " - " & volgnr & " - " & datedoc
ERROR

(B) fname = Toganr & " - " & slo & " - " & volgnr & "
NO ERROR29887


Sub InvoiceToPdf()
Dim volgnr As Long
Dim slo As String
Dim Toganr As Long
Dim bedrag As Currency
Dim fname As String
Dim datedoc As Date
datedoc = Format(Date, "mmmm-dd-yyyy")


volgnr = Range("B1")
slo = Range("B2")
Toganr = Range("B3")
bedrag = Range("B4")
datedoc = Range("B5")


Path = "C:\Users\gebruiker\factuur\"


fname = Toganr & " - " & slo & " - " & volgnr & " - " & datedoc
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=Path & fname




End Sub


..

[jolivanes]

What is in B5?

[wdg1]

What is in B5?
In B5 is today date.
In Belgium =vandaag()
In english =today()

If I put the date of today, dd/mm/yy (26th June 2022), which in the USA is 06/26/2022, the macro does not work.

[wdg1]

Format(Now(), "yyyy-mmmm-dd")
'or maybe
Format(Now(), "mmmm-dd-yyyy")

ok, finally, I discovered where to use this suggestion!
The line has to be:


fname = Toganr & " - " & slo & " - " & volgnr & " - " & Format(Now(), "dd-mm-yyyy") & ".pdf"

Now today's date is added to the fine name.

Thank you again for your help, which is much appreciated.
Ward, belgium

[Aussiebear]

What happens if you wish to reprint or resave the pdf at a later date?

[Paul_Hossler]

If I put the date of today, dd/mm/yy (26th June 2022), which in the USA is 06/26/2022, the macro does not work.


You cannot use slashes in a filename


https://docs.microsoft.com/en-us/windows/win32/fileio/naming-a-file



Use any character in the current code page for a name, including Unicode characters and characters in the extended character set (128–255), except for the following:


The following reserved characters:

< (less than)
> (greater than)
: (colon)
" (double quote)
/ (forward slash)
\ (backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)