capture 4 screens to excel 3

[ram567]

could you tell me how to write the code in the below problem
1. i have to capture 4 screens to ecel sheet
screen has Account no journl id journla model journal type

there are four rows in one screen there are 5 screen
each screen write in excel sheet as row wise.
like A, B C D
could you help me the code please.
thanks in advance
and another i have to run in main session from extra!

 

[ram567]

VZCHIN
i tested the above code. it is going on going on the macro is running still. and 99999 record repeating again again again

 

[vzachin]

can you supply a sample of your data (in particular the very last screen with the 2 records)
and your code?

 

[ram567]

it works great i added last line do until sess0.scssion END OF LIST )
IT WORKS FINE. THANKS A LOT VZCHIN

 

[ram567]

Rw = 1
Do
For x = 6 To 23
AcctNo = (Sess0.Screen.GetString(x, 11, 5))
JournlId = Trim(Sess0.Screen.GetString(x, 22, 10))
JournlaType = Trim(Sess0.Screen.GetString(x, 35, 25))'<---is 25 length correct?
JournlaMode = Trim(Sess0.Screen.GetString(x, 59, 10))
If journlaMode = "" Then
Exit For
End If
vzchin
i modifed the code below, it works fine. i think integer problem if i say 99999 tha macro is not working so otherfields are alpha only so i put other fields as condition. thank you so much. i need another question
Hi Vzchin
. capture the screen in sheet1 and the same thing but journlal mode sorting order
for example if journal mode is xx, yy, aa


Rw = Rw + 1
With obj.Worksheets("test")
.Cells(Rw, "A").Value = AcctNo
.Cells(Rw, "B").Value = JournlId
.Cells(Rw, "C").Value = JournlaType
.Cells(Rw, "D").Value = JournlaMode
End With
Next
Sess0.Screen.SendKeys ("<PF8>") 'next screen
Sess0.Screen.WaitHostQuiet (500)
Loop Until JournalType = "DEMO" AND Sess0.Screen.GetString(24,8, 11) = "END OF LIST"

 

[IknowMe]

Your going to want to do this once after your done populating your worksheet. Of course this is assuming I understand your question. I believe your asking how to sort your worksheet by the "JournlaMode" column on your worksheet (column D).

With obj.Worksheets("test")
     .Cells.Select
     Selection.Sort Key1:=Range("D1"), _
     Order1:=xlAscending, Header:=xlGuess, _
     OrderCustom:=1, MatchCase:=False, _
     Orientation:=xlTopToBottom, _
     DataOption1:=xlSortNormal
End With

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[vzachin]

glad the code is finally working.

. capture the screen in sheet1 and the same thing but journlal mode sorting order
for example if journal mode is xx, yy, aa

still don't quite understand what you need here. please elaborate.

 

[ram567]

Hi vzchin
i have in sheet 1 acctno, journla id , journl type jounla mode viz COLUMN A, B C,D
I NEED JOUNRLA MODE SORTING ORDER. MY MILSON HAS THE ABOUVE CODE IN IT. I WILL TRY THAT ONE AND LET YOU KNOW
I NEED JOURNLA MODE, ACCTNO JOURNLA ID JOUNRL TYPE IN SHEET 2JOURNLA MODE ASCENDING ORDER

 

[ram567]

Mr Milson
thank you so much.
i actually do the below query. VZCHIN helped me the above code from vt session and gran in excel sheet1
i need from main session and do the follwing
i have the code : below so far
i need to grab 3 screen and if claus inthe screen
Sees0.Screen<Home>GPSE<TAB>VTRE<TAB>MSNG -> first screen
grab in excel sheet 1
and another screen
sess0.Screen<home>vjc<tab>oper -> second screen
from screen 1 match with 06/22, 07/22, 08/22 ... so on 23/22
screen 2
05/58 - 20 words
if it is = and 08/080 = "I" AND 13/080 = "I" THEN
WRITE ROW 9 AND 10 IN EXCEL SHEET 1 COLUM "E"
AND ROW 13/16 TO 13/69 TO COLUMN 'F"

