Copy data from Attachmate to Excel in a Loop to a single cell in each sheet

[Tommeck37]

Hello,

I am trying to build up a big macro tool to automate process of reporting.

I have a three-days report to scrape off the screen of attachmate. I'd like to create a code that would go into session, scrape specific data from specific line of screen and paste it onto excel sheet (to each sheet, cell B10). The thing that makes it more complex is that macro has got to go into session by three parameters
1. it puts string "S" on screen
2. takes data from cell I5 and puts on the screen lines below
3. takes date from the last sheet cell A1 and puts onto screen then goes into session by sending "enter"

With the above three items it enters session and copies data. It should enter session with the above datas as many times as there are sheets in excel (with data in I5). Then it should paste the financial figure into each sheet (from which data from 2. was taken to enter session) to cell B10.

The so far code goes in to session. I am not able to copy any data to cell B10 let alone make it a loop.

Please find the excel with code attached below.

Thank you for any help

Tommeck37

 

[Tommeck37]

I don't understand your suggestions. But I know for sure it does not work.

We wrote a lot but really litlle was done. Nevermind. Thanks for trying anyway

 

[SkipVought]

If the information you posted was correct, then the loop technique for being ABSOLUTELY SURE that the mainframe has responded, works. I can categorically state that a fixed wait period will at some point fail. I discovered this truism many years ago, found this approach and have successful terminal access ever since, at the speed of VBA.

 

[SkipVought]

Here is my latest code, for what its worth, that addresses several of your 5 issues...

Sub RapidBalancesY()

    Dim Sys As Object, Sess As Object, Screen As Object
    Dim i As Integer, dzien As String, miesiac, gdzie As String
'new declartiona
    Dim lRow As Long, rw As Integer, ws As Worksheet, iMonth As Integer
        
    Set Sys = CreateObject("EXTRA.System")
    
    
    
    ' Assumes an open session
    Set Sess = Sys.ActiveSession
    Set Screen = Sess.Screen
    
    'Enters a today's date minus one day for statement date to input into sys session
    
    ThisWorkbook.Sheets("Day1").Range("A1") = WorksheetFunction.Text(Date - 1, "ddmmyyyy")
    dzien = Sheets("Day1").Range("A1").Value
    
    'Copies content of cell in order to have value date of a month
    
'this value never changes so why should if be in the loop???
    miesiac = ThisWorkbook.Sheets("Day1").Range("A2").Value
            
    'Loop for all sheets to take value of cell I5 to enter into sys session
    For Each ws In ThisWorkbook.Worksheets
        gdzie = ws.Range("I5").Value
        
        
        On Error Resume Next

'
        Screen.PutString "S", 5, 20
        Screen.PutString "     ", 13, 49
        Screen.PutString gdzie, 13, 49
        Screen.PutString dzien, 20, 51
        Screen.SendKeys ("<enter>")
        
'these coordinates are a guess from your jpg
'the cursor appears to be in screen location 1,1
'this is odd to me, but your system seems a bit odd from the ones I've seen at three aerospace companies over 30 years
        Do Until Sess.Screen.WaitForCursor(1, 1) Or Sess.Screen.GetString(23, 16, 14) = "NO INFORMATION"
            DoEvents
        Loop
      
        
        If Sess.Screen.GetString(23, 16, 14) <> "NO INFORMATION" Then   'prosess summary screen
            
            lRow = Application.CountA(ws.Range(ws.Cells(10, "C"), ws.Cells(30, "C"))) + 1
            
            rw = 12  'whatever screen row
            Do Until Trim(GetString(rw, 9, 8)) = "" Or rw > 23   'when the date is EMPTy whatever column that is
                iMonth = GetString(rw, 11, 2)
                If iMonth = Month(Date - 1) Then
                    ws.Cells(10 + lRow, "C").Value = Trim(GetString(rw, 22, 23)) * -1
                    ws.Cells(10 + lRow, "D").Value = "cr val" & GetString(rw, 9, 8)
                    
                    lRow = lRow + 1     'incriment the sheet row
                    ws.Cells(10 + lRow, "C").Value = Trim(GetString(rw, 47, 23)) * 1
                    ws.Cells(10 + lRow, "D").Value = "db val" & GetString(rw, 9, 8)
                    
                    lRow = lRow + 1     'incriment the sheet row
                    rw = rw + 1         'incriment the screen row
                End If
                
            Loop
        
            
            Screen.SendKeys "<PF3>"
            Do Until Sess.Screen.WaitForCursor(5, 20)  'rest coordinates for the initial screen
                DoEvents
            Loop
        End If
        
'now get another sheet
    Next
End Sub

 

[Tommeck37]

floggin a dead horse...

macro goes into first screen and that's all, nothing happens (first bank name only)


I think you won't help me that way, that is, writing totally new code
You do not have the system to check the code, I do, but I do not understand your code to amend it in any way

The only way I could see it possible is to work on my code which maybe is not ideal (timing etc) but is much closer to the expected result.
I am only missing one issue: macro copies all well to C10 & D10 and below but if I run the whole process again it will overwrite those data in excel. It should write further below to next empty cells. If that is achieved all job is completed. I will only need to build 4 modules for each day of processing separately and that's it. All reporting will go smoothly.

Cheers
Tommeck37

 

[SkipVought]

Here's part of your post on Feb 15.

Notice the row numbers in the [highlight #FCE94F]highlighted text[/highlight]. You have NOTHING at all in those rows ANYWHERE in ANY of your examples!!!

I cannot trust what you are posting. No wonder your results using my code based on your information would not work!

Consequently, you are on your own.

Tommeck37 (Vendor)(OP)4 Feb 15 12:58
good job

the issues are still there

1. Loop goes sheet by sheet, however, when it finds a bank that cannot open summary screen it waits as long as precisely 30 seconds, that gives a lot of time
2. the time to copy figures from the screen I think is too short as macro did not copy those banks that have many items (debits and credits)
3. macro copies only debits ([highlight #FCE94F]only line rw, 31, 17, but should also rw, 58, 1[/highlight]7
....