CODEWITHSUNDEEP
excelvbapdfvba7
toothgap01
toothgap01

Reputation: 11

Exporting excel file to pdf with the file name as combined information from a Cell and the date and a word

I am coding in VBA in excel. I am trying to export an excel file as a pdf. I want the name of the pdf file to be a combination of multiple things: Words (it's a name) from cell D3 & todays date & the word 'invoice'. Right now, the file exports, but it has the name 'combined' instead of showing Name09-12-202Invoice.

How do I get the 'combined' variable to reflect the Name & date & word invoice, and then get the pdf to have that name?

The code runs, just not as I am expecting. Since the exported pdf file has the name combined, instead of the information in the 'combined variable'.

I don't know if I need to have all of the dimensions for 'combined' to be a string. But if that's the case I don't know how to turn a date into a string dimension. Am I combining the clientname, client date, and client invoice variables together into one variable 'combined' properly? And primarily, why isn't the 'combined' at the end of saveLocation, not the information in the 'combined' variable?

Thank you to anyone who helps! I am new to VBA so I appreciate it!

Sub SaveActiveSheetsAsPDF()

    'Create and assign variables
    Dim saveLocation As String
    Dim clientname As String
    Dim clientdate As Date
    Dim clientinvoice As String
    Dim combined As String
    
    clientname = Range("D3")
    clientdate = Date
    clientinvoice = "Invoice"
    combined = clientname & clientdate & clientinvoice
    
    saveLocation = "C:\Users\jessi\Documents\Info\combined.pdf"
    
    'Save Active Sheet(s) as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        filename:=saveLocation

End Sub

Upvotes: 0

Views: 4077

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Like this:

clientname = Range("D3").Value
clientdate = Format(Date, "yyyy-mm-dd") 'Make sure you don't end up with (eg) mm/dd/yyyy format...

saveLocation = "C:\Users\jessi\Documents\Info\" & _
                clientname & "_" & clientdate & "_Invoice.pdf"
    
'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                 filename:=saveLocation

Upvotes: 2

Related Questions