VBScript to format the file and date .csv

Question

Hi I need help to set the file name as .csv currently we could set the file name as 2_10_2018user.csv with the below codes

Set CPY = CreateObject("Scripting.FileSystemObject")

CPY.CopyFile "\\users\user.csv", "\\home\group\" & Replace(FormatDateTime(Date(),2),"","-") & "users.csv",True

Set CPY = nothing
'MsgBox("Done")

one more question: does it works when you operate with file stored on local drive? in your sample your are operate with file on network drive — Zam, Feb 12 '19 at 18:10
Hi I need help to set the file name in bracket "<><>"as "filenamedate.csv" currently we could set the file name as 2_10_2018user.csv with the below codes Set CPY = CreateObject("Scripting.FileSystemObject") CPY.CopyFile "\\users\user.csv", "\\home\shared\group\" & Replace(FormatDateTime(Date(),2),"","-") & "users.csv",True Set CPY = nothing 'MsgBox("Done") it worked only I am concern about this line "Replace(FormatDateTime(Date(),2),"","-") & "users.csv",True" to rename the file — solmon, Feb 12 '19 at 18:12
It's not clear exactly what you want to do - do you want the name of the copied file to be filename then date? Please clarify with examples of what you have and what you want to see, by editing the question rather than as a comment where it is all but impossible to read your code statements. — Dave, Feb 12 '19 at 19:41

score 0 · Answer 1 · answered Feb 17 '19 at 02:53

You cannot use the ANGLE brackets in a file/folder name it is not permitted. In fact, The following characters are NOT permitted:

/ \ < > ? * | : "

The bit which you state concerns you:

Replace(FormatDateTime(Date(),2),"","-") & "users.csv",True"

Can be broken down into the following individual commands:

FormatDateTime(Date(),2)

This is telling the system to format the system date (date today) as a SHORT DATE The options for the second parameter are:

0 = vbGeneralDate (date and time)
1 = vbLongDate    (long date)
2 = vbShortDate   (short date)
3 = vbLongTime    (long time)
4 = vbShortTime   (short time)

So now we have the date format, Next is the replace action :

Replace(FormatDateTime(Date(),2),"","-") & "users.csv",True"

where we replace the date delimiters in this case "" (null string) with a hyphen "-" (-)

So this part of the filename changes from 010119 to 01-01-19 (for example 1st Jan 2019)

and "users.csv" is attached to the end of it all

Finally the TRUE comment tells the system to OVERWRITE the file if it already exists!

If you want the filename before the date then simply swap the bits over:

CPY.CopyFile "\\users\user.csv", "\\home\shared\group\" & "users-" & Replace(FormatDateTime(Date(),2),"","-") & ".csv",True

VBScript to format the file and date .csv

1 Answers1