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]

yes I checked the other sheets, only one balance filled into the first sheet.

Also I saw on the screen only one bank was entered on and on

What is needed is step one sheet forward with data from I5 each sheet.

 

[SkipVought]

What's going on with this...

Do Until Sess.Screen.getstring(3, 41, 5) = "HKUSD"

When your macro starts, what string is in row 3 column 41?

What happens the SECOND time thru the loop?

I'd suggest that you STEP thru your macro to see exactly what is happening on the screen an in Excel.


BTW, "HKUSD" is the next to the last form sheet in your workbook.

 

[SkipVought]

I modified your code just to test the for...next loop. Of course, I cannot test your emulator either.

The code puts the value in gdzie in B10 in each sheet!

Sub RapidBalances2()
    Dim Sys As Object, Sess As Object, Screen As Object
    Dim i As Integer, gdzie As String
    
'    Set Sys = CreateObject("EXTRA.System")
'    Set xlApp = CreateObject("Excel.Application")   [highlight #FCE94F]'<<< You do not need this statement if you are running the code in Excel[/highlight]
    
    
    ' 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(Now() - 1, "ddmmyyyy")
    'Dzien = Sheets("Day1").Range("A1").Value
            
    'Loop for all sheets to take value of cell K5 to enter into sys session
    For i = 1 To ThisWorkbook.Sheets.Count - 1
        gdzie = ThisWorkbook.Sheets(i).Range("I5").Value
        
'        Do Until Sess.Screen.getstring(3, 41, 5) = "HKUSD"
'        With Screen
        
'                .PutString "S", 5, 20
'               .WaitHostQuiet (100)
'               .PutString gdzie, 13, 49
'               .WaitHostQuiet (100)
'               .PutString "22012015", 20, 51
'               .WaitHostQuiet (100)
'               .SendKeys ("<enter>")
'               .WaitHostQuiet (50)
'               ThisWorkbook.Sheets(i).Range("B10").Value = .getstring(6, 52, 16)
               ThisWorkbook.Sheets(i).Range("B10").Value = gdzie
'               .WaitHostQuiet (500)
'               .SendKeys "<PF3>"
'               .WaitHostQuiet (100)
        
'            End With
'
'        Loop
    Next i
    
End Sub

 

[Tommeck37]

Of course I'll test the code tomorrow first thing in the office.

Do Until Sess.Screen.getstring(3, 41, 5) = "HKUSD"

is another problem I will face.
This now is a temporary way to stop the Loop.

Finally, I would like the loop to stop with the end of all sheets in excel. As banks run out, the macro should stop entering screens.
But I do not know if it is ever possible to set such a condition for Do....Loop.

There many many problems. Some of the banks will not be able to be entered then the loop should recongnize this error and start next sheet loop.
The more problematic it is because the initial screen is always the same so I cannot set error resume for the loop to resume from next sheet.
It could be good if I found an error message on the screen for macro to recognize it and then go the loop again. Yes! there can be such notification at the bottom of the screen. I will have to check that thoroughly

Kind Regards
Tommeck37

 

[SkipVought]

I your Do...Loop within your For...Next a problem.

Your For...next IS advancing thru ALL the sheets in your workbook!

 

[Tommeck37]

Hello,

I've tried with

ThisWorkbook.Sheets(i).Range("B10").Value = gdzie

and it still repeats one sheet on and on but now it inputs content of cell I5 to cell B10.

I stepped trough the code but unfortunatelly it did not help me much. I saw the code executing step by step. But how to make it step sheet forward?!

I also found there is a way to find an error. There is a piece of info at the bottom of screen saying "NO INFORMATION FOUND" so it could be a break point for the loop to start again from next sheet. But until that, I have to find a way to make a loop stepping one sheet forward.

Cheers
Tommeck37

 

[SkipVought]

That code was just to test theo loop thur all the sheets. If you remone the Do...Loop you will see that every sheet has a value in B10!!!

 

[SkipVought]

It still repeats one sheet on and on"

Are we in two different universes with different laws of logic???

Have you ACTUALLY and TRUELY selected each sheet and ACTUALLY and TRUELY looked at each sheet?

 

[Tommeck37]

hi,

yes I did check the sheets

But I manage to write what is below and it works OK. It goes through all sheets and updates them
Now time for next problem. There will be a situation when macro will not be able to enter the screen. Then I will do the check with getstring = "NO INFORMATION" then I would like to make macro continue the loop with the next sheet.

