CODEWITHSUNDEEP
vbaexcel
The Gootch
The Gootch

Reputation: 85

Automatically Save File as Text With Friday's Date

I recorded a macro that at the end saves the workbook as a text file and closes.

I would like to change the file name to Friday's date so that every week when I save it, the file name is different.

This is what the macro recorded

ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\username\Desktop\Temp\\File2218.txt" _
    , FileFormat:=xlText, CreateBackup:=False

I tried to change it to 

ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\username\Desktop\Temp\File" & Text(Today()+2, "mdyy")& txt" _
    , FileFormat:=xlText, CreateBackup:=False

(The reason I did +2 is because it's always done one Wed, therefore +2 = Friday)

But it doesn't work.

Any help would be greatly appreciated!

Upvotes: 0

Views: 289

Answers (1)

braX
braX

Reputation: 11755

Does the C:\Users\username folder actually exist? You will probably have to change username to your own username, or use Environ$("Username") like this:

"C:\Users\" & Environ$("Username") & "\Desktop\Temp\File"

And make sure the folder exists too, or you will get another error.

Also, use DateAdd() to add days:

NewDate = DateAdd("d", 2, Date)

And Format$() to format it (Text() is more of a worksheet function)

Once completed, you end up with this:

ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\" & Environ$("Username") & "\Desktop\Temp\File" & _
     Format$(DateAdd("d", 2, Date), "mdyy") & ".txt" _
    , FileFormat:=xlText, CreateBackup:=False

Upvotes: 2

Related Questions