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!

 

[SkipVought]

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}

 

[ram567]

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

 

[SkipVought]

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}

 

[ram567]

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

 

[SkipVought]

aline = sess.Screen.GetString(iCount, 1, 4Cols)

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}

 

[ram567]

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

 

[SkipVought]

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}

 

[ram567]

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

 

[vzachin]

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

Sub 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
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
 
End Sub

 

[ram567]

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

 

[ram567]

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

 

[vzachin]

ram567,

you need to use SendKeys to scroll screens & pages.

Do

Do
Sess0.Screen.SendKeys ("<right><PF8>")<--next screen
Sess0.Screen.WaitHostQuiet (500)
Loop While AcctNo = "99999"

Sess0.Screen.SendKeys ("<right><PF?>") <--next page
Sess0.Screen.WaitHostQuiet (500)
Loop While ???????

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

Do: Loop Until Sess0.Screen.Row = 1 And Sess0.Screen.Col = 11

where Row = the original row &
Col = the original column
so it would look something like this:

Sess0.Screen.SendKeys ("<right><PF8>")<--next screen
Sess0.Screen.WaitHostQuiet (50)
Do: Loop Until Sess0.Screen.Row = 1 And Sess0.Screen.Col = 11
Sess0.Screen.WaitHostQuiet (50)

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

 

[ram567]

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

 

[vzachin]

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

should be

Sess0.Screen.GetString(24,8,11) = "End of List"

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

also, i believe this is Case Sensitive.

it does not write next screen in excel sheet

something like this should work

Rw  = 1
Do
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.SendKeys ("<PF8>") 'next screen
Sess0.Screen.WaitHostQuiet (500)
Loop While Acctno = "99999" or  Sess0.Screen.GetString(24,8, 11) = "End of List"

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?

 

[ram567]

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

 

[ram567]

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

 

[ram567]

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

 

[vzachin]

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:

Rw  = 1
Do
For x = 6 To 23                    
AcctNo = [COLOR=red][s]Trim([/s][/color]Sess0.Screen.GetString(x, 11, 5)[COLOR=red][s])[/s][/color]
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))
[COLOR=red]If AcctNo = "99999" Then
Exit For
End If[/color]
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

=======

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?

 

[ram567]

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.