VBA code to add cell information to file name

[STIRRELL]

Hi all,
I am working on a macro to save off a file. We would like to pull a few cells of data from a spreadsheet into the name of the spreadsheet. The start date is in cell B3 on the INPUT tab. How can we pull this into the file name using VBA? I think the dashes will cause a problem so we can use underscore instead of dashes .

For example
Start Date: 02/11/22 ( or 2_11_22)

existing code:
Dim NTCT_EXPENSE_PAYROLL As Workbook
Dim CSV_FILE As Worksheet

Set shtToExport = ThisWorkbook.Worksheets("CSV_FILE") 'Sheet to export as CSV
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False 'Possibly overwrite without asking
wbkExport.SaveAs Filename:="C:\EXPORT\7J1", FileFormat:=xlCSV
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

thank you for your help
Sharon

 

[Marc L]

Hi,​

​

you can use the Replace VBA function on the B3 cell Text property in order to replace "/" with "-" for example …​

 

[kevin9999]

Does this give you what you want?

Sub Save_With_Date()
    Dim StartDate As Date
    StartDate = Worksheets("INPUT").Range("B3").Value
    
    ThisWorkbook.Sheets("CSV_File").Copy
    ActiveWorkbook.SaveAs "C:\EXPORT\7J1 " & Format(StartDate, "d_m_yy") & ".csv", FileFormat:=6
End Sub