E
E.Q.
I have employees that open up five monthly reports each night after midnight
numbers are collected for the day. The reports names have the format of
yy-mmrpt.xls. So for example two reports for Jan '08 would be 08-01tss.xls
and 08-01po4.xls. We have reports dating back to 2001.
I amended the userform used to select the files by adding a spinner to
select the year and gave it a range of 2001 - current year + 1. The form's
Activate event initializes the spinner to the current year and sets the
spinner max. (Before adding the spinner, someone needed to enter VBA and
change a string variable to select the year.) The month and report id are
picked by radio buttons and check boxes respectively
I need to add some error handling code for this (they've created a set of
files for 2008, but the spinner will choose future files that have yet to be
created after new year's day).
I've thought a simple msgbox "file not found" in the error code would work,
but a more elegant solution would be to check which files exists and disable
the check box for any report if no report exist for the implied month (via
radio button) and year (via spinner). I actually have a similar problem for
earlier years... the first few years we had four reports. So I have a
07-01SHF.xls file but I don't have a 04-01SHF.xls file.
So in short, I'd like to find out if a file exists via VBA and prevent the
user from asking for it if it ain't there.
Any suggestions would be appreciated.
Peace.
EQ
numbers are collected for the day. The reports names have the format of
yy-mmrpt.xls. So for example two reports for Jan '08 would be 08-01tss.xls
and 08-01po4.xls. We have reports dating back to 2001.
I amended the userform used to select the files by adding a spinner to
select the year and gave it a range of 2001 - current year + 1. The form's
Activate event initializes the spinner to the current year and sets the
spinner max. (Before adding the spinner, someone needed to enter VBA and
change a string variable to select the year.) The month and report id are
picked by radio buttons and check boxes respectively
I need to add some error handling code for this (they've created a set of
files for 2008, but the spinner will choose future files that have yet to be
created after new year's day).
I've thought a simple msgbox "file not found" in the error code would work,
but a more elegant solution would be to check which files exists and disable
the check box for any report if no report exist for the implied month (via
radio button) and year (via spinner). I actually have a similar problem for
earlier years... the first few years we had four reports. So I have a
07-01SHF.xls file but I don't have a 04-01SHF.xls file.
So in short, I'd like to find out if a file exists via VBA and prevent the
user from asking for it if it ain't there.
Any suggestions would be appreciated.
Peace.
EQ