So the macro goes sheet by sheet then it meets an obstacle that makes it unable to enter screen, then it sees a message at the bottom of screen No INFORMATION then it should continue with the next sheet (bank) and go like that until the end of sheets. Is it doable?

Here is what I done so far

Sub RapidBalances()
Dim Sys As Object, Sess As Object, Screen As Object
Dim i 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(Now() - 1, "ddmmyyyy")
dzien = Sheets("Day2").Range("A1").Value
        
'Loop for all sheets to take value of cell K5 to enter into sys session
For i = 1 To ThisWorkbook.Sheets.Count
gdzie = ThisWorkbook.Sheets(i).Range("I5").Value

Do
With Screen

        .PutString "S", 5, 20
       .WaitHostQuiet (10)
       .PutString gdzie, 13, 49
       .WaitHostQuiet (10)
       .PutString dzien, 20, 51
       .WaitHostQuiet (10)
       .SendKeys ("<enter>")
       .WaitHostQuiet (50)
       ThisWorkbook.Sheets(i).Range("B10").Value = .getstring(6, 51, 17)
       ThisWorkbook.Sheets(i).Range("I5").Value = gdzie
       .WaitHostQuiet (50)
       .SendKeys "<PF3>"
       .WaitHostQuiet (10)

    End With
    
 Loop Until True
Next i
    
End Sub

 

[SkipVought]

One more thing before we proceed.

Exactly what is sheet Day1 used for OTHER THAN THIS DATE?

 

[Tommeck37]

sheet Day1 is used only for the date...I am thinking of replacing it with InputBox

 

[SkipVought]

So what's wrong with

Dim sDate as String

SDate = Format(Date-1, "ddmmyyyy")

Screen.PutString sDate, 20, 51

Don't need that Day1 sheet at all! Delete it. Now your For...Next loop can be for ALL your sheets.

Second clean-up issue: your Do...Loop is totally unnecessary!

So now let's consider the Message Area. Exactly where is it usin screen coordinates AND please COPY 'n' PASTE EVERYTHING ON THAT ROW.

 

[Tommeck37]

I will remove day1 sheet.

Now I made some changes and it works fine. It recognizes an error (when macro cannot enter the screen). When it cannot enter the screen, it resumes its action and goes further. But the little problem here is that it does not restart from the first action of WITH BLOCK (which is putstring "S", 5, 20) but copies content of the screen (which is ThisWorkbook.Sheets(i).Range("B10").Value = .getstring(6, 49, 19)).

But as it cannot enter the right screen, it is still on opening screen and therefore copies incorrect data from screen.
So the sheets to which macro cannot enter the screen are updated with incorrect information.


Here is what I have so far

Sub RapidBalances()
Dim Sys As Object, Sess As Object, Screen As Object
Dim i 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(Now() - 1, "ddmmyyyy")
dzien = Sheets("Day1").Range("A1").Value
        
'Loop for all sheets to take value of cell K5 to enter into sys session
For i = 1 To ThisWorkbook.Sheets.Count
gdzie = ThisWorkbook.Sheets(i).Range("I5").Value

Do
With Screen

        .PutString "S", 5, 20
       .WaitHostQuiet (10)
       .MoveTo 13, 49
       .SendKeys "<EraseEOF>"
       .PutString gdzie, 13, 49
       .WaitHostQuiet (10)
       .PutString dzien, 20, 51
       .WaitHostQuiet (10)
       .SendKeys ("<enter>")
       .WaitHostQuiet (1000)
       ThisWorkbook.Sheets(i).Range("B10").Value = .getstring(6, 49, 19)
       If Sess.Screen.getstring(23, 16, 14) = "NO INFORMATION" Then Exit Do
       .WaitHostQuiet (50)
       .SendKeys "<PF3>"
       .WaitHostQuiet (10)

    End With
    
Loop Until True
Next i
    
End Sub

I think I need to have the Do....Loop statement. I could not recognize an error without it.

Kind Regards,
Tommeck37

 

[Tommeck37]

OK problem solved. I moved statement

If Sess.Screen.getstring(23, 16, 14) = "NO INFORMATION" Then Exit Do

line before it copies from the screen. It works ok now. It does not copy unnecesarry content.

Now... is it possible to write sort of log file with the names of banks that macro could not enter screen with?
I need this because all those banks that macro could not enter, we will have to look for the information manually which means that we would need a list of those banks that have to be updated manually.


Kind Regards
Tommeck37

 

[Tommeck37]

Currently I am struggling with figures

System shows strange names instead of - 1,500,000.00 it shows 1,500,000.00 DB
and instead of 2,000.00 it shows 2,000.00CR ( credits and debits in full words)

