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]

That won't work try this.

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
Set FinanceSheet = Workbooks("book1").Worksheets(1)
'Assumes named workbook is open

ExtraScreen.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, 1, 3).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.

 

[halobender]

WOW works beautifully however I had to add a few things.. this is what it sits at right now.

Sub MATCC()
'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
Set FinanceSheet = Workbooks("name.xls").Worksheets(1)
'Assumes named workbook is open

ExtraScreen.SendKeys "<home>wcad<enter>"
ExtraScreen.WaitHostQuiet (g_HostSettleTime)

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

End Sub


Okay like I said this is working great however now that I have this working I have decided that this isnt going to work the way i was hoping.. When it pastes the information it is pasting it all in 1 cell so A1 gets info from WCAD and A2 gets info from IAPS. Needless to say the FIND and TRIM to make this work the way I want would be near impossible to understand.

So I am wondering first do any of you guys hang around on any IRC servers if so which and what rooms? I have some free time this weekend and would love a quick lesson in string pulls!

Next I really need to learn how to grab very direct strings like from wcad screen get string 15/02 word start to word end 15/09. and paste that to excel sheet 1 R15 and so forth.

Can this code be altered to get strings instead of whole screens?

 

[halobender]

Ok, I've changed my code to try and just grab the individual sections I need, and throw them in seperate cells. I tried three different ways to do it, but haven't had a chance to run it and see which if any actually do what they're supposed to. The main difference between the first two ways is the ", 1, 3" in an area grab, because I honestly have no idea what it's for. Could you explain what the values are for, or point me to somewhere that does? I'm having a rough time finding it.

Sub MATCC()

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

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

ExtraScreen.SendKeys "<Home>WCAD<Enter>"
ExtraScreen.WaitHostQuiet (g_HostSettleTime)

'WCAD

'*** AREA GRAB 1 ***
FinanceSheet.Cells(3, 1).Value = ExtraScreen.Area(3, 43, 3, 55).Value 'BA
FinanceSheet.Cells(3, 2).Value = ExtraScreen.Area(4, 43, 4, 55).Value 'PD
FinanceSheet.Cells(3, 3).Value = ExtraScreen.Area(5, 43, 5, 55).Value 'OL
FinanceSheet.Cells(3, 4).Value = ExtraScreen.Area(8, 43, 8, 55).Value 'DU

'*** AREA GRAB 2 - wtf is , 1, 3 for? ***
FinanceSheet.Cells(7, 1).Value = ExtraScreen.Area(3, 43, 3, 55, 1, 3).Value 'BA
FinanceSheet.Cells(7, 2).Value = ExtraScreen.Area(4, 43, 4, 55, 1, 3).Value 'PD
FinanceSheet.Cells(7, 3).Value = ExtraScreen.Area(5, 43, 5, 55, 1, 3).Value 'OL
FinanceSheet.Cells(7, 4).Value = ExtraScreen.Area(8, 43, 8, 55, 1, 3).Value 'DU

'*** GET STRING ***
FinanceSheet.Cells(11, 1).Value = ExtraSession.GetString(3, 43, 13) 'BA
FinanceSheet.Cells(11, 2).Value = ExtraSession.GetString(4, 43, 13) 'PD
FinanceSheet.Cells(11, 3).Value = ExtraSession.GetString(5, 43, 13) 'OL
FinanceSheet.Cells(11, 4).Value = ExtraSession.GetString(8, 43, 13) 'DU

'IAPS

ExtraScreen.SendKeys "<Home>IAPS<Enter>"
ExtraScreen.WaitHostQuiet (g_HostSettleTime)

FinanceSheet.Cells(14, 1).Value = ExtraScreen.Area(15, 6, 15, 21, 1, 3).Value 'type 1
FinanceSheet.Cells(16, 1).Value = ExtraScreen.Area(17, 2, 17, 13, 1, 3).Value 'adb
FinanceSheet.Cells(16, 2).Value = ExtraScreen.Area(17, 29, 17, 33, 1, 3).Value 'apr

FinanceSheet.Cells(18, 1).Value = ExtraScreen.Area(19, 6, 19, 21, 1, 3).Value 'type 2
FinanceSheet.Cells(20, 1).Value = ExtraScreen.Area(21, 2, 21, 13, 1, 3).Value 'adb
FinanceSheet.Cells(20, 2).Value = ExtraScreen.Area(21, 29, 21, 33, 1, 3).Value 'apr

'Grab more info if pg2 exists, send f8 and recapture
If ExtraSession.GetString(15, 50, 4) = "MORE" Then
    ExtraScreen.SendKeys "<F8>"
    ExtraScreen.WaitHostQuiet (g_HostSettleTime)
    
    'recapture
    FinanceSheet.Cells(22, 1).Value = ExtraScreen.Area(15, 6, 15, 21, 1, 3).Value 'type 3
    FinanceSheet.Cells(24, 1).Value = ExtraScreen.Area(17, 2, 17, 13, 1, 3).Value 'adb
    FinanceSheet.Cells(24, 2).Value = ExtraScreen.Area(17, 29, 17, 33, 1, 3).Value 'apr

    FinanceSheet.Cells(26, 1).Value = ExtraScreen.Area(19, 6, 19, 21, 1, 3).Value 'type 4
    FinanceSheet.Cells(28, 1).Value = ExtraScreen.Area(21, 2, 21, 13, 1, 3).Value 'adb
    FinanceSheet.Cells(28, 2).Value = ExtraScreen.Area(21, 29, 21, 33, 1, 3).Value 'apr
    
