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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

HalfAce

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

Forum statistics

Threads
1,140,941
Messages
5,703,292
Members
421,290
Latest member
java

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top