attachmate and tsys (total systems) 1

[halobender]

Does anyone know if it is possible to make an excel spreadsheet that will communicate and gather information (like rows of text from certain transfields) from a tsys session that is running in attachmate? If it would be possible where would I start to config something like this?

 

[IknowMe]

Most definately, first decide if your going to be scripting in EB(Attachmate) or VBA(Excel). Then search this Forum for Excel and check out the FAQ's.

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[halobender]

okay i checked the faqs and gained some good information from them. Basically I have a dos session running in extra that i need 1 command to grab 2 entirely different screens from and dump it to excel so that i can have my spreadsheet auto figure some numbers. The set up i have at the moment envolves a tiresome copy and paste function 2 times per account worked. Could the sollution be something as simple as an edited macro? or do i have to go to the extent of using the create objects?

 

[calculus]

An edited macro will create objects to communicate with Extra.

calculus

 

[halobender]

Alright apparently I am going to need a little more help with this than what I had originally planned. I read the two faqs that have some nice pieces of vba which i assume are for excel, and i also have a book called power programming with vba for excel.

I am working with excel 2003 and attachmate extra 8.0
extra already has some built in funtions like a screen capture that only captures text full screen and opens outlook with that information in the message. Basically I want to do the same thing but with excel.

I run a local version of extra but the dos based app runs off a server, can someone provide an example of vb in extra or vba in excel that would suit my needs?

The vba must be able to go to the extra session and type in a few letters then <enter> capture screen then return the whole screen to excel, then go back to extra and type a few different letters and capture screen and again go dump the results in excel. I tried linking a pasted field but as you know that field updates constant and the values i need do not stay in the correct fields.

 

[iSPOK]

Do you have the script?, and are you trying to capture in a static position or your trying to capture data that could be any where on the screen??

 

[halobender]

The two screens i want to capture always containt the same type of information (fielded) some information may change but for the most part it is the same, and I just want to capture the entire screen and dump to excel where I already have formulas to get certain information and dump it to another area. At this point I do not have a script just some examples I found by searching the forums here.

 

[iSPOK]

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()

'--------------------------------------------------------------------------------
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 = 10 ' 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
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)


' This section of code contains the recorded events

System.TimeoutValue = OldSystemTimeout

'Extra Object
Dim Sys_Obj As Object, Sess_Obj As Object, oScreen_Obj As Object
Set oScreen_Obj = Sess0.Screen

'Excel Object
Dim Excel_Obj As Object, Excel_Sheet As Object
Set Excel_Obj = GetObject(, "excel.application")
Set Excel_Sheet = Excel_Obj.Workbooks("your file name.xls").Worksheets(1)

Dim Extra_Col As Integer
Dim Extra_Row As Integer
Dim Extra_Page As Integer
Dim Excel_Row As Integer
Dim Excel_Col As Integer
Dim result As String

LastExcelRow = Excel_Sheet.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1).Row
For Excel_Row = InputBox("Input Starting Row") To LastExcelRow
oScreen_Obj.SendKeys ("<Home><EraseEOF>")
oScreen_Obj.SendKeys ("dcs")
oScreen_Obj.MoveTo 1, 9
oScreen_Obj.PutString Excel_Sheet.Cells(Excel_Row, 1).Value
oScreen_Obj.SendKeys ("<enter>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
oScreen_Obj.SendKeys ("dcrd")
oScreen_Obj.SendKeys ("<enter>")
oScreen_Obj.WaitHostQuiet (g_HostSettleTime)
'Check for "Request Completed" (for multiple pages) outputting each page to file
'First page
Set SearchComplete = Sess0.Screen.Search("REQUEST COMPLETED")
If SearchComplete = "" Then
Set MyArea = Sess0.Screen.Area(23, 2, 22, 11, , 10)
Else
Set MyArea = Sess0.Screen.Area(23, 2, SearchComplete.Bottom, 10)
End If

'While loop for subsequent pages
Capturing SearchComplete"
While SearchComplete = ""
Set SearchComplete = Sess0.Screen.Search("*****")
If SearchComplete = "" Then
Set SearchComplete = Sess0.Screen.Search("PROD TOTAL")
If SearchComplete = "" Then
Sess0.Screen.SendKeys ("<PF8>")
oScreen_Obj.WaitHostQuiet (g_HostSettleTime)
End If
End If

Wend
' This will search the entire page for the '****' code
Sess0.Screen.GetString(CurrentRow, CurrentCol, 6) + "
Set MyScreen = Sess0.Screen
Set MyArea = MyScreen.Search("****")
MyScreen.MoveTo MyArea.Bottom, MyArea.Left + 0
CurrentRow = MyScreen.Row
CurrentCol = MyScreen.Col
result = Trim(Sess0.Screen.GetString(CurrentRow, CurrentCol, 6))
'MsgBox "Found 'B' Code at " + Str$(CurrentRow) + "," + Str$(CurrentCol) + ", " + Sess0.Screen.GetString(CurrentRow, CurrentCol, 6) + "."
'You should be on Extra Screen for column one of start row of Excel, now I'll write something to Excel column 2 on the same row.
Excel_Sheet.Cells(Excel_Row, 2).Value = result
oScreen_Obj.WaitHostQuiet (g_HostSettleTime)

System.TimeoutValue = OldSystemTimeout

Next Excel_Row

End Sub
...................................................

I myself found this in the forum, but have managed to get it to do various screen capturing processes with great sucess.....

 

[halobender]

I know this is going to sound way noobish, but as you see I am a Technical user and not a VB master so I am wondering if you can tell me if I will need to edit this and which parts will need editing. I want this to copy the extra session window lets call it window A then auto goto and copy window B and drop them into specific areas of the excel spreadsheet.

 

[iSPOK]

'Excel Object
Dim Excel_Obj As Object, Excel_Sheet As Object
Set Excel_Obj = GetObject(, "excel.application")
Set Excel_Sheet = Excel_Obj.Workbooks("your file name.xls").Worksheets(1)

your file name.xls. = what ever name you call the excel document....

Worksheets(1) = is the worksheet yor working from.....

("REQUEST COMPLETED") : this is what ever indicates the end of you page....

Set SearchComplete = Sess0.Screen.Search("*****") : The ***** represents the word your looking for on the screen..... In your case you can use screen cords,, eg..(1,80, 24, 80)........

Set MyArea = MyScreen.Search("****") : Again you can use screen cords for this..........


Set SearchComplete = Sess0.Screen.Search("PROD TOTAL") : This indicates in my world the one of two things at the end of my page., PROD TOTAL or REQUEST COMPLETED..

If SearchComplete = "" Then
Sess0.Screen.SendKeys ("<PF8>") : PF8 advances my page by one, if one of the two words above, PROD TOTAL or REQUEST COMPLETED.. appears at the bottom of the page........


I hope this helps...

 

[halobender]

spok first of all let me thank you and the other fine people at this forum, this is a great place. Now to the nitty-gritty. I worked with the code you gave me and couldnt get it to run at all. Nothing zip zilch. Anyhow I dug around here on the forums some more and found the following which I started manipulating to suit my needs and got it running a bit but it is doing some things I dont want it to do so I am hoping some of you can help me figure out the kinks.bolded areas are my issues as the rest is properly put together i think what i want the code in Excel to do is to look at the extra session and command send keys <home>WCAD<enter> "capture screen" then go back to excel and dump the capture to A1, then go back to Extra and send keys <home>IAPS<enter> "capture screen" then back to extra and dump the capture to say R1.. screen is 01/01 x 24/80. If and when i get this running i want to make some buttons on my spreadsheet to be clickable macros to basically call up extra session and send keys <home>aame<enter>then paste some text and possibly some cell information from the excel sheet into certain areas within the text. Please help



Sub TsysCalculator()



'--------------------------------------------------------------------------------



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 = 10 ' time is in 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

Sess0.Screen.WaitHostQuiet (g_HostSettleTime)





' This section of code contains the recorded events



System.TimeoutValue = OldSystemTimeout



'Extra Object

Dim Sys_Obj As Object, Sess_Obj As Object, SPOKScreen_Obj As Object

Set SPOKScreen_Obj = Sess0.Screen



'Excel Object

Dim Excel_Obj As Object, Excel_Sheet As Object

Set Excel_Obj = GetObject(, "excel.application")

Set Excel_Sheet = Excel_Obj.Workbooks("financetest.xls").Worksheets(1)

'Change ("xxxxxx.xls") part according to which ever worksheet your working from



Dim Extra_Col As Integer

Dim Extra_Row As Integer

Dim Extra_Page As Integer

Dim Excel_Row As Integer

Dim Excel_Col As Integer

Dim result As String



LastExcelRow = Excel_Sheet.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1).Row

For Excel_Row = InputBox("Input Starting Row") To LastExcelRow

SPOKScreen_Obj.SendKeys ("")

SPOKScreen_Obj.SendKeys ("")

SPOKScreen_Obj.MoveTo 0, 0

SPOKScreen_Obj.PutString Excel_Sheet.Cells(Excel_Row, 1).Value

SPOKScreen_Obj.SendKeys ("")

result = SPOKScreen_Obj.GetString()

'You should be on Extra Screen for column one of start row of Excel.

Excel_Sheet.Cells(Excel_Row, 2).Value = result

SPOKScreen_Obj.WaitHostQuiet (g_HostSettleTime)



result = SPOKScreen_Obj.GetString()

'You should be on Extra Screen for column one of start row of Excel.

Excel_Sheet.Cells(Excel_Row, 3).Value = result



System.TimeoutValue = OldSystemTimeout



Next Excel_Row



End Sub

 

[iSPOK]

first of all, when the input dialog box pups up, enter the row number you want it to start from,, eg. 1, however if the first row is labled, then type 2 end then click the ok button..... Note: it will start from cell A1.

