I'm a temp employee for a company. My job has been to collect data and
compile it for presentation. My project was supposed to be done on
August 31st, but the company is so happy with the system I've developed
for the project that they've decided to continue it (yet I'm leaving
the job regardless of whether they want me to stay or not) They've
asked me to make my spreadsheet "dumb" proof for the person taking over
the data collection and I need help because its not something I
envisioned ever needing to do. I have a lot of the basics already
accomplished (for example the only way to input information is through
forms) but, as i've been informed that the person taking over has
limited computer skills, i need to account for as many situations as
possible so i'm just going to start with the first thing that comes to
mind:

I to be able to see backups going back at least two saves (3 more
realistically) but, i also need there to only exist a small number of
backups (ideally only going back 4 saves for example) I need the name
to reflect how new the backup is (thinking best way might be to have
the backup file name be based off time/date)

If there is already 4 backups available, the oldest one is deleted when
a new one is created (to save drive space)

I need to create a template version of my project (for each new month)
that upon openning saves itself as the name of the next month (assuming
excel can see the names of other files) along with the year.

The backups do not need to account for the month (assuming that the
person would at least be smart enough to know what month they are
currently working with) just 4 backups total, not 4 per month

I need not allow the dates within a spreadsheet to exceed the month (a
simple message box that says incorrect month would suffice)

This is all i can think of at the moment (these are just the things i
know off the top of my head are going to be necessary)

Other situations that i will need to tackle expand upon the template
(such as filling out the schedule being required as the first step)

I realize that this may seem in poor taste to ask for so much help, but
from my side, i get paid the same ammount regardless of how many hours
i put in, but it has to be done (so, either i ask for help or i start
putting in 16+ hour days instead of the 12 hour days i'm arlready
managing)

any and all help is welcome and appreciated (for example if you know
how to approach any of the situations or complications with applying
some of these ideas, thats more than nothing)

feel free to post or email questions if you're interested in helping me

As i tackle issues on my own, i'll be posting here to update progress

thanks for all the help i've gotten thus far

Dim cnt as Long, s as String
cnt = 0
do
s = "C:\Backups\Matt*.xls"
cnt = DeleteOldest( s)
Loop while cnt 3
Thisworkbook.SaveCopyAs "C:\Backups\Matt_" & format(now,"yyyymmdd_hh_mm").xls

End Sub



Public Function DeleteOldest(testString as String)
Dim sName as String
Dim cnt as Long
Dim dt as Date
sName = dir(testString)
do while sName < ""
cnt = cnt + 1
dt = Parsename( sName)
if dt < oldestDate then
sOldName = sName
oldestDate = dt
end if
sName = dir()
Loop
if cnt 4 then
Kill "C:\Backups\" & sOldName
cnt = cnt -1
end if
DeleteOldest = cnt
End Function


Public Function ParseName(sName As String) As Date
Dim yr As Long, mth As Long, dy As Long
Dim hr As Long, min As Long, iloc As Long
iloc = InStr(1, sName, ".xls", vbTextCompare)
s = Left(sName, iloc - 1)
s = Right(s, 14)
yr = Left(s, 4)
mth = Mid(s, 5, 2)
dy = Mid(s, 7, 2)
hr = Mid(s, 10, 2)
min = Mid(s, 13, 2)
ParseName = DateSerial(yr, mth, dy) + TimeSerial(hr, min, 0)
End Function

--
Regards,
Tom Ogilvy


"Matt" wrote:

I'm a temp employee for a company. My job has been to collect data and
compile it for presentation. My project was supposed to be done on
August 31st, but the company is so happy with the system I've developed
for the project that they've decided to continue it (yet I'm leaving
the job regardless of whether they want me to stay or not) They've
asked me to make my spreadsheet "dumb" proof for the person taking over
the data collection and I need help because its not something I
envisioned ever needing to do. I have a lot of the basics already
accomplished (for example the only way to input information is through
forms) but, as i've been informed that the person taking over has
limited computer skills, i need to account for as many situations as
possible so i'm just going to start with the first thing that comes to
mind:

I to be able to see backups going back at least two saves (3 more
realistically) but, i also need there to only exist a small number of
backups (ideally only going back 4 saves for example) I need the name
to reflect how new the backup is (thinking best way might be to have
the backup file name be based off time/date)

If there is already 4 backups available, the oldest one is deleted when
a new one is created (to save drive space)

I need to create a template version of my project (for each new month)
that upon openning saves itself as the name of the next month (assuming
excel can see the names of other files) along with the year.