IF IT IS <> "I" THEN
GO TO SCREEN 3
SESS0.SCREEN<HOME>VJC,TAB>MTR
MATCH THE FIRST SCHREN 05/28 - 20WORDS
WRITE ROW 07/003 TO 07/079, 08/003 TO 08/079, 09/003 TO 09/079
IN COLUMN F


code:

Main
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions
If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object.Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 3000 ' milliseconds
OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object.Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
'--------------------------------------------------------------------------
sFile = "C:\testRAM567.xls"
Dim obj as object
Dim objWorkbook as object
Set obj=CreateObject("Excel.Application")
obj.visible=TRUE
obj.Workbooks.Open sFile
set objWorkbook=obj.Worksheets("test")
'---------------------------------------------------------------------------
'THIS copies the data from the VT session row by row into
'Excel sheet beginning in Cell A2-C2 and downward
Rw = 1
Do
For x = 6 To 23
AcctNo = (Sess0.Screen.GetString(x, 11, 5))
JournlId = Trim(Sess0.Screen.GetString(x, 22, 10))
JournlaType = Trim(Sess0.Screen.GetString(x, 35, 25))'<---is 25 length correct?
JournlaMode = Trim(Sess0.Screen.GetString(x, 59, 10))
If journlaMode = "" Then
Exit For
End If
Rw = Rw + 1
With obj.Worksheets("test")
.Cells(Rw, "A").Value = AcctNo
.Cells(Rw, "B").Value = JournlId
.Cells(Rw, "C").Value = JournlaType
.Cells(Rw, "D").Value = JournlaMode
End With
Next
Sess0.Screen.SendKeys ("<PF8>") 'next screen
Sess0.Screen.WaitHostQuiet (500)
Loop Until JournalType = "DEMO" AND Sess0.Screen.GetString(24,8, 11) = "END OF LIST"

 

[ram567]