LastExcelRow = Excel_Sheet.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1).Row
For Excel_Row = InputBox("Input Starting Row") To LastExcelRow
SPOKScreen_Obj.SendKeys ("<home>WCAD<enter>")
SPOKScreen_Obj.GetString(1,80,24,80,,Block)
SPOKScreen_Obj.WaitHostQuiet (g_HostSettleTime)
Excel_Sheet.Cells(Excel_Row, 18).Value = result
SPOKScreen_Obj.WaitHostQuiet (g_HostSettleTime)

when pasting back into excel, try thinking like this A1=1, B1=2 and so on..
So location R1 is 18,,,
try this first and let me know how get on.....

 

[halobender]

spok2 again thanks for the wonderful post. I have yet to try this new post but should find time to test it tomorrow. I am wondering however that if this does work how can i remove the input box and just always have it start at 1. Also from what i see here is that this will send keys to extra wcad and then copy screen and paste to row 18 or R1 correct? if that is the case how do i get WCAD screen copy to paste to 1 and then add send keys <home>IAPS<enter> paste to 18?

LastExcelRow = Excel_Sheet.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1).Row
For Excel_Row = InputBox("Input Starting Row") To LastExcelRow
SPOKScreen_Obj.SendKeys ("<home>WCAD<enter>")
SPOKScreen_Obj.GetString(1,80,24,80,,Block)
SPOKScreen_Obj.WaitHostQuiet (g_HostSettleTime)
Excel_Sheet.Cells(Excel_Row, 1).Value = result
SPOKScreen_Obj.WaitHostQuiet (g_HostSettleTime)
SPOKScreen_Obj.SendKeys ('<home>IAPS<enter>">)
SPOKScreen_Obj.GetString(1,80,24,80,,Block)
SPOKScreen_Obj.WaitHostQuiet (g_HostSettleTime)
Excel_Sheet.Cells(Excel_Row, 18).Value = result
SPOKScreen_Obj.WaitHostQuiet (g_HostSettleTime)

I would think it would be accomplished by doing something like that but if you help me remove input box and have it auto start at 1 then i may need to alter something. again thanks in advance!

 

[halobender]

okay spok2 i tried the code you last gave me and it does not like the following line

SPOKScreen_Obj.GetString(1,80,24,80,,Block)


says that it expects = and if i try = then it wants ( and if i try .value = then it just errors.. any clues?

here is a screen shot of the problem in action

http://img512.imageshack.us/img512/9209/image001ji4.jpg

 

[iSPOK]

sorry my mistake,, try this
SPOKScreen_Obj.GetString(1,1,24,80)

 

[halobender]

looks good but what about sending keys <Home>IAPS<Enter> where would i add that

 

[iSPOK]

this depends on what your doing with,,IAPS,, are you just switching to that screen or you now need to put the data from excel into it.....or you now need to copy this screen as well, and put the data somewhere??

 

[halobender]

I need to have exel go to extra and send keys <home>wcad<enter> copy screen then go back to excel and put that in a1, then have excel go back to extra and send keys <home>iaps<enter> copy screen then go back to excell and put that in r1. after which i have a bunch of formulas that will control the numbers and output different information into certain cells.. once this information is placed i want to set up seperate macros maybe per buttoned objects that you can click that will send keys to extra <home>aame<enter><tab><tab>written text and ('Sheet1!'A1) written text (more cell data)<enter>

 

[halobender]

alright spok2 so far loving the suggestions you have made towards helping me get this up and running.. however i am still basically at base one. Here is the present error for the code you suggested to me. Basically what i did was remove that area of the original code and replaced with yours. Now it is erroring, so i added value = to your code and got it working kinda.. however it was wrong so i deleted it and am stuck the code remains as it is seen in the following screen. When the error wasnt happening i was able to copy strings only from extra where i actually need full screens into more than just one cell (as in start in a1 but fill an area from a1-a15-a1-p1 full rectangle). If i understood how this loop thing works exactly that would help tons.

http://img462.imageshack.us/img462/6924/image002bc6.jpg

 

[IknowMe]

Try the following code halobender. If this is what your trying to do we can flesh it out further.

Sub Main()
'Extra Screen Object
Dim ExtraScreen As Object
'On Error GoTo ErrorThis
Set ExtraScreen = CreateObject("EXTRA.System").ActiveSession.Screen
'Assumes an open Extra Session

'Excel Worksheet object
Dim FinanceSheet As Worksheet
FinanceSheet = Workbooks("financetest").Worksheets(1)
'Assumes named workbook is open

ExtrScreen.Sendkeys "<home>wcad<enter>"

For ExtraPage = 1 To 2 'Well add more pages later
    FinanceSheet.Cells(ExtraPage, 1).Value = ExtraScreen.Area(1, 1, 24, 80).Value
    ExtraScreen.Sendkeys "<home>iaps<enter>"
Next ExtraPage

End Sub

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.