End If

End Sub

 

[halobender]

YIPPEE it is complete thanks to all you great peoples at

http://www.tek-tips.com,

after a few weeks and several headaches here is the finalized code.. Not bad for a guy that doesnt know alot about vba.

Sub MATCC()

'Extra Screen Object
Dim ExtraScreen As Object
'On Error GoTo ErrorThis
Set ExtraScreen = CreateObject("EXTRA.System").ActiveSession.Screen

'Excel Worksheet Object
Dim FinanceSheet As Worksheet
Set FinanceSheet = Workbooks("MFPCalculator.xls").Worksheets(1)
'Assumes named Workbook is open

ExtraScreen.SendKeys "<Home>WCAD<Enter>"
ExtraScreen.WaitHostQuiet (g_HostSettleTime)

'*** WCAD ***
FinanceSheet.Cells(5, 28).Value = ExtraScreen.Area(3, 2, 3, 37).Value     'NAME
FinanceSheet.Cells(8, 28).Value = ExtraScreen.Area(2, 22, 2, 27).Value    'QUEUE
FinanceSheet.Cells(10, 28).Value = ExtraScreen.Area(3, 43, 3, 55).Value   'BALANCE
FinanceSheet.Cells(12, 28).Value = ExtraScreen.Area(4, 43, 4, 55).Value   'PAST DUE
FinanceSheet.Cells(14, 28).Value = ExtraScreen.Area(5, 43, 5, 55).Value   'OVER LIMIT
FinanceSheet.Cells(16, 28).Value = ExtraScreen.Area(8, 43, 8, 55).Value   'STATEMENT DUE
FinanceSheet.Cells(18, 28).Value = ExtraScreen.Area(11, 39, 11, 41).Value 'DAYS PAST DUE
FinanceSheet.Cells(20, 28).Value = ExtraScreen.Area(12, 39, 12, 39).Value 'BROKEN PROMISES
FinanceSheet.Cells(22, 28).Value = ExtraScreen.Area(13, 39, 13, 39).Value 'NSF PAYMENTS

'***IAPS***
ExtraScreen.SendKeys "<Home>IAPS<Enter>"
ExtraScreen.WaitHostQuiet (g_HostSettleTime)

FinanceSheet.Cells(6, 31).Value = ExtraScreen.Area(3, 53, 3, 61).Value    'MINIMUM PAYMENT
FinanceSheet.Cells(8, 31).Value = ExtraScreen.Area(4, 73, 4, 80).Value    'DUE DATE
FinanceSheet.Cells(10, 31).Value = ExtraScreen.Area(4, 15, 4, 16).Value   'BILLING DAYS
FinanceSheet.Cells(12, 31).Value = ExtraScreen.Area(3, 73, 3, 80).Value   'STATEMENT DATE

FinanceSheet.Cells(15, 30).Value = ExtraScreen.Area(15, 2, 15, 21).Value  'TYPE 1
FinanceSheet.Cells(15, 31).Value = ExtraScreen.Area(17, 2, 17, 13).Value  'ADB 1
FinanceSheet.Cells(15, 32).Value = ExtraScreen.Area(17, 29, 17, 33).Value 'APR 1

FinanceSheet.Cells(18, 30).Value = ExtraScreen.Area(19, 2, 19, 21).Value  'TYPE 2
FinanceSheet.Cells(18, 31).Value = ExtraScreen.Area(21, 2, 21, 13).Value  'ADB 2
FinanceSheet.Cells(18, 32).Value = ExtraScreen.Area(21, 29, 21, 33).Value 'APR 2

'Grab more info if IAPS <<MORE>> exists, send f8 and recapture
If ExtraScreen.Area(15, 50, 15, 53) = "MORE" Then
    ExtraScreen.MoveTo 15, 2
    ExtraScreen.WaitHostQuiet (g_HostSettleTime)
    ExtraScreen.SendKeys "<PF8>"
    ExtraScreen.WaitHostQuiet (g_HostSettleTime)
    
    'Recapture
    FinanceSheet.Cells(21, 30).Value = ExtraScreen.Area(15, 2, 15, 21).Value  'TYPE 3
    FinanceSheet.Cells(21, 31).Value = ExtraScreen.Area(17, 2, 17, 13).Value  'ADB 3
    FinanceSheet.Cells(21, 32).Value = ExtraScreen.Area(17, 29, 17, 33).Value 'APR 3

    FinanceSheet.Cells(24, 30).Value = ExtraScreen.Area(19, 2, 19, 21).Value  'TYPE 4
    FinanceSheet.Cells(24, 31).Value = ExtraScreen.Area(21, 2, 21, 13).Value  'ADB 4
    FinanceSheet.Cells(24, 32).Value = ExtraScreen.Area(21, 29, 21, 33).Value 'APR 4

End If

'Command back to WCAD screen
        ExtraScreen.WaitHostQuiet (g_HostSettleTime)
        ExtraScreen.SendKeys "<Home>WCAD<Enter>"

End Sub

 

[IknowMe]

Glad you got it all worked out. Sorry I wasn't more responsive but I was out of town and away from computer all weekend.

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