What code do you have so far?

Where are you stuck?

Have you searched in this forum?

Skip,

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

skip
thank you very much for your response. i searched in this form from that i got whole screen captured and put it in excel. but i want only 4 columns in this extra screenand put it in excel and add 4 screen captured write into excel. could you provide the code please
thanks

When you capture the text in Extra, it's just that -- TEXT.

You can parse in accordance with the specification for the 4 fields that you posted, which YOU have and I do not.

Skip,

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

Thank you very much for your help Skip,
i have code here
acct no journal id   journla type   journla mode
head line has 05,011  05,22  05,35  05,59
but the content has 06,11,5  06,22,10  06,35,25  06,59,10
could you tell me how to mention the above one copy to cell and the same thing has 4 screens

the below code copies only current screen

for iCount = 6 to 23  'Rows
      aline = sess.Screen.GetString(iCount, 1, 4Cols)  
      wb.WorkSheets("sheet1").Cells(iCount, 1).Value = aline
      wb.WorkSheets("sheet1").Cells(iCount, 1).Font.Name = "Courier New"
   next iCount  
       

the arguments are Row, Col, Length, [Page]
where Page is for VT session only.

So what is 4Cols

Skip,

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

thanks skip

aline = sess.Screen.GetString(23,4,8)
do i have to change the above one. each page 23 rows 4 column 8 pages

the THIRD argument is LENGTH.

"each page 23 rows 4 column 8 pages"

How can each page have 8 pages???

Are you using VT Session?

Skip,

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

thanks Skip
yes it is VT SESSION. but each page has 23 rows 4 colums totally 8 pages. but last column in each page has journal model. so if the jounrla model = "xx
write other three columns and jounral model vise put it into excel. how to write the code

this is a but ugly but functional nevertheless for 1 page.
i don't know how you advance to the next page.
one problem i noticed is the length for journla type is 25 characters, which overlaps with where journla mode begins
hope this helps

Thanks VZACHIN. thank you so much. it works perfectly. but i need to do other pages too, could yuo help me in that.

HI1
could you help me the below code i am trying to do if acct no = 99999 it stops copying into excel
othewise goes on next page and next page continueing
After next i insert this line

Sess0.Screen.GetString("<F8>")
but though, it is not going to next page, the same page is repeating
even in put
do
Rw  = 1
For x = 6 To 23                    
AcctNo = Trim(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))
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.GetString("<F8>")

        Sess0.Screen.WaitHostQuiet (1000)
   ' Loop While AcctNo = trim( Sess0.Screen.GETstring( x,11,5 = 9999999
End Sub

 

ram567,

you need to use SendKeys to scroll screens & pages.

please note that i use ("<right><PF8>") to move the cursor over 1 field prior to PF8. when the data refreshes, the cursor should move back to it's original position.
i would then add
where Row = the original row &
Col = the original column
so it would look something like this:

Please heed the following:
there is a row limitation in excel depending on the version being used.

Using Do/Loops can go on forever until
the VT session is no longer active or
Excel is closed or
you tell the macro to stop.
when should excel stop copying data?
how do you know when the last page has been reached?


hth

Hello VZChin
first of all thank you very much for your explanation and helping me lot.
Do
Sess0.Screen.SendKeys ("<PF8>") 'next screen
Sess0.Screen.WaitHostQuiet (500)
Loop While Acctno = "99999" or  Sess0.Screen.GetString(24,08, 20) = "End of List"

inthat patriuclar extra! no pages. last screen shows in 24/008  - End of list
if i run the above code it shows alternative line the first screen only. it does not write next screen in excel sheet. coudld you help me in this regard

Sess0.Screen.GetString(24,08, 20) = "End of List"

should be

11 is for the number of characters in "End of List".

also, i believe this is Case Sensitive.

Quote:

it does not write next screen in excel sheet

something like this should work


some questions:
where did you put the Do statement?
is End of List the
end of the screens? or
end of the pages?
how do you go to next page?

thanks! but it ahs 7 screens i ran the above code. but it writes only first screen. it does not go next screen. end of screen only shows END OF LIST.
FOR NEXT SCREEN IT SAYS F8 between do loop only. but still it is not working. help me in this regard. thanks in advance

thank you so much VXCHIN. it is great working. Awsom. i made a mistakte . instead of while i put until  
sorry about that.
the same thing i need to put it in sheet 2 journal mode sorting order
could you provide me
thank you so much once again. you are really genious

Hi!
the above one is working but do until it does not write the two records in excel. becasue last screen has 2 records
in it. could you provide how to do that. thanks in advance

Quote:

the above one is working but do until it does not write the two records in excel. becasue last screen has 2 records
in it. could you provide how to do that. thanks in advance

how about this way instead:
=======

Quote:

the same thing i need to put it in sheet 2 journal mode sorting order

not sure what you mean by this.
have you tried placing this code in an excel module?

Hi Vzchin
thank you so much for helping me. i run this macro in extra! sheet1 it comes everyhting. capture the screen in sheet1 and the same thing but journlal mode sorting order
for example if journal mode is xx, yy, aa
it comes as aa, yy, xx
thanks a lot VZchin. really you are great.

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

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

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

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"

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).

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

glad the code is finally working.

Quote:

. 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.

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

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"

 

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

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

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


       

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!



have you tried writing this code in Excel?

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

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.

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

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

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

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

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

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

you mean its snapshot

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

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

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.

Quote (ram):

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.

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

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  

Quote:

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.

can you query your screen with a journalid one at a time instead?

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

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

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

can you query your next screen by doing a find with a journalid

what i'm trying to understand is the following:
when you reach ,
that means all the data is now in sheet1.

how do you now get to the next screen?
do you then do the following ?are you saying that you cannot query the 2nd screen at all?

why do you need to first sort in excel?

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

which macro is failing? at what point?

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   

 

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:

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

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


you should answer skip's question on your other post,

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?  

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