Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simplify save code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify save code
Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 149
Default Simplify save code
DavidH56,

Try something along the lines of what is listed below. (The code is
untested).

Best,

Matthew Herbert

Dim strFName As String

Select Case Weekday(Now())
Case 1, 7
strFName = "C:\Daily Report\Archives\DAILY REPORT" & Format(Date,
"dd-mmm-yyyy") & ".xls"
Case 2 To 6
strFName = "C:\Daily Report\" & Format(Date, "dddd") & "\DAILY
REPORT" & Format(Date, "dd-mmm-yyyy") & ".xls"
End Select

ActiveWorkbook.SaveAs Filename:=strFName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify save code
Thanks Matthew for such a quick response. This is exactly what I was looking
for. Works great. Thanks a million once again.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Matthew Herbert" wrote:

DavidH56,

Try something along the lines of what is listed below. (The code is
untested).

Best,

Matthew Herbert

Dim strFName As String

Select Case Weekday(Now())
Case 1, 7
strFName = "C:\Daily Report\Archives\DAILY REPORT" & Format(Date,
"dd-mmm-yyyy") & ".xls"
Case 2 To 6
strFName = "C:\Daily Report\" & Format(Date, "dddd") & "\DAILY
REPORT" & Format(Date, "dd-mmm-yyyy") & ".xls"
End Select

ActiveWorkbook.SaveAs Filename:=strFName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Simplify save code
Try the below

Private Sub Save_DAILY_REPORT()
ActiveWorkbook.SaveAs Filename:="C:\Daily Report\" & _
IIf(Left(Format(Date - 5, "ddd"), 1) = "S", "Archives", _
Format(Date, "dddd")) & "\DAILY REPORT" & _
Format(Date, "dd-mmm-yyyy") & ".xls", FileFormat:=xlNormal
End If

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify save code
Thanks for your quick response Jacob. I first got the end if block error. I
changed spelling for the first if. I now get syntax error.

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jacob Skaria" wrote:

Try the below

Private Sub Save_DAILY_REPORT()
ActiveWorkbook.SaveAs Filename:="C:\Daily Report\" & _
IIf(Left(Format(Date - 5, "ddd"), 1) = "S", "Archives", _
Format(Date, "dddd")) & "\DAILY REPORT" & _
Format(Date, "dd-mmm-yyyy") & ".xls", FileFormat:=xlNormal
End If

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Simplify save code
Just a note for the OP edification. IIf() is a good VBA syntax.

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)



"DavidH56" wrote in message
...
Thanks for your quick response Jacob. I first got the end if block error.
I
changed spelling for the first if. I now get syntax error.

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jacob Skaria" wrote:

Try the below

Private Sub Save_DAILY_REPORT()
ActiveWorkbook.SaveAs Filename:="C:\Daily Report\" & _
IIf(Left(Format(Date - 5, "ddd"), 1) = "S", "Archives", _
Format(Date, "dddd")) & "\DAILY REPORT" & _
Format(Date, "dd-mmm-yyyy") & ".xls", FileFormat:=xlNormal
End If

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for
simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Simplify save code
Did you copy/paste the code? or modified.

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Thanks for your quick response Jacob. I first got the end if block error. I
changed spelling for the first if. I now get syntax error.

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jacob Skaria" wrote:

Try the below

Private Sub Save_DAILY_REPORT()
ActiveWorkbook.SaveAs Filename:="C:\Daily Report\" & _
IIf(Left(Format(Date - 5, "ddd"), 1) = "S", "Archives", _
Format(Date, "dddd")) & "\DAILY REPORT" & _
Format(Date, "dd-mmm-yyyy") & ".xls", FileFormat:=xlNormal
End If

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 82
Default Simplify save code
I copy and pasted your code. When copied as is I get compile error at Enf If
saying End If without Block If.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jacob Skaria" wrote:

Did you copy/paste the code? or modified.

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Thanks for your quick response Jacob. I first got the end if block error. I
changed spelling for the first if. I now get syntax error.

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jacob Skaria" wrote:

Try the below

Private Sub Save_DAILY_REPORT()
ActiveWorkbook.SaveAs Filename:="C:\Daily Report\" & _
IIf(Left(Format(Date - 5, "ddd"), 1) = "S", "Archives", _
Format(Date, "dddd")) & "\DAILY REPORT" & _
Format(Date, "dd-mmm-yyyy") & ".xls", FileFormat:=xlNormal
End If

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Simplify save code
In looking at Jacob's code quickly, it appears he added the End If statement
by mistake. Copy/Paste the code he posted but leave off the End If statement
and I think his code will then work for you (although I should say that did
not directly test his code).

--
Rick (MVP - Excel)


"DavidH56" wrote in message
...
I copy and pasted your code. When copied as is I get compile error at Enf
If
saying End If without Block If.
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jacob Skaria" wrote:

Did you copy/paste the code? or modified.

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Thanks for your quick response Jacob. I first got the end if block
error. I
changed spelling for the first if. I now get syntax error.

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jacob Skaria" wrote:

Try the below

Private Sub Save_DAILY_REPORT()
ActiveWorkbook.SaveAs Filename:="C:\Daily Report\" & _
IIf(Left(Format(Date - 5, "ddd"), 1) = "S", "Archives", _
Format(Date, "dddd")) & "\DAILY REPORT" & _
Format(Date, "dd-mmm-yyyy") & ".xls", FileFormat:=xlNormal
End If

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for
simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Simplify save code
The first "if" was not spelled wrong... IIf is a valid function. You could
look it up in the VB help files, but here is an online link to the VB help
file for this function...

http://msdn.microsoft.com/en-us/libr...24(VS.60).aspx

--
Rick (MVP - Excel)


"DavidH56" wrote in message
...
Thanks for your quick response Jacob. I first got the end if block error.
I
changed spelling for the first if. I now get syntax error.

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



"Jacob Skaria" wrote:

Try the below

Private Sub Save_DAILY_REPORT()
ActiveWorkbook.SaveAs Filename:="C:\Daily Report\" & _
IIf(Left(Format(Date - 5, "ddd"), 1) = "S", "Archives", _
Format(Date, "dddd")) & "\DAILY REPORT" & _
Format(Date, "dd-mmm-yyyy") & ".xls", FileFormat:=xlNormal
End If

If this post helps click Yes
---------------
Jacob Skaria


"DavidH56" wrote:

Hi,

Thanks all of you experts for being such a great help in solving my
programming issues. I would like to know the best method for
simplyfying this
bit of code:

Private Sub Save_DAILY_REPORT()
'TO SAVE REPORT FILE
If Weekday(Now()) = 1 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If

If Weekday(Now()) = 2 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Monday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 3 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Tuesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 4 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Wednesday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If

If Weekday(Now()) = 5 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Thursday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 6 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Friday\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


End If

If Weekday(Now()) = 7 Then _

ActiveWorkbook.SaveAs Filename:= _
"C:\Daily Report\Archives\DAILY REPORT" & Format(Date, "
dd-mmm-yyyy") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End If


Thanks in advance for your help.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplify code Inkel Excel Worksheet Functions 3 March 27th 09 05:52 PM
simplify code C02C04 Excel Programming 5 February 4th 09 01:50 PM
Simplify Code help DavidH56 Excel Programming 14 September 23rd 08 11:37 PM
Please help me simplify the given code Bob Phillips Excel Programming 6 November 13th 07 07:28 PM
Simplify this code Scott Excel Programming 2 February 8th 06 03:56 AM


All times are GMT +1. The time now is 09:48 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement