Swaink
03-03-2010, 09:59 AM
Hey im having trouble with this bit of code. Ideally it is supposed to print the info to an excel sheet for any days that the program missed. I get trapped in an infinite loop with my Do until loop and i think that transitioning this to a case statement might make things easier.
the code is as follows:
'Prints previous dates if program not ran. Excluding weekends
Do Until (DateAdd("d", 1, NewDate) = Date) 'Loops until current date
'Determines if date was missed and if it is a weekend day
If (Weekday(SentFailedLog.Fields("Date")) = (Today - 1)) Then
isYesterday = True
End If
If (Weekday(SentFailedLog.Fields("Date")) = 1) Then
isWeekend = True
ElseIf (Weekday(SentFailedLog.Fields("Date")) = 7) Then
isWeekend = True
End If
'If program missed running a day excel sheet is filled in w/ missed date
If (isYesterday = False And isWeekend = False) Then
'NewDate is date missed (Calculated by adding 1 to last date)
NewDate = DateAdd("d", 1, SentFailedLog.Fields("Date"))
With SentFailedLog
.AddNew
.Fields("Date") = NewDate
.Fields("Cycle") = -35
.Fields("Compact&Repair") = "No"
.Fields("File Size") = File_Size_MB
.Fields("F8") = "PROGRAM NOT RAN"
.Update
End With
SentFailedLog.MoveLast
End If
Loop
any help with pseudo code would be awesome!
Thanks
Edit: VBA tags added to code. You can format your code for the forum by selecting it when posting and hitting the VBA button.
the code is as follows:
'Prints previous dates if program not ran. Excluding weekends
Do Until (DateAdd("d", 1, NewDate) = Date) 'Loops until current date
'Determines if date was missed and if it is a weekend day
If (Weekday(SentFailedLog.Fields("Date")) = (Today - 1)) Then
isYesterday = True
End If
If (Weekday(SentFailedLog.Fields("Date")) = 1) Then
isWeekend = True
ElseIf (Weekday(SentFailedLog.Fields("Date")) = 7) Then
isWeekend = True
End If
'If program missed running a day excel sheet is filled in w/ missed date
If (isYesterday = False And isWeekend = False) Then
'NewDate is date missed (Calculated by adding 1 to last date)
NewDate = DateAdd("d", 1, SentFailedLog.Fields("Date"))
With SentFailedLog
.AddNew
.Fields("Date") = NewDate
.Fields("Cycle") = -35
.Fields("Compact&Repair") = "No"
.Fields("File Size") = File_Size_MB
.Fields("F8") = "PROGRAM NOT RAN"
.Update
End With
SentFailedLog.MoveLast
End If
Loop
any help with pseudo code would be awesome!
Thanks
Edit: VBA tags added to code. You can format your code for the forum by selecting it when posting and hitting the VBA button.