MR. MILSON
the below code can i put it in extra! the above macro, in that case Order 1 is Journal mode and ordercustom is sheet name
is that right? let me know. thanks a lot
With obj.Worksheets("test")
.Cells.Select
Selection.Sort Key1:=Range("D1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

 

[vzachin]

ram567,
good luck with the rest of your coding. this is getting a little bit complicated for me now.

i'm still confused though.
you're taking data from 3 VT sessions and placing data into an Excel workbook with 3 sheets?
then sorting the data in sheet 1?
and now comparing VT session 1 with VT session 2 and if certain conditions are met, copy data and place in Excel sheet1, Column E & F
otherwise compare data VT session 1 with VT session 2?
and copy data to Excel Sheet 1 Column F?

it would be easier for me to understand if i can see some sample data.

good luck

 

[ram567]

Mr. vzchin
thanks a lot
SCREEN1 l EXAMPLE EACH JOURNAL ID HAS DIFFERENT DEPARTMENT AND DEFIRRETN LIST
SO IF THE EXCEL SHEET HAS JOURNAL ID ASCENDING ORDER SORT

acctno journalid journal type journal model
CB98 MANABC ACCOUNTS FF
ST149 TABNG CREDIT MM
NN145 TANGR VTMS NN


SCREEN 2
IF 05/08 = SCREEN 1 i.e MANABC
AND 08/80 = 'I' AND 13/080 = 'i' THEN
row 9 has so many DEPARTMENT for example TR567,MN457,MT548, CG349 AND SO ON
ROW 10 ALSO CONTINUE
ROW13/016 TO 13/73 HAS LIST FOR EXAMPLE T54N,T762,T754 AND SO ON
IF IT IS X THEN
SCREEN3
THE SAME DATA LIKE SCREEN2 ROW9L
TR567 HAS HOWMANY ACCTNO, JOURNLA ID JOURNLA TYPE AND JOURNAL MODEL
T765 HAS HOWMANY
BUT I DOT KNOW HOW TO DO THAT
IS THAT ANYOTHER WAY TO DO THAT WHAT SAMPLES DO YOU WANT LET ME KNOW
THANKS

 

[vzachin]

let's deal with only screen 2 for now.
Question #1 = how are you finding the data for screen 2?
Question #2 = where does the data begin in Row 9 & 10?
Question #3 = where does the data end in Row 9 & 10?
Question #4 = is Row 9 for Column E in Excel
Question #5 = is Row 10 for Column F in Excel
Question #6 = the data for Column E & F, are they supposed to align with the original data from Screen 1?
Question #7 = what is the data in row 13 for? is this information supposed to be copied into Excel?

sorry for all these questions. man! am i confused!

SCREEN2                                                                         
       XXXXXX(THIS IS ROW 9)<--IF THIS MATCHES SCREEN 1 &(8,80)=1, & (13,80)    
                               (8,80,1)="I" & (13,80,1)="I"                     
                                                                                
THEN COPY ROWS 9 & 10 TO EXCEL SHEET COLUMN E & F (THESE ARE DEPARTMENTS?)     I
TR567,MN457,MT548,CG349 (THIS IS ROW 9- WHAT IS THE COL POSITION & # CHARS)     
AA111,AA112,AA113,AA114 (THIS IS ROW10- WHAT IS THE COL POSITION & # CHARS)     
                                                                                
                                                                                
               T54N,T762,T754,(THIS IS ROW 13, HOW MANY # CHARS?)              I
               WHAT IS THE PURPOSE OF ROW 13?

have you tried writing this code in Excel?

 

[ram567]

VZCHIN THANKS FOR YUOR HELP ANSWERED YOUR QUESTION

EXCEL SHEET1 HAS SORTING JOURNAL ID

Question #1 = how are you finding the data for screen 2?

SCREEN2 ROW05/058 MATCH WITH JOURNAL ID SCREEN1 06,022, 07/022,08/022
OR IF ITIS IN EXCEL SHEET1 HAS SORTING JOURNLA ID FIRST COLUMN A HAS JOURNLA ID
EACH COLUMN MATCH WITH SCREEN2 ROW5/088
IF IT MATCHES AND also it matches screen 2 08/80 = 'I' AND 13/080 = 'i' THEN
or scren 1 has 06/22 to 23/22 to journla id that also match with screen 2 08/80 = 'I' AND 13/080 = 'i' THEN

Question #2 = where does the data begin in Row 9 & 10?
09/3 to 09/080 10/03 to 10/80 ( some screen has only 09/03 some screen has 09/03 to 09/70 or some screen 10/03 to 10/15

Question #3 = where does the data end in Row 9 & 10?
minimum 09/03 to 09/080 10/03 to 10/80

Question #4 = is Row 9 for Column E in Excel
9 in column E AND 10 ALSO COLUM IN EXCEL IF IT IS 'I'
Question #5 = is Row 10 for Column F in Excel
QUEST 4 ANSWER THE SAME ABOVE
Question #6 = the data for Column E & F, are they supposed to align with the original data from Screen 1?
YES IT IS ONE JOURNAL ID HAS SO MANY IN 9 AND 10 I.E EXCEL SHEET COLUM A IF IT IS JOURNAL ID AND COLUMN E HAS SO MANY FOR 1 JOURNLA ID

Question #7 = what is the data in row 13 for? is this information supposed to be copied into Excel?
ROM 13/080 IF IT IS I IT WILL COPY INTO COLUMN E AND IF IT IS X COPY TO F SCREEN AND GO TO SCREEN 3

nO YOU ARE NOT CONFUSTED I TOLD YOU YOU ARE GENIOUS

CODE
SCREEN2
XXXXXX(THIS IS ROW 9)<--IF THIS MATCHES SCREEN 1 &(8,80)=1, & (13,80)
(8,80,1)="I" & (13,80,1)="I"

THEN COPY ROWS 9 & 10 TO EXCEL SHEET COLUMN E & F (THESE ARE DEPARTMENTS?) I
TR567,MN457,MT548,CG349 (THIS IS ROW 9- WHAT IS THE COL POSITION & # CHARS)
AA111,AA112,AA113,AA114 (THIS IS ROW10- WHAT IS THE COL POSITION & # CHARS)


T54N,T762,T754,(THIS IS ROW 13, HOW MANY # CHARS?) I
WHAT IS THE PURPOSE OF ROW 13?



have you tried writing this code in Excel?
NO I AM NOT TRYING ANYTHIGN. I AM VERY POOR IN THIS KNOWLEDGE, HELPING FROM YOU

 

[IknowMe]

Ram, it seems much is lost in translation here. I cleaned up the code you seem to be using (some Error Checking should be added based on the skills of your users). Try this out and see if it still gives the results your looking for. I'm unclear of what you want to happen with the Excel Worksheet from here, perhaps you can attempt to help me understand your needs a bit better.

Option Explicit
Sub Main

    Dim oExtraScreen As Object, oExcelApp as Object, oExcelWS as Object    
    Dim iExScrnRow as integer, iExWSrow as integer
    Dim sAcctNo as String, sJournalId as String, sJournalType as String
    Dim sJournalMode as String

    Set oExtraScreen = CreateObject("EXTRA.System").Sessions.ActiveSession

    Set oExcelApp=CreateObject("Excel.Application")
    oExcelApp.visible=TRUE
     
    oExcelApp.Workbooks.Open "C:\testRAM567.xls"
    Set oExcelWS = oExcelApp.Worksheets("test")
    
    iExWSrow = 1

    Do
        For iExScrnRow = 6 To 23                    
            sAcctNo = Trim(oExtraScreen.GetString(iExScrnRow, 11, 5))
            sJournalId = Trim(oExtraScreen.GetString(iExScrnRow, 22, 10))
            sJournalType = Trim(oExtraScreen.GetString(iExScrnRow, 35, 25))
            sJournalMode = Trim(oExtraScreen.GetString(iExScrnRow, 59, 10))
            If sJournalMode <> "" Then
                iExWSrow = iExWSrow + 1
                With oExcelWS
                    .Cells(iExWSrow, "A").Value = sAcctNo
                    .Cells(iExWSrow, "B").Value = sJournalId
                    .Cells(iExWSrow, "C").Value = sJournalType
                    .Cells(iExWSrow, "D").Value = sJournalMode
                 End With
            Else
                 Exit For
            End If
        Next
        oExtraScreen.SendKeys ("<PF8>")
        oExtraScreen.WaitHostQuiet (500)
    Loop Until sJournalType = "DEMO" and oExtraScreen.GetString(24,8, 11) = "END OF LIST"

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[IknowMe]

Ooops, add and "End Sub" statement to the bottom of that code.

[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[ram567]

Thank you so much Mr. milson. IT WORKS GREAT. THANKS A LOT.
form that it comes in excel sheet . but i need to go three screens
as i said before,
first screen it goes there and create the excel (ALREAADY CREATED) and second screen
sJournalId = Trim(oExtraScreen.GetString(iExScrnRow, 22, 10)) = second screen ROW5/088
IF IT MATCHES AND also it matches screen 2 08/80 = 'I' AND 13/080 = 'i' AND IF IT IS 5 CHARACTERS WRITE IN TEST.COLUMN"D"
OR >5 CHARACTERS WRTIE IN TEST COLUMN E
IF THE SCREE2 08/080 = E THEN GO TO THIRD SCREEN
ROW 13/16 TO 13/69 TO COLUMN 'G

 

[ram567]

mR MILSON
I FORGOT TO ASK YOU? IS IT POSSIBLE CAN I DO TWO COLUMN ASCENDING ORDER, IF I PUT COLUMN C IT COMES AS COLUMN C ASC ORDER. how can i put two column together in ascending order like excel.
pl let me know, thanks once again

 

[WinblowsME]

ram, post the four screens of info (select all, copy and paste) and replace any sensitive info with XXX.

 

[ram567]

you mean its snapshot

 

[ram567]

vzchin,
could you help me how to do that with three screen. Thanks in advance.