Help adding an IF statement to my code..

help_questions

Board Regular
Joined
Aug 22, 2005
Messages
215
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello help_questions,
Perhaps something like this will get you started.
Code:
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

Board Regular
Joined
Aug 22, 2005
Messages
215
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
Code:
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

Board Regular
Joined
Aug 22, 2005
Messages
215

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,849
Messages
5,544,647
Members
410,627
Latest member
georgealice
Top