I am trying to translate them into normal excel signs of minus (and plus)

I am sure there is a way to do that via VBA

Kind Regards
Tommeck37

 

[SkipVought]

You parse the string and assign a sigh accordingly.

Also, you need to remove COMMAS in the string of numeric characters before assigning to a cell in Excel.

 

[Tommeck37]

yes, Replace, If and Instr but how?

 

[SkipVought]

Your examples were ambiguous. Please COPY 'n' PASTE the "strange names" so we can see EXACTLY how these values (+ & -) are displayed.

 

[Tommeck37]

Hi,

I have managed to resolve it with replace if and instr

here is the code:

Function delDBCR(textDBCR)

delDBCR = Val(Replace(Replace(Replace(Replace(textDBCR, ",", ""), " ", ""), "(", ""), ")", ""))

If InStr(textDBCR, "DB") > 0 Then delDBCR = -delDBCR
    
End Function

Sub test()
Dim tekst

For i = 1 To Sheets.Count
tekst = Sheets(i).Range("B10")
Sheets(i).Range("B10").Value = delDBCR(tekst)
Next i

End Sub

All of the above changes for example a number: 1,500,000.00DB to (1,500,000.00) and 1,500,000.00CR to 1,500,000.00


Kind Regards
Tommeck37

 

[Tommeck37]

Now the problem is this:

Macro gets into a screen. If there is "value date" lower or equal to 1 (this is input in a cell of spreadsheet) then it copies further content of line 12 column 31 and then column 58
At the same time it writes a short sentence in column D (at the same row) of spreadsheet (cr or db val value date on stt of date). Now the macro should not write it if there is no content in line 12 that is, there is no value date.
Macro goes out of loop if value date is higher that 1 and does not copy what is in line 12 column 12 and column 58. However, it still writes a sentence: cr or db val value date on stt date.

The question is How to stop processing this sentence if there is no value date in line 12 column 13?

I have tried with:

If Trim(Sess.Screen.getstring(12, 13, 2)) = "" Then Exit Do

but with no success. Macro still writes the sentence cr db val value date...

Here is the code

Sub RapidBalances()

Dim Sys As Object, Sess As Object, Screen As Object
Dim i 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(Now() - 1, "ddmmyyyy")
dzien = Sheets("Day1").Range("A1").Value
        
'Loop for all sheets to take value of cell I5 to enter into sys session
For i = 1 To ThisWorkbook.Sheets.Count
gdzie = ThisWorkbook.Sheets(i).Range("I5").Value


'Copies content of cell in order to have value date of a month
miesiac = ThisWorkbook.Sheets("Day1").Range("A2").Value
On Error Resume Next


'With Session
Do
       Screen.PutString "S", 5, 20
       Screen.WaitHostQuiet (10)
       Screen.MoveTo 13, 49
       Screen.SendKeys "<EraseEOF>"
       Screen.PutString gdzie, 13, 49
       Screen.WaitHostQuiet (10)
       Screen.PutString dzien, 20, 51
       Screen.WaitHostQuiet (10)
       Screen.SendKeys ("<enter>")
       Screen.WaitHostQuiet (100)
       If Sess.Screen.getstring(23, 16, 14) = "NO INFORMATION" Then Exit Do
       
       
       
       If Sess.Screen.getstring(12, 13, 2) <= miesiac Then
       ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Sess.Screen.getstring(12, 31, 17) * -1
       ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "D").End(xlUp).Offset(1, 0).Value = "cr val" & Sess.Screen.getstring(12, 10, 5) & " on stt" & dzien
       
       ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Sess.Screen.getstring(12, 58, 17)
       ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "D").End(xlUp).Offset(1, 0).Value = "db val" & Sess.Screen.getstring(12, 10, 5) & " on stt" & dzien
       End If
       
       If Sess.Screen.getstring(13, 13, 2) <= miesiac Then
       ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Sess.Screen.getstring(12, 31, 17) * -1
       ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "D").End(xlUp).Offset(1, 0).Value = "cr val" & Sess.Screen.getstring(12, 10, 5) & " on stt" & dzien
       
       ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Sess.Screen.getstring(12, 58, 17)
       ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "D").End(xlUp).Offset(1, 0).Value = "db val" & Sess.Screen.getstring(12, 10, 5) & " on stt" & dzien
       End If
             
       
       Screen.WaitHostQuiet (10)
       
       
       
       Screen.SendKeys "<PF3>"
       Screen.WaitHostQuiet (10)

    
    
Loop Until True
Next i
    'End With
End Sub

Regards
Tommeck37