Adding Today's Date to a Workbook Using VBA

[Luis_B]

Hi Guys,

I need help adding a date to a Workbook that I create using VBA. Below is the code that I'm using to create the workbook and to save it to CSV. It there a way to add a formula to this code to add the date to the actual name of the file? so the name of the file would look something like this "WK1-401K081022"

'Create a new file that will receive the data
Set newWB = Workbooks.Add
With newWB
Set newS = newWB.Sheets("Sheet1")
newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
newWB.Sheets("Sheet1").Range("B:B").NumberFormat = "m/d/yyyy"

'Save in CSV
Application.DisplayAlerts = False
.SaveAs Filename:="C:\DOWNLOADS\WK1-401K.csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
Workbooks("WK1-401K").Close SaveChanges:=True 'SaveAndCloseExcelSheet

Thank you, I appreciate any help

 

[davesexcel]

"C:\DOWNLOADS\WK1-401K.csv"

tRY

"C:\DOWNLOADS\WK1-401K" & FORMAT(NOW,"MMDDYY") & ".csv"

 

[Luis_B]

tRY

"C:\DOWNLOADS\WK1-401K" & FORMAT(NOW,"MMDDYY") & ".csv"

Thank you Dave, that worked great. Would you be able to show me how to close the workbook after it is created with the date in the name? I tried the code below but it doesn't work

Workbooks("WK1-401K"& Format(Now, "MMDDYY").Close SaveChanges:=True 'SaveAndCloseExcelSheet

The entire code looks like this after I applied your adjustment

Application.DisplayAlerts = False
.SaveAs Filename:="C:\DOWNLOADS\WK1-401K" & Format(Now, "MMDDYY") & ".csv"
Application.DisplayAlerts = True
Workbooks("WK1-401K" & Format(Now, "MMDDYY").Close SaveChanges:=True 'SaveAndCloseExcelSheet

Thank you,

 

[davesexcel]

.close

I presume that is all you need

 

[Luis_B]

.close

I presume that is all you need

Thank you Dave. I appreciate your help. I was missing a closing parenthesis. Everything works great now. Have an awesome day

 

[davesexcel]

I thinking, since you already saved newWB, all you need is .close to close the workbook, before your end with, no need for the workbook().close save stuff.

 

[Luis_B]

I thinking, since you already saved newWB, all you need is .close to close the workbook, before your end with, no need for the workbook().close save stufHello Dave,

Hi Dave,

I have been getting this message below when I open the files. Do you happened to know how to fix this issue. I would need to save the file to CSV

Thank you,

Set newWB = Workbooks.Add
With newWB
Set newS = newWB.Sheets("Sheet1")
newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
newWB.Sheets("Sheet1").Range("B:B").NumberFormat = "m/d/yyyy"

'Save in CSV
Application.DisplayAlerts = False
.SaveAs Filename:="C:\DOWNLOADS\WK1-401K" & Format(Now, "MMDDYY") & ".csv"
Application.DisplayAlerts = True
Workbooks("WK1-401K" & Format(Now, "MMDDYY")).Close SaveChanges:=True 'SaveAndCloseExcelSheet


End With

 

[davesexcel]

When saving a sheet as csv, you would need to tell it the csv Fileformat.

If you use the macro recorder, you will see what I mean.

For example:

    ActiveWorkbook.SaveAs Filename:="C:\Users\dmorrison\Downloads\MyBook.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False

 

[Luis_B]

When saving a sheet as csv, you would need to tell it the csv Fileformat.

If you use the macro recorder, you will see what I mean.

For example:

    ActiveWorkbook.SaveAs Filename:="C:\Users\dmorrison\Downloads\MyBook.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False

I forgot about using the macro recorder. Thank you again for your help. That fixed my problem. Have an awesome day