macro inlcude last businessday

[swamy567]

i am writing macro in attachment. grabing all date, rates and put it in text document
it is ok to show all dates and rate. but exclude last busines day. it shows including last business day.
how do i accomplish my task.
this is the main code
Sess0.Screen.Sendkeys("<HOME>")
Sess0.Screen.Sendkeys("GU<Tab>abcd,USD,")
Sess0.Screen.PutString "???",1,20
Sess0.Screen.PutString ConstStr,1,23
Sess0.Screen.PutString InputPriorToDate,1,26
Sess0.Screen.Sendkeys("<Tab>LKMN4<Enter>")

Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Print #1, InputPriorToDate$
'***********************************************************************
'*Get each currency's date and amount and print to QrtlyRateDate file. *
'***********************************************************************

ConstCurrCode = Sess0.Screen.GetString(4,67,3)
InputPriorToDate = format(CVdate(InputPriorToDate), "mm/dd/yyyy")

intRowNum=9
static strVar1PrevyearCheck
strVar1PrevyearCheck = (VAL(mid(InputPriorToDate,9,2))-1)
strVar1PrevyearCheck = 0 & strVar1PrevyearCheck
Do
For intRowNum = 9 to 22
'msgbox intRowNum
strErrormsg = Sess0.Screen.GetString(24,2,17)
If strErrormsg = "A205W-END OF LIST" Then
Print #1, "99999"
msgbox "Program Execution Completed."
'sess0.screen.WaitHostQuiet(g_HostSettleTime)
EXIT DO
Exit Sub
Else
StrCurrencyCode = Sess0.Screen.GetString(4,67,3)
'MSGBOX "StrCurrencyCode " & StrCurrencyCode & " ConstCurrCode " & ConstCurrCode
If StrCurrencyCode = ConstCurrCode Then
StrConcatenate = "CIBC1,CAD," & StrCurrencyCode & "S,"
strVar1$ = Sess0.Screen.GetString(intRowNum,11,30) '04/28/03 1.75000000 Effective Date & Conversion Rate
strVar1Date$ = DateValue(format(mid(strVar1$,1,8))) '04/28/03
strVar1Year = Mid(strVar1$,7,2) 'Year(strVar1Date$) '02 or 2002
strVarMarchMonth = Val(mid(InputPriorToDate,1,2))
strDecMonth = Val(mid(strVar1$,1,2))
strVar1generalNum = DateValue(format(strVar1Date$, "Short Date")) '4/28/2003
strInputPriorToDategeneralNum = DateValue(format(InputPriorToDate, "Short Date"))

intLengthInputPriorToDategeneralNum =len(strInputPriorToDategeneralNum)

If intLengthInputPriorToDategeneralNum =8 Then
strInputYear = Mid(STRInputPriorToDategeneralNum,7,2) '1/1/2003
ElseIf intLengthInputPriorToDategeneralNum =9 Then
strInputYear = Mid(STRInputPriorToDategeneralNum,8,2) '1/20/2003, 12/1/2003
ElseIf intLengthInputPriorToDategeneralNum =10 Then
strInputYear = Mid(STRInputPriorToDategeneralNum,9,2) '12/12/2003
End If
strDifference=val(strInputYear) - val(strVar1Year)
If strVarMarchMonth= 3 AND strDecMonth= 12 AND _
STRVAR1generalNum < STRInputPriorToDategeneralNum AND _
strVar1Year = strVar1PrevyearCheck Then
Print #1, StrConcatenate & strVar1
Else
If strVar1Date$ >= STRInputPriorToDategeneralNum AND strDifference >1 Then
Else
If STRVAR1generalNum < STRInputPriorToDategeneralNum _
AND strDifference =1 Then
Print #1, StrConcatenate & strVar1
End If
If STRVAR1generalNum < STRInputPriorToDategeneralNum _
AND strDifference =0 Then
Print #1, StrConcatenate & strVar1
End If
End If
End If
Else
Msgbox "New currency(StrCurrencyCode) code on screen is " + StrCurrencyCode + " Constant(old(ConstCurrCode)) is " + ConstCurrCode
ConstCurrCode = StrCurrencyCode
'Msgbox "Currency is new i.e. Constant(after swap(ConstCurrCode)) is " + ConstCurrCode
EXIT DO
End If
End If
Next intRowNum
Sess0.Screen.Sendkeys("<Home>")
Sess0.Screen.Sendkeys("<Pf8>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
ConstCurrCode = Sess0.Screen.GetString(4,67,3)
If StrCurrencyCode <> ConstCurrCode Then
Print #1, ""
End If
Loop

 

[SkipVought]

I wrote you a nifty compact macro to return the last workday in a month given a date in that month. Is it not working for you?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swamy567]