The backups do not need to account for the month (assuming that the
person would at least be smart enough to know what month they are
currently working with) just 4 backups total, not 4 per month

I need not allow the dates within a spreadsheet to exceed the month (a
simple message box that says incorrect month would suffice)

This is all i can think of at the moment (these are just the things i
know off the top of my head are going to be necessary)

Other situations that i will need to tackle expand upon the template
(such as filling out the schedule being required as the first step)

I realize that this may seem in poor taste to ask for so much help, but
from my side, i get paid the same ammount regardless of how many hours
i put in, but it has to be done (so, either i ask for help or i start
putting in 16+ hour days instead of the 12 hour days i'm arlready
managing)

any and all help is welcome and appreciated (for example if you know
how to approach any of the situations or complications with applying
some of these ideas, thats more than nothing)

feel free to post or email questions if you're interested in helping me

As i tackle issues on my own, i'll be posting here to update progress

thanks for all the help i've gotten thus far

Wow thanks Tom, far more than i was expecting, you're awesome
Tom Ogilvy wrote:
Dim cnt as Long, s as String
cnt = 0
do
s = "C:\Backups\Matt*.xls"
cnt = DeleteOldest( s)
Loop while cnt 3
Thisworkbook.SaveCopyAs "C:\Backups\Matt_" & format(now,"yyyymmdd_hh_mm").xls

End Sub



Public Function DeleteOldest(testString as String)
Dim sName as String
Dim cnt as Long
Dim dt as Date
sName = dir(testString)
do while sName < ""
cnt = cnt + 1
dt = Parsename( sName)
if dt < oldestDate then
sOldName = sName
oldestDate = dt
end if
sName = dir()
Loop
if cnt 4 then
Kill "C:\Backups\" & sOldName
cnt = cnt -1
end if
DeleteOldest = cnt
End Function


Public Function ParseName(sName As String) As Date
Dim yr As Long, mth As Long, dy As Long
Dim hr As Long, min As Long, iloc As Long
iloc = InStr(1, sName, ".xls", vbTextCompare)
s = Left(sName, iloc - 1)
s = Right(s, 14)
yr = Left(s, 4)
mth = Mid(s, 5, 2)
dy = Mid(s, 7, 2)
hr = Mid(s, 10, 2)
min = Mid(s, 13, 2)
ParseName = DateSerial(yr, mth, dy) + TimeSerial(hr, min, 0)
End Function

--
Regards,
Tom Ogilvy


"Matt" wrote:

I'm a temp employee for a company. My job has been to collect data and
compile it for presentation. My project was supposed to be done on
August 31st, but the company is so happy with the system I've developed
for the project that they've decided to continue it (yet I'm leaving
the job regardless of whether they want me to stay or not) They've
asked me to make my spreadsheet "dumb" proof for the person taking over
the data collection and I need help because its not something I
envisioned ever needing to do. I have a lot of the basics already
accomplished (for example the only way to input information is through
forms) but, as i've been informed that the person taking over has
limited computer skills, i need to account for as many situations as
possible so i'm just going to start with the first thing that comes to
mind:

I to be able to see backups going back at least two saves (3 more
realistically) but, i also need there to only exist a small number of
backups (ideally only going back 4 saves for example) I need the name
to reflect how new the backup is (thinking best way might be to have
the backup file name be based off time/date)

If there is already 4 backups available, the oldest one is deleted when
a new one is created (to save drive space)

I need to create a template version of my project (for each new month)
that upon openning saves itself as the name of the next month (assuming
excel can see the names of other files) along with the year.

The backups do not need to account for the month (assuming that the
person would at least be smart enough to know what month they are
currently working with) just 4 backups total, not 4 per month

I need not allow the dates within a spreadsheet to exceed the month (a
simple message box that says incorrect month would suffice)

This is all i can think of at the moment (these are just the things i
know off the top of my head are going to be necessary)

Other situations that i will need to tackle expand upon the template
(such as filling out the schedule being required as the first step)

I realize that this may seem in poor taste to ask for so much help, but
from my side, i get paid the same ammount regardless of how many hours
i put in, but it has to be done (so, either i ask for help or i start
putting in 16+ hour days instead of the 12 hour days i'm arlready
managing)

any and all help is welcome and appreciated (for example if you know
how to approach any of the situations or complications with applying
some of these ideas, thats more than nothing)

feel free to post or email questions if you're interested in helping me

As i tackle issues on my own, i'll be posting here to update progress

thanks for all the help i've gotten thus far