Change displayed format in worksheet for path and filename in VBA

Question

I have an Excel worksheet with a specific cell for a customer and another cell for a date formatted as mmm dd, yyyy

When writing VBA code to create and record the worksheet as a PDF file in a folder unique to the customer and date, I want to use the the customer and date from the worksheet in the path and filename. However, the date format for the path needs to be "yyyy" and the date format for the file name as "mm-dd-yy"

For example, in the workbook the customer is 004 and date is Jun 07, 2022 The path would be C:\004\2022
The file name would be 06-07-22---004.pdf

My attempt so far:

Sub SaveBilling()

Dim Customer as String
Dim BillDate as String
Dim Path as String
Dim File as String

Customer = Range ("B1")
BillDate = Range ("D1")
Path = "C:\Customer\BillDate\"
File = BillDate & "---" & Customer

'more code here to save as pdf

End Sub

Where in the code do I enter formatting of the dates for the path and file name?

Format (Date, "yyyy"), Format (Date, "mm-dd-yy")

Thanks to all who've assisted in clarifying my prior presentations. Learning to use Stack Overflow has become a skillset in itself!

Tim Williams · Accepted Answer · 2025-08-08 17:58:31Z

2

For example:

Sub SaveInvoicesAs_pdf()
    'use Const for fixed values
    Const ROOT As String = "C:\Invoice Records" 'no ending "\"
    
    Dim InvoiceDate As Date, ws As Worksheet, FileName As String, Member As String
    
    Set ws = ActiveSheet 'or some specific sheet
    InvoiceDate = ws.Range("H13").Value
    Member = ws.Range("C13").Value
    
    'build the path
    FileName = Join(Array(ROOT, Member, Year(InvoiceDate), _
                    Format(InvoiceDate, "mm-dd-yyyy") & " invoice.pdf"), "\")
    
    Debug.Print FileName
    
    'export the sheet...

End Sub

answered Aug 8 at 17:58

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Winston Smith Aug 9 at 1:31

Thank you Tim for your input. I rewrote my VBA code as per your example, but to no avail. In your example, I don't see how my file name will create as I need; I must be mistaken? Each invoice needs it own path defined by member and date. For members 004 & 005 on Jan 07, 2022 the paths and file names become: C:\Invoice Records\004\2022\01-07-22---004.pdf C:\Invoice Records\005\2022\01-07-22---005.pdf For February 07 the same members: C:\Invoice Records\004\2023\01-07-23---004.pdf C:\Invoice Records\005\2023\01-07-23---005.pdf The worksheet date range ("H13") is formatted mmm dd, yyyy

Shrotter Aug 9 at 5:17

@WinstonSmith What when wrong if you run the given example code? Maybe change the subcode for the filename to Format(InvoiceDate, "dd-mm-yy") & "---" & Member & ".pdf"

Tim Williams Aug 9 at 15:48

It's difficult to see how Feb 7 could end up as 01-07-23 It's not important how H13 is formatted as long as it contains an actual date value.

Winston Smith Aug 10 at 0:37

My mistake: a typo. I meant Feb 07, 2022 formatted to 02-07-22

Tim Williams Aug 10 at 1:00

Please edit your question to add your current code.

Lucky Joe · Accepted Answer · 2025-08-09 07:29:07Z

2

simplified (there are shorter solutions):

Sub SaveBilling()
' C:\Invoice Records\004\2022\01-07-22---004.pdf
' C:\Invoice Records\005\2022\01-07-22---005.pdf
' C:\Invoice Records\004\2023\01-07-23---004.pdf
' C:\Invoice Records\005\2023\01-07-23---005.pdf

    Const ROOT As String = "C:\Invoice Records\"

    Dim Customer As String
    Dim BillDate As String
    Dim BillDateYear As String
    Dim Path As String
    Dim File As String
    
    Customer = Range("B3")
    Customer = Format(Customer, "000")
        
    BillDate = Range("D3")
    BillDate = Format(BillDate, "mm-dd-yyyy")
    BillDateYear = Year(BillDate)
    
    Path = ROOT & Customer & "\" & BillDateYear & "\"
    File = BillDate & "---" & Customer
    
    Debug.Print Path
    Debug.Print File
    
    'more code here to save as pdf

End Sub

answered Aug 9 at 7:29

Lucky Joe

714 bronze badges

1 Comment

Winston Smith Aug 10 at 2:35

Much appreciation to you Tim!!! Your latest simplified example does the task as needed. In the months and years to come, creating an organized tree of folders and files for all customers will go smoothly and quickly :-)

Collectives™ on Stack Overflow

Change displayed format in worksheet for path and filename in VBA

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related