save a pdf using a "date" value in a cell as a string in my file name

[martinargimon]

HI there,

I want to use the value of a cell in my spreadsheet to be incorporated as part of my PDF file NAME.
in my sub routine i want to save it in a specific folder starting with the file name as "Remittance date ".... ( and then bring across the date value form the cell , to form part of the name of my file at the end.
I've tried different 'formatting ' option , but my PDF creator does not like what i'm doing
Can u please helP?
Many Thanks
regards , MArtin

 

[vletm]

martinargimon​

I had some challenges even open Your file.
I tried to continue anyway.
You wrote something about date ... and ... range("J15").
... Range J15 do not have a date ... Range J14 has
If You tried to have / while You formatted that date then
... it's possible BUT then there have to be many folders ( each date pary needs own folder )
If You format Your date without those / then it works ( or You can use eg - to separate Date - Month and Year )
Here one sample,
but it used fixed my named folder ( I cannot use Your named path )
Ps. There can be some challenges if You try to use long path (as You're using).

 

[martinargimon]

HI vletm,
Thanks for you help.
you are correct is saying that ius cell j14 ( the one with a date, sorry).
So in essence the only thing that i want to do is everytime i save my 'remittance' proof , is to select the range that i wnat to save ( which i've done) , and then just add the name of the file preceeded with the path : ....... and then add the Lease No. as well as the listed in J14..
In this way I will be able to go to the folder "Remittances" and will be able to locate the Remittance by 'Lease no." a well as the date.
What i do not know is the syntax to use to get the PDF writer to save the date in the format listed in j14.
( Not sure if i am making sense).

WS2.Range("A1:L43").ExportAsFixedFormat xlTypePDF, Filename:= _
"C:\Users\martin argimon\CMA\CMA Realty - Documents\Administration\ICT\Database Program\Remittances Owners\" & "Remittance_Lease No. " & WS2.Range("j15").Value, _
IgnorePrintAreas:=False, Openafterpublish:=True

 

[vletm]

martinargimon​

You seem to skip one possible solution...

& "Remittance_Lease No. " & WS2.Range("j15").Value

Test to change above to

& "Remittance_Lease No.\" & format(WS2.Range("J14"),"yyyymmdd")

... get possible date.
Your given path do not have Remittances?
Can You find out the real full path - what do You have?
and
What should be after that?
( path to PDF have to be 100% correct )

 

[Marc L]

What i do not know is the syntax to use to get the PDF writer to save the date in the format listed in j14.

Hi, just use the cell property Text rather than Value …​

​

As explained in VBA help - a must read ! - a date cell properties demonstration to try on a blank worksheet :​

Sub Date4Noob()
   With [D4]
       .NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
       .Value = Date
        MsgBox "Text  :  " & .Text & vbLf & vbLf & "Value  : " & .Value & vbLf & vbLf & "Value2 : " & .Value2, , "D4 properties"
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[martinargimon]

Thanks Marc L. I will try your suggestion . kind regards . MArtin

 

[martinargimon]

Marc L.
your code worked 100% , Thanks a million
Kind regards
Martin

 

[martinargimon]

Hi Marc L.
I wanted to make just a change to the code by creating a variable that 'holds' the date in my remittance form, rather than using your proposed code .......' Format(WS2.Range("J14"), "yyyymmdd") , which by the way works 100% Ok.
Is only that i thought i could save the date in a variable called "invoicedate" ( see below), but the PDF writer saves the file with a weird name like below:
( in other words instead of 20241221 as today's date , it saves it as an unrecognizable number "2435612221



How can i get the invoicedate variable to hold the number the correct way i.e. 20241221 ?
Thanking you in advance
Martin

>>> use code - tags <<<

Private Sub CmdB_SaveRemittance_Click()

    Dim WS2 As Worksheet
    Set WS2 = Worksheets("Owner_Payment_Remittance")
   
    ' Declare variables for lease number and invoice date
    Dim leaseno As String
    Dim invoicedate As String
   
    ' Retrieve values from the worksheet
    leaseno = WS2.Range("J15").Value
    invoicedate = Format(WS2.Range("J14"), "yyymmdd")
   
    ' Combine the file path with lease number and invoice date
    Dim filePath As String
    filePath = "C:\Users\martin argimon\CMA\CMA Realty - Documents\Administration\ICT\Database Program\Remittances Owners\" & _
               "Remittance Lease No." & leaseno & "_" & invoicedate
              
               ' Format(WS2.Range("J14"), "yyyymmdd")
   
    ' Export the range as a PDF  I did not enter Type:=xlTypePDF
    WS2.Range("A1:L43").ExportAsFixedFormat xlTypePDF, _
        Filename:=filePath, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
  
 End Sub

 

[Marc L]

the PDF writer saves the file with a weird name

Your bad as a "y" char is missing within the date format !​

If the cell layout is correct then the VBA Format function is useless just using the cell property Text …​

​

Anyway according to your previous post code my VBA two cents :​

Sub Demo1()
        Const C = "C:\Users\martin argimon\CMA\CMA Realty - Documents\Administration\" & _
                  "ICT\Database Program\Remittances Owners\Remittance Lease No."
   With Worksheets("Owner_Payment_Remittance")
       .[A1:L43].ExportAsFixedFormat 0, C & .[J15] & Format(.[J14], "_yyyymmdd"), OpenAfterPublish:=True
   End With
End Sub

You may Like it !​

​

 

[martinargimon]

I will try this afternoon and revert. Thanks a lot Marc L. Kind Regards. Martin

 

[martinargimon]

Thanks very much for your updated code.
Last question, please, as follows: Based on my initial question, is there a way to save the date in my cell J14 as a variable that i can then use for other subroutines.?. Right now. as per you code above the date is not a variable in the file path.
I tried creating a variable to hold the date, but the PDf writer rejects it. Any thoughts on this ?
Thanks Marc.

 

[Marc L]

There is no issue to use a variable as a string date, maybe you forgot to correct the date format …​

Check the variable content and the file path in order to find out the reject reason.​

​

 

[Marc L]

According to your initial post attachment where the VBA procedure is included in the 'Owner_Payment_Remittance' worksheet module​

then it is very not necessary to qualify this worksheet in the code !​

​

So removing the useless the VBA procedure revamped as​

Private Sub CmdB_SaveRemittance_Click()
    S$ = "C:\Users\martin argimon\CMA\CMA Realty - Documents\Administration\ICT\Database Program\Remittances Owners\"
    If Dir(S, 16) = "" Then Beep: Exit Sub
    S = S & ["Remittance Lease No."&J15&"_"&TEXT(J14,"yyyymmdd")]
    [A1:L43].ExportAsFixedFormat 0, S, OpenAfterPublish:=True
End Sub

You should Like it !​

​

 

[martinargimon]

Ok Marc. Thanks a lot for all your help and suggestions. You can go ahead a close this ticket. Kinmd Regards . Martin