How to get previous date 1

[ram567]

Hi!
anyone help me the below request:
the report has main menu
date applivation decription
====================================
1/11/08 Accounts M&C
=====================================
1. 1/11/08 M&C details 40
2. 1/11/08 M&C Accounts 6

how do I GO THIS MENU
FROM MAIN MENU <SENDKEYS>S
IT WILL COME TO THIS MENU AND ENTER DATE AUTO PREVIOUS DAY
AND SENDKEYS<TAB>ACCOUNTS<TAB>M&C
HOW DO I GET PREVIOUS DAY AUTOMATCIALLY

 

[SkipVought]

     ASSETS = Sess.Screen.GetString(19, 72, 2)
      ObjWorksheet.Cells(10, 3).Value = ASSETS ' ASSETS

      LIABILITES = Sess.Screen.GetString(19, 72, 2)
      ObjWorksheet.Cells(10, 5).Value = CDN EQUI  'LIABILITES

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

SKIP
NO it is nto the value in 19,72,2 it may some time 18 it may some time 17
row position will be vary

 

[SkipVought]

You'll have to GetString for each row and test the row heading value to determine if it's the ones you want.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

Skip
i cannot search teh postion. becasue each page position will be wary.
rather i find "assets" if it is there and corresponding value put it in excel sheet
so for taht
if getstring search " assets"
ObjWorksheet.Cells(10, 3).Value = ASSETS ' ASSETS
is it possible

 

[SkipVought]

That's what the InStr function is for.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Check out this code snippet...

    Dim s As String, i As Integer, j As Integer, b As String, val As String
    s = "          LIABLITIES      50      40      80      20 "
    i = InStr(s, "LIABLITIES")
    If i > 0 Then
        val = ""
        For j = i + Len("LIABLITIES") To Len(s)
            b = Mid(s, j, 1)
            Select Case b
                Case " "
                    If val <> "" Then MsgBox val
                    val = ""
                Case Else
                    val = val & b
            End Select
            
        Next
    End If

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

Skip
thanks for your help. but this logic is not working here, may be i asked yu for advice, yu can give me a suggestion
waht exactly i want it the belwo screen go to 1 and see ist page if it is session 9, 12 check last month or current month if it is last month it will grab
from row 18, 002 to 22,002
and put it in excel sheet
1/11/08 Accounts M&C
=====================================
1. 1/11/08 M&C details 40
2. 1/11/08 M&C Accounts 6

URAD DAL
ASSETS 00 00 00 00
LIABLITIES 50 40 80 20
TOTAL LOSS 30 30 30 30
TOTAL NET 30 30 30 30
Channadal
ASSETS 25 75 10 20
LIABLITIES 45 80 30 25
TOTAL LOSS 90 40 20 80
TOTAL NET 30 30 30 30


each page copied into excel sheet.
what is your suggestion how i can do this one

 

[Skie]

On the topic of holidays, for my work I wrote a script to calculate the holidays. We only use US federal holidays so it was easy to figure them for any given year.

Function PriorBusDay(sYear, dDate)
  Dim HOL(9)
  HOL(0) = DateValue("01-01-" & sYear) 'New Years
  HOL(1) = DateValue("07-04-" & sYear) 'Independence
  HOL(2) = DateValue("11-11-" & sYear) 'Veterans
  HOL(3) = DateValue("12-25-" & sYear) 'Christmas
  HOL(4) = DateValue("01-15-" & sYear) 'Martin Luther King
  HOL(5) = DateValue("02-15-" & sYear) 'Washington
  HOL(6) = DateValue("09-01-" & sYear) 'Labor
  HOL(7) = DateValue("10-08-" & sYear) 'Columbus
  HOL(8) = DateValue("05-31-" & sYear) 'Memorial
  HOL(9) = DateValue("11-01-" & sYear) 'Thanksgiving

  For i = 0 To 3
    If WeekDay(HOL(i)) = 1 Then
      HOL(i) = HOL(i) + 1
    ElseIf WeekDay(HOL(i)) = 7 Then
      HOL(i) = HOL(i) - 1
    End If
  Next

  For i = 4 to 7
    While WeekDay(HOL(i)) <> 2
      HOL(i) = HOL(i) + 1
    Wend
  Next

  While WeekDay(HOL(8)) <> 2
    HOL(8) = HOL(8) - 1
  Wend

  While WeekDay(HOL(9)) <> 5
    HOL(9) = HOL(9) + 1
  Wend
  HOL(9) = HOL(9) + 21

  dDate = dDate - 1
  DateChanged = True
  Do While DateChanged
    DateChanged = False
    If WeekDay(dDate) = 1 Or WeekDay(dDate) = 7
      dDate = dDate - 1
    End If
    For i = 0 To 9
      If dDate = HOL(i) Then
        dDate = dDate - 1
        DateChange = True
      End If
    Next
  Loop

  PriorBusDay = dDate
End Function

 

[SkipVought]

SIMPLIFY!

Grab EVERYTHING and write it into an Excel table.

Then use Excel's plethora of analysis, lookup and reporting features to pull out the data you want. This is the way that I process most mainframe screens. If I can get the data into Excel, I can slice it and dice it seven ways from sunday.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

Skip
thanks a lot!. i will try it today

 

[ram567]

Skip
you said before vba in excel i can do it. do you have any samples i can see that. could you send zip file so that i can see how it works

 

[SkipVought]

Sample scraper:

http://www.sendspace.com/file/miqv5j

With each new screen, I add descriptive data to a table that enables my software to know what fields are in what position on the screen.

I read EVERTHING on the screen into ONE string and parse the string in accordance with the table. Then I plunk it into an Excel sheet
[tt]
ScrNam FIELD FR TR CL LN TYP
PPNI6703 ScrNam 1 1 3 8 CHAR
PPNI6703 T_Dat 1 1 63 8 NUM
PPNI6703 T_Tim 1 1 73 8 NUM
PPNI6703 PN 4 4 20 16 CHAR
PPNI6703 NOMEN 4 4 45 35 CHAR
PPNI6703 SEL 8 21 4 1 CHAR
PPNI6703 PACKAGE 8 21 8 7 CHAR
PPNI6703 VENDOR 8 21 17 6 CHAR
PPNI6703 TY 8 21 25 1 CHAR
PPNI6703 ST 8 21 28 1 CHAR
PPNI6703 # TRAV 8 21 31 3 CHAR
PPNI6703 QTY 8 21 35 5 NUM
PPNI6703 REC_QTY 8 21 41 5 NUM
PPNI6703 BEG OPER 8 21 48 3 CHAR
PPNI6703 END OPER 8 21 54 3 CHAR
PPNI6703 C DATE 8 21 60 8 NUM
PPNI6703 NEED DATE 8 21 70 8 NUM
PPNI6703 MSG 22 22 13 68 CHAR
[/tt]
where...
[tt]
ScrNam - Screen ID from the screen
FIELD - Field Name and headings in my Excel table (not necessarily the same name as on screen)
FR - From line
TR - Thru line (if FR <> TR then multi line spec
CL - Start Column
LN - Length of Field
TYP - Type of Field (Numeric or Character)
[/tt]


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ram567]

thank yu very much Skip. but the user point of view how can the run the macro daily everyday online process current report, should run macro in daily process

 

[SkipVought]

You can use the Windows Scheduled Tasks in the Control Panel.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}