CODEWITHSUNDEEP
excelvba
Bijan
Bijan

Reputation: 3

Add today's date to file name

I need to add today's date to a file name.

I have part of the code copied from another file, but it doesn't have that feature.

Where it says "/CARYYMMDD2428395101.BCA" is the place that I need to change to today's date.

Sub Export_Selection_As_Fixed_Length_File()
 ' Dimension all  variables.
Dim DestinationFile, CellValue, Filler_Char_To_Replace_Blanks As String
Dim FileNum, ColumnCount, RowCount, FieldWidth As Integer
Dim sht As Worksheet

'Below are options incase you want to change the folder where VBA stores the .txt file
'We use ActiveWorkbook.Path in this example
'ActiveWorkbook.Path 'the activeworkbook
'ThisWorkbook.Path  'the workbook with the code
'CurDir  'the current directory (when you hit File|open)


'If a cell is blank, what character should be used instead
Filler_Char_To_Replace_Blanks = " "

    'Check if the user has made any selection at all
    If Selection.Cells.Count < 2 Then
        MsgBox "No row has been selected"
        Selection.Activate
        End
    End If

'This is the destination file name.
DestinationFile = ActiveWorkbook.Path & "/CARYYMMDD24284444101.BCA"
'Obtain next free file handle number.
FileNum = FreeFile()

I expect to get the name of the file as CAR19080824284444101.BCA

Upvotes: 0

Views: 3105

Answers (3)

user11707668
user11707668

Reputation:

This is some pseudo-code, which saves 'ThisWorkbook' into the specified path (directory eg. C:\Test) and adds the date to the end of the filename.

ThisWorkbook.SaveCopyAs <declare_path_variable> & **Format(Date, "dd-mm-yyyy")** & ThisWorkbook.Name

Upvotes: 1

ASH
ASH

Reputation: 20302

You can do it like this:

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "MasterCSV " & _
              Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

Upvotes: 0

Mark S.
Mark S.

Reputation: 2584

First I want to point out you qualified your variables incorrectly. The line Dim DestinationFile, CellValue, Filler_Char_To_Replace_Blanks As String only declares Filler_Char_To_Replace_Blanks as a String the rest are Variant types.

Format(Date, "yyyymmdd") is what you're looking for you can change the format however, I demonstrate below another way to name, but if you like what I here just modify it.

Sub Export_Selection_As_Fixed_Length_File()
' Dim all  variables.
Dim DestinationFile As String, CellValue As String, Filler_Char_To_Replace_Blanks As String
Dim FileNum As Integer, ColumnCount As Integer, RowCountAs Integer, FieldWidth As Integer
Dim sht As Worksheet

'Below are options incase you want to change the folder where VBA stores the .txt file
'We use ActiveWorkbook.Path in this example
'ActiveWorkbook.Path 'the activeworkbook
'ThisWorkbook.Path  'the workbook with the code
'CurDir  'the current directory (when you hit File|open)


'If a cell is blank, what character should be used instead
Filler_Char_To_Replace_Blanks = " "

    'Check if the user has made any selection at all
    If Selection.Cells.Count < 2 Then
        MsgBox "No row has been selected"
        Selection.Activate
        End
    End If

'This is the destination file name. Unsure if you wanted a certain format, but this shows you how to add it.
DestinationFile = ActiveWorkbook.Path & "/CARYYMMDD24284444101.BCA" & Month(Date) &"."&Year(Date)

'Obtain next free file handle number.
FileNum = FreeFile()

Upvotes: 1

Related Questions