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]

Mr Milson
Could you help me in the above one.
thanks in advance

 

[IknowMe]

As much as I hate to say it at this point, you may want to move your code over to vba if your going to be doing more with your excel worksheet than just populating it. EB has a hard time passing some of Excel's methods/functions through the object and worksheet manipulation is much better in VBA.

I'm still very confused about what your actual need is based on your statement.

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

"but i need to go three screens
as i said before,
first screen it goes there and create the excel (ALREAADY CREATED)"
The code both vzachin and I provided should be copying all Extra screens to Excel until "DEMO" and "END OF LIST" are found on the screen. So I don't understand what you mean by "three screens". It may be three, but could be more or less.

" 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"
I have no idea what your referencing as "second screen" here nor what your attempting to compare.

"IF THE SCREE2 08/080 = E THEN GO TO THIRD SCREENROW 13/16 TO 13/69 TO COLUMN 'G"
Again not a clue. What is the third screen? How do you navigate to it? I'm sorry I just can't understand your explanation.

To answer your question about sorting by multiple columns: Go through your Excel help file and research the range and selection objects.

[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]

Mr. Milson
thank you very much for your help.the below code i created for second screen
second screen ROW5/088 = sJournalId = Trim(oExtraScreen.GetString(iExScrnRow, 22, 10)) = second screen ROW5/088
but what happens here it goes 02 to 109 check everything
and repeating the same journal id in sheet 1, if it is repeat it does not need ot write in sheet2. see this below code any changes, please
Do
'Sess0.Screen.GetString(3,20, 25))



Journalid = Trim(Sess0.Screen.GetString(5, 58, 12))
Optkey = Trim(Sess0.Screen.GetString(8, 80, 1))
OptkeyMask = Trim(Sess0.Screen.GetString(9, 3, 100)) + Trim(Sess0.Screen.GetString(10, 3, 100))
Translist = Trim(Sess0.Screen.GetString(13, 16, 100))
TL = Trim(Sess0.Screen.GetString(13, 80, 1))
Optaccess = Trim(Sess0.Screen.GetString(18,10,1))
Update = Trim(Sess0.Screen.GetString(18,20,1))
Optinsert = Trim(Sess0.Screen.GetString(18,30,1))
Replace = Trim(Sess0.Screen.GetString(18,40,1))
Delete = Trim(Sess0.Screen.GetString(18,50,1))
Move = Trim(Sess0.Screen.GetString(18,60,1))
Overlay = Trim(Sess0.Screen.GetString(18,71,1))
FOR RW = 2 to 109
If Journal id = obj.Worksheets("Sheet1").cells(RW,"B").value then

RW = RW + 1
With obj.Worksheets("Sheet2")
.Cells(Rw, "A").Value = Jouranlid
.Cells(Rw, "B").Value = OptKey
.Cells(Rw, "C").Value = optKeyMask
.Cells(Rw, "D").Value = TL
.Cells(Rw, "E").Value = TransList
.Cells(Rw, "F").Value = Optaccess
.Cells(Rw, "G").Value = Update
.Cells(Rw, "H").Value = Optinsert
.Cells(Rw, "I").Value = Replace
.Cells(Rw, "J").Value = Delete
.Cells(Rw, "K").Value = Move
.Cells(Rw, "L").Value = Overlay


'obj.Worksheets("Sheet2").Cells.Sort Key1:=obj.Worksheets("Sheet2").Columns("D"), Header:= 1
obj.Save
End with
Else
End if
Next
Sess0.Screen.SendKeys ("<PF8>") 'next screen
Sess0.Screen.WaitHostQuiet (100)
Loop

 

[vzachin]

Sess0.Screen.SendKeys ("<PF8>") 'next screen

what happens when you evoke <PF8>? does it go to the next journalid?

your code is checking sheet1 Rw 2 to 109 (row by row) to see if it's on the screen, then placing the data into sheet2. this takes longer to execute.

[blue]can you query your screen with a journalid one at a time instead?[/blue]

in otherwords, use your sheet1 data to query your mainframe first. then if found, then place data in sheet2.

 

[ram567]

vzchin
thanks ! i did first it copies into sheet1 and checking from current screen to sheet 1
earlier macro itdoes sheet 1. this is for second screen sheet2

 

[ram567]

PF 8 GOES TO NEXT SCREEN AND CHCK THE JOURNAL ID IN 05/088

 

[vzachin]

[blue]can you query your next screen by doing a find with a journalid[/blue]

