Append Date

y2k

Board Regular
Joined
Feb 25, 2002
Messages
133
Does anybody know how to use VBA to save a file with with date appended onto the end? Eg save "file1" as "file1 01-01-02"? If necessary the date can be entered into one of the fields in the spreadsheet.
This message was edited by y2k on 2002-03-05 07:30
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
ActiveWorkbook.SaveAs Filename:="file1 " & Date & ".xls"

Will save the file with todays date

ActiveWorkbook.SaveAs Filename:="file1 " & Range(Date) & ".xls"

Will save the file with the date stored in the range "Date" on the worksheet
This message was edited by Steve Hartman on 2002-03-05 07:38
 
Upvote 0
If the date is in Cell A1 for example,
then just get that in to a variable...

myDate = Range("A1").Value

And then save your file:

ActiveWorkbook.SaveAs Filename:="File 1 " & mydate & ".xls"
 
Upvote 0
Help... it's not working! What am I doing wrong? When I run the macro, I get an error message saying it can't access the file "C:file1 05
 
Upvote 0
"C:\file1 05\03"

Are the double slashes actually in your code or is it just the way it shows up on this board? If you do have double slashes there try it with single slashes.
This message was edited by Steve Hartman on 2002-03-05 08:15
 
Upvote 0
I think it's the fact the the & Date & produces a date with a slash as the day/month delimiter and you can't have slashes in your filenames.

The following worked for me. Write the date to a cell, pick up it's components and then save (substitute in your own path)...

--
Range("A1").Formula = Now()
Range("B1").FormulaR1C1 = "=DAY(RC[-1])"
Range("C1").FormulaR1C1 = "=MONTH(RC[-2])"
Range("D1").FormulaR1C1 = "=YEAR(RC[-3])"

MyDay = Range("B1").Value
MyMonth = Range("C1").Value
MyYear = Range("D1").Value

ChDir ("C:temp")

ActiveWorkbook.SaveAs Filename:="All_claims " & MyDay & "-" & MyMonth & "-" & MyYear & ".xls"

--
This called my file
"All_claims 5-2-2002.xls"
It ain't elegant, but it works.

Rgds
AJ
 
Upvote 0
no... the double slashes are the way the board is showing it. I've only gone single slashes, excep at the beginning of the UNC, but that's supposed to have 2 slashes
 
Upvote 0
AJ is right, the slash in the filename is the problem.

A slightly neater way would be:

ActiveWorkbook.SaveAs Filename:="All_claims " & Month(Date) & Day(Date) & Year(Date) & ".xls"

to save as todays date
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top