Help adding an IF statement to my code..

[help_questions]

THis is the code that I use to save my file:

ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\September_22_2006_11.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close


I want to add an if statement to the code that will do the following:

If the current time is between 9am and 12pm, save the file as C:\Reports\Report1
If the current time is between 12:01pm and 3pm, save the file as C:\Reports\Report2
If the current time is between 3:01pm and 7pm, save the file as C:\Reports\Report3
If the current time is between 7:01pm and 11:59pm, save the file as C:\Reports\Report4

Any help with this would be greatly appreciated.

Thanks

 

[HalfAce]

Hello help_questions,
Perhaps something like this will get you started.

Dim StrReport As String, fPath As String
StrReport = "Reports\Report4"

If Time < 0.791666667 Then StrReport = "Reports\Report3" 'earlier than 7:01 PM
If Time < 0.625694444 Then StrReport = "Reports\Report2" 'earlier than 3:01 PM
If Time < 0.500694444 Then StrReport = "Reports\Report1" 'earlier than 12:01 PM

fPath = "C:\" & StrReport

ChDir "C:\"
ActiveWorkbook.SaveAs Filename:=fPath & "\September_22_2006_11.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close

Note that it assumes the folder 'Reports' and within that the subfolders 'Report1,
Report2, Report3 & Report4' do in fact exist on the C drive so there's no error trapping
for that. It also doesn't address the workbook's SaveAs name being that particular date,
and if you're saving over an existing file then you'll want to turn off the DisplayAlerts
as well.

 

[help_questions]

Thanks for your post HalfAce.....I just want to clarlify my question....

If the current time is between 9am and 12pm, save the file as C:\Reports\Report1.xls
If the current time is between 12:01pm and 3pm, save the file as C:\Reports\Report2.xls
If the current time is between 3:01pm and 7pm, save the file as C:\Reports\Report3.xls
If the current time is between 7:01pm and 11:59pm, save the file as C:\Reports\Report4.xls

any ideas?

 

[HalfAce]

OK, well those last file paths aren't the same as the first ones you posted so let me
make sure I understand.
You want to SaveAs to the file path:
C:\Reports\
and then name the file 'Report1, Report2, Report3 or Report4' depending on the time?
(- Is that right?) If so then try this.

Dim fName As String, fPath As String
fPath = "C:\Reports\"
fName = "Report4.xls"
If Time < 0.791666667 Then fName = "Report3.xls" 'earlier than 7:01 PM
If Time < 0.625694444 Then fName = "Report2.xls" 'earlier than 3:01 PM
If Time < 0.500694444 Then fName = "Report1.xls" 'earlier than 12:01 PM

Application.DisplayAlerts = False
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:=fPath & fName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.Close

 

[help_questions]

Thanks halface...this works great....
I figure that the decimals that you are using to evaluate the current time is the percent of each time period over 24 hours. It makes sense once you understand. Since I am new to VBA, I'd like to know why you used the decimals (% of day) instead of the conventional time format....

Thanks again for your help and valuable line of code....

 

[HalfAce]

You're most welcome. Glad it helped.
Yes, those decimal numbers depict certain times of the day. Excel views a 24 hour
period as 1 so any time between midnight & midnight is going to be something less
than 1. For example:
6:AM is 0.25
12:Noon is 0.5
6:PM is 0.75, etc.

To figure out the value for any given time of day you can do this.
In A1 enter 6:05 AM
In B1 enter =A1 and format that as Number (with 6 or more decimal places.)
You should end up with the value 0.253472
To verify, in C1 enter =B1 and format that as Time (1:30:55 PM)

Another way... (say to find the value for 12:15 AM)
In A2 enter =15*(1/1440)
You should end up with the value 0.010417
In B2 enter =A2 and format that as Time (1:30:55 PM)

That help any?

 

[help_questions]

Yes...this makes sense. Thanks for your help !!