what i'm trying to understand is the following:
when you reach

Loop Until sJournalType = "DEMO" and oExtraScreen.GetString(24,8, 11) = "END OF LIST"

,
that means all the data is now in sheet1.

how do you now get to the next screen?
do you then do the following ?

oExtraScreen.SendKeys ("<PF8>")

are you saying that you cannot query the 2nd screen at all?

why do you need to first sort in excel?

 

[ram567]

vzchin
yes what you said is correct. now i did two sepearte macro. so it run separatly. once i finish third screen i will try to mix all in one, if it does not work i will separate the three screen.
becasue extra macro wont run if it is more than 1200 lines
each and every screen have seperate command i have to include send keys enter
i did not include anythign still
i sorted in excel sheet in first one because each account no has so many jounal id's

 

[vzachin]

which macro is failing? at what point?

 

[ram567]

vzchin,
thanks
the below one when i run it the property or method not found in the below code and
nextrow = .[A1].currentregion.rows.count -1
obj.save, what happend it asked already exists do yuo save it that also have error



Do
'Sess0.Screen.GetString(3,20, 25))



Journalid = Trim(Sess0.Screen.GetString(5, 58, 12))
Optkey = Trim(Sess0.Screen.GetString(8, 80, 1))
OptkeyMask = Trim(Sess0.Screen.GetString(9, 3, 100)) + Trim(Sess0.Screen.GetString(10, 3, 100))
Translist = Trim(Sess0.Screen.GetString(13, 16, 100))
TL = Trim(Sess0.Screen.GetString(13, 80, 1))
Optaccess = Trim(Sess0.Screen.GetString(18,10,1))
Update = Trim(Sess0.Screen.GetString(18,20,1))
Optinsert = Trim(Sess0.Screen.GetString(18,30,1))
Replace = Trim(Sess0.Screen.GetString(18,40,1))
Delete = Trim(Sess0.Screen.GetString(18,50,1))
Move = Trim(Sess0.Screen.GetString(18,60,1))
Overlay = Trim(Sess0.Screen.GetString(18,71,1))
FOR RW = 2 to 109
If Journal id = obj.Worksheets("Sheet1").cells(RW,"B").value then
RW = RW + 1
With obj.Worksheets("Sheet2")
i = 0
nextrow = .[A1].currentregion.rows.count -1

for icol = 3 to .[A1].currentregion.columns.count
.cells(nextrow, icol).value = vData(i)
i = i + 1

Next

.Cells(Rw, "A").Value = Jouranlid
.Cells(Rw, "B").Value = OptKey
.Cells(Rw, "C").Value = optKeyMask
.Cells(Rw, "D").Value = TL
.Cells(Rw, "E").Value = TransList
.Cells(Rw, "F").Value = Optaccess
.Cells(Rw, "G").Value = Update
.Cells(Rw, "H").Value = Optinsert
.Cells(Rw, "I").Value = Replace
.Cells(Rw, "J").Value = Delete
.Cells(Rw, "K").Value = Move
.Cells(Rw, "L").Value = Overlay


'obj.Worksheets("Sheet2").Cells.Sort Key1:=obj.Worksheets("Sheet2").Columns("D"), Header:= 1
obj.Save
End with
Else
End if
Next
Sess0.Screen.SendKeys ("<PF8>") 'next screen
Sess0.Screen.WaitHostQuiet (100)
Loop

 

[vzachin]

the macro 2,screen 2, sheet 2 should be similar to macro 1, screen 1, sheet 1

in other words, have the macro open your excel sheet...etc...

also, there's a typo here:

.Cells(Rw, "A").Value = Jouranlid

[blue]i still don't know how you get to your 2nd screen and how you are calling up your data[/blue]

i don't understand what this code is supposed to do

i = 0
       nextrow = .[A1].currentregion.rows.count -1
    
      for icol = 3 to .[A1].currentregion.columns.count
          .cells(nextrow, icol).value = vData(i)
          i = i + 1

       Next

[blue]you should answer skip's question on your other post, [/blue]

fwiw, it is much easier to copy this code into excel and have excel execute the code. you're already on a learning curve, what's 2 more steps?

 

[ram567]

vzchin
find the last row in column A in sheet 2
it workes everyhting fine except the above one. it checks in sheet 1 column a first row and second row if it is same it wont write in sheet2
if i remove the above code it works fine but only thing it repeats the same thing howmnay times it has journla id
i want to avoid that
one journla id it has to write in sheet 2 if it is repeat it wnt write