Any way to autosave new file with creation date as filename?

[vk2003]

Recently started using excel, and have started to realised just ho
powerful it is. I have found the help files extremely useful, and hav
managed to do pretty much all I wanted bar 2 things. The main problem
have been unable to solve is:

I have created a workbook template which contains 6 worksheets - on
for each of Monday-Friday, and a summary sheet. The daily sheets ar
used to input sales figures for 5 members of staff.

What I would like to happen is:

When the new workbook is created at the start of a week (monday), th
relevant date is entered for each day (eg 1 Jan 2005) into the top lef
cell of each worksheet.

Then the file autosaves with the filename being the date of the monda
(eg 01Jan05.xls) for 1st January 2005 assuming it were a Monday. Thi
filename would obviously remain unchanged as the week progresses.

This however needs to be able to cope with the fact that due to Ban
Holidays, the workbook may not necessarily be created on a Monday.

Can anyone suggest how this can be done? Thanks very much in advance

 

[David McRitchie]

see http://www.mvps.org/dmcritchie/excel/backup.htm#demand
look at macro backupBYDATE. I think you want the filename to
show the date and be able to be sorted in a directory by name.
dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd")

Since you want Monday's date instead of using NOW() you might
use Now() - (Now() - 3) Mod 7 in place of Now()
to adjust the date back to a Monday.
dname = "c:\mybackup\B" & Format(Now() - (Now() - 3) Mod 7, "yyyy_mmdd")

Assuming that you want to create the workbook with a Monday
date regardless of being a workday or in the same month as the
rest of the week days. All you you need to do is calculate
the Monday date on a given day. Since it is a macro you
would need macro code. There is a test for if the file already
exists or not in the backupBYDATE macro.

Hopefully this points you in the correct direction.

I don't know what you already have or don't have, or even if you
are using a macro. you can put that Monday date into
the Monday sheet or perhaps just name the sheets by their date.

 

[vk2003]

David

Thanks for the posting. I must admit to flailing in the wind a little
as I have no experience of macros/VBA. I have been reading som
tutorials, but since I have next to no programming experience, it is
bit of an uphill struggle.

A Gentleman in another forum posted some code which should (I can se
from logically working through it in my head) do all that I ask, but
really can't get it to work. This is most likely due to the fact that
am doing something wrong when entering it ('link here to forum posting
(http://www.mrexcel.com/board2/viewtopic.php?p=860713#860713) )

I like the fact that your code checks for an existing file, and als
places them in appropriate directories, but must admit to bein
completely out of my depth as to (A) How to get it to work, and (B
Amalgamate with your code!

Can you help any further please?

Regards

Vishal...
"And all I want is the Moon on a stick!!

 

[vk2003]

'assumes this code is in your template workbook VBA in ThisWorkbook
object and
'template has named range for myIsCopy,
'template has named range for SaveAsDate and (SaveAsDate is cell a1 on
sheet Mon)
'range myIsCopy is = 0 on template workbook.
'Sheet1 named Mon
'Cells on subsequent sheets, Sheet 2 named Tue, sheet 3 named Wed
'Tue Cell A1 =Mon!A1+1
'Wed Cell A1 =Mon!A1+2
'etc Good Luck, Chas Weber

Private Sub Workbook_Open()
If Range("myIsCopy") = 0 Then
myDate = Date
If Weekday(myDate) <> 2 Then myDate = Date + 9 - Weekday(Date)

myDate = Format(myDate, "dd-mmm-yyyy")
Application.Worksheets("Mon").Range("SaveAsDate") = myDate

x = "C:\" & myDate & ".xls"

Range("myIsCopy") = 1
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:=x
End If
End Sub

OK
So I was being really dense, and was defining MyIsCopy as multiple
cells. Looking through the code again, I realised after much head
scratching that I had actually not followed your excellent directions
Chas in asmuch as it actually needs to be a single cell with the value
0

When it first ran, it did so smoothly.
BUT (isn't there always one?!)

I ran some simulations:

A) With the date today being Sunday 13Nov2005, on opening a document
from the template, the monday date (and filename) became 21Nov2005.

B) With todays date being set to Monday 14thNovember, the filename etc
was perfect

C) With todays date being set to Tuesday 15thNovember 2005, the Monday
date and filename again became 21Nov2005 (this is the most likely error
in my case as there are often Bank Holiday Mondays, which would mean the
first working day of the week becomes Tuesday, and therefore the day on
which the new file would be spawned fom the template.

Is it possible to modify the date calculation to ensure that in the
above simulation, and with the week as follows for example:

Sunday 13th
Monday 14th
Tuesday 15th
Wednesday 16th
Thursday 17th
Friday 18th
Saturday 19th
Sunday 20th
Monday 21st

A file spawned from the template between 13th-19th (inclusive) should
give a Monday date/filename of 14th.
But if the file spawned on 20th, then it dates/names for the 21st..
I hope this is making sense!

Any suggestions?
Thank you again.

 

[David McRitchie]

You can use either of these formulas for your calculated start date
so that Sunday goes to Monday.

mydate = date - (date - 1) Mod 7 + 1
or
mydate = date - Weekday(date) + 2