Filename based on value cell,but with specific format

Question

i want to save a filename based on a cell value (AA2 for example) which has date format (dd/mm/yyyy) but i want it to be like yyyymmdd without slash (/). This code works fine but gives me the current date (which doesnt help)

Sub CopyToCSV()
Dim MyPath As String
Dim MyFileName As String
'The path and file names:
MyPath = "C:\Hello"
MyFileName = "Greetings_" & Format(Date, "yyyymmdd")
'Makes sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then Hello = Hello & ".csv"
'Copies the sheet to a new workbook:
Sheets("final step").Copy
'The new workbook becomes Activeworkbook:
With ActiveWorkbook
'Saves the new workbook to given folder / filename:
    .SaveAs Filename:= _
        MyPath & MyFileName, _
        FileFormat:=xlCSV, _
        CreateBackup:=False, _
        Local:=True
'Closes the file
    .Close False
End With
End Sub

`MyFileName = "Greetings_" & Format((Range("AA2").Value, "yyyymmdd") & ".csv"` — Tim Williams, Feb 23 '23 at 16:15
I tried it but even the cell AA2 is not empty, excel name stops at Grretings_ — Larry_Laffer, Feb 23 '23 at 18:57
If the cell you need isn't on the ActiveSheet then you need to specify which sheet you want to take the date from... — Tim Williams, Feb 23 '23 at 19:24

score 0 · Accepted Answer · answered Feb 23 '23 at 20:24

0

Like this:

Sub CopyToCSV()
    Const MY_PATH As String = "C:\Hello\"
    Dim MyFileName As String, dt As Date
    
    dt = ThisWorkbook.Sheets("info").Range("AA2").Value '<< specify sheet name
    MyFileName = MY_PATH & "Greetings_" & Format(dt, "yyyymmdd") & ".csv"
    
    ThisWorkbook.Sheets("final step").Copy
    
    With ActiveWorkbook 'The new workbook becomes Activeworkbook
        .SaveAs Filename:=MyFileName, FileFormat:=xlCSV, _
            CreateBackup:=False, Local:=True
        .Close False
    End With
End Sub

answered Feb 23 '23 at 20:24

Tim Williams

154,628
8
97
125

Another issue fro the above code. I cant find out got to make this csv with utf8 encoding. Seems in 2016 excel doesnt work – Larry_Laffer Feb 24 '23 at 07:06
https://stackoverflow.com/a/49399803/478884 – Tim Williams Feb 24 '23 at 07:13

Filename based on value cell,but with specific format

1 Answers1