sorry skip it is not working. i have the above macrow which has given the text documnet including last business day . i need only except last business day.
thanks for your help.

 

[swamy567]

Skip
the above macro has checking from current year to last 10 years. but i need to check only current year and previous year . i did checked
year (now ) and
year (now)-1
but still it is beyond that checking.
could you help me

 

[SkipVought]

Tyr this function instead. I keep forgetting this is Extra Basic and not VBA...

function LastWorkday 

    LastWorkday = DateSerial(Year(date), Month(date) + 1, 1)-1

    Do
        iDay = Weekday(LastWorkday)
        Select Case iDay
            Case 2 To 6
                Exit do
        End Select
        LastWorkday = LastWorkday - 1
    Loop

End function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Try this instead...

Function LastWorkday(dte) 

    dt = DateSerial(Year(dte), Month(dte) + 1, 1) - 1
    
    LastWorkday = dt
    
    Do
        iDay = Weekday(dt)
        Select Case iDay
            Case 2 To 6
                Exit do
        End Select
        
        dt = dt - 1
    Loop
    
    LastWorkday = dt
    
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swamy567]

skip thanks
i am writing macro in attachmaent. so the above macro which i provided can i include yours and try.
and please i am so confused where do i have to includeyours.

 

[SkipVought]

I wrote the function in Attachmate Extra Basic and tested it, which I had not done before.

Check help on Functions. You must declare the function first...

Declare Function LastWorkday(dte)

Sub Main
   msgbox LastWorkday("11/01/08")
End Sub

Function LastWorkday(dte) 

    dt = DateSerial(Year(dte), Month(dte) + 1, 1) - 1
    
    LastWorkday = dt
    
    Do
        iDay = Weekday(dt)
        Select Case iDay
            Case 2 To 6
                Exit do
        End Select
        
        dt = dt - 1
    Loop
    
    LastWorkday = dt
    
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Yet another difference between Extra Basic and VBA. Must test for december dates...

Function LastWorkday(dte) [b]
    
    If Month(dte) = 12 then
        m = 1
        y = Year(dte) + 1
    Else
        m = Month(dte) + 1
        y = Year(dte) 
    End If

    dt = DateSerial(y, m, 1) - 1[/b]
    
    LastWorkday = dt
    
    Do
        iDay = Weekday(dt)
        Select Case iDay
            Case 2 To 6
                Exit do
        End Select
        
        dt = dt - 1
    Loop
    
    LastWorkday = dt
    
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swamy567]

Skip
thank you very much for your precious time. i am still struggling. i am trying to do from the screen.
Sess0.Screen.Sendkeys("<HOME>")
Sess0.Screen.Sendkeys("GU<Tab>abcd,USD,")
Sess0.Screen.PutString "???",1,20
Sess0.Screen.PutString ConstStr,1,23
Sess0.Screen.PutString InputPriorToDate,1,26
Sess0.Screen.Sendkeys("<Tab>LKMN4<Enter>")
the screen has form 9 to 22 effective date and rate
for example if i try
gu abcd,usd,???,>09/29/08 LKMN4
Here iam trying year current year and previous year., exclude last business day.
how do i try the abouve gu menu. could you help me

 

[SkipVought]

I have no idea on earth what you are trying to explain.

Your original question stated, "i am writing macro in attachment. grabing all date, rates and put it in text document
it is ok to show all dates and rate. but exclude last busines day."

I surmize that your dates are in "rows 9 to 22 effective date and rate." But you never disclosed what column, although it migh be buried in your code example.

You want to loop from 9 to 22 and then use the LastWorkday function to determine if the acquired date is LESS THAN the LastWorkday ...

'assign the column for EffDate
ColEffDate = ???
for row = 9 to 22
  EffDate = oSess.GetString(row, ColEffDate, 8)
  if DateValue(EffDate) < LastWorkday(DateValue(EffDate)) Then
     'now we have a WEEKDAY date that is less than the Last Workdate

  end if
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swamy567]

Skip
Again thanks a lot. i will try it tomorrow and get back to you.
thank you once agian.

 

[swamy567]

thanks a lot! it is working great.