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

 

[SkipVought]

Tom,

Your code with modifications. I commented throughout the code.

Sub RapidBalances()
    Dim Sys As Object, Sess As Object, Screen As Object
    Dim i As Integer
    Dim sDate As String     'date for the screen
    Dim gdzie As String     'account/screen code
    
    Set Sys = CreateObject("EXTRA.System")
'since you are coding and running your procedure in Excel, this statement is unnecessary!
'you don't even use it!
'    Set xlApp = CreateObject("Excel.Application")
    
    ' 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
    
'the only reason to use ThisWorkbook is when you are referencing other workbooks in your code.
'notice that you fail to reference ThisWorkbook in the next line.
'I'd rather see this code like this, as you are overwriting the value in A1
'Date is a reserve word!
'A real date in Excel is much more useful than text.  Is this sheet used for anything else?
    ThisWorkbook.Sheets("Day1").Range("A1") = Date - 1
    sDate = Format(Date - 1, "ddmmyyyy")
            
    '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("K5").Value        'in your post you said I5???
        
        With Screen
            .PutString "S", 4, 19
            .PutString gdzie, 20, 51
            .PutString sDate, 20, 27
            .SendKeys ("<enter>")
            
            Do Until .WaitForCursor(4, 19)
                DoEvents
            Loop
'what happens next time when data is already in B10? will the data go into B11?
            ThisWorkbook.Sheets(i).Range("B10").Value = .GetString(5, 20, 10)
        End With
    Next i
    
End Sub

Let me say this. I've done screen scraping for about 15 years. until my firm converted their mainframe system to SAP. I found myself doing something similar to what you have in your workbook. And then someone else had a different form for some other screen and another had data to get from one screen to access yet another. So I was constantly making custom programs to do scraping tasks. And these tasks all started with lists if hundreds or thousands of data items.

So I sat down with myself one day and I said, "Self, there must be a better way!" and I designed a workbook into which I would put all my scrapings, a sheet for each screen (one row per screen for ALL the data on the screen). So when I would process a list of 100 ithes, I'd get my sheet with 100 rows of data. Then if my customer wanted only three items from the screen, That's the only bitty modification I'd have to make to get him that list.

But that's just scratching the surface. I wanted to have ONE program to do this wholesale scrapping process for ANY screen with a minimal of setup for a brand new screen I had never processed before and nearly ZERO setup for screens I had already done. So I designed a table that describes each screen (takes about 5 minutes to enter the data ONE TIME)
[tt]
SCREEN NAME
FIELD NAME
ROW FROM
ROW THRU
COLUMN
LENGTH
TEXT/NUM
[/tt]
I was going to add a tag for the Field where the Screen Rest Coordinates reside (where the cursor is when the system returns control)

Then I designed several functions: GetTableForScreen, GetFieldData and I forget there were about 6 I think. And finally the main function that reads my list in Excel and grabs the screen data and writes it into the appropriate Excel sheet.

With this kind of system, I could turn almost any user request in less than a day, even a new one!

Bottom line: Your situation is very specific. If you are sure that this is the ONLY scrapper you will ever do, proceed as intended.

However, your accomplishment will precede you and you will likely be asked to do other scrapping tasks. Take heed!

 

[Tommeck37]

Let me put it different way.

The main idea with this macro is to update each sheet with closing balance from screen. Each sheet represents one bank. To get closing balance for one sheet, macro needs to get into one screen. One sheet = one screen. In order to get into one screen, macro has got to putstring three pieces of info.
1. letter "S", wchich stands for summary
2. abbreviation of bank's name ( this resides in cell I5, yes there was an error in code, this is I5, not K5)
3 and date

Macro should enter session as many times as there are sheets
e.g. sheet 8009 = closing balance from session into cell B10
then macro should take another sheet 80010 and enter closing balance from screen into cell B10 of sheet 80010
The process should repeat through all sheets.

I did not mention in my first post, there can be a circumstance that macro would not be able to enter particular bank's screen of closing balance as the date might be incorrect. In this case macro could resume to next sheet (next bank), and inform somehow that the specific sheet was not updated. It could be a logfile with missing updates for sheets. For example: Closing balance missing for sheets 80015, 80017. ( the number of all sheets amounts to around sixty pieces)

Thanks for the idea with the date function. I was not aware of this.

This is not the end of the report as this is only one day of three.
Later on report is to be updates with balances that has got a specific range of dates assigned to. But this later on...


I used to work on SAP. That is much more human technology that mainframe. That's for sure. The capabilities of SAP make such things a lot more easier.
The thing is that I sort of got back in technology progress and now have to work on mainframe. Such a fate :/
The good thing about it is that this is only one of the two reports that can be modernized by macro. But definitely that would change my team's life.


Thanks

Tommeck37

 

[SkipVought]

So where is the problem?

 

[Tommeck37]

The problem is that macro does not update the sheets.

The macro goes into the right screen and that's all. It does not getstring and paste into cell B10.

I think the loop is incorrect but this is only my guess.

 

[SkipVought]

Please post the code that you are using to update the sheet.

You original code was incorrect!

 

[Tommeck37]

The date that the codes input to cell A1 is "42 027.00" this is not even a date.


Further code I will be able to test only on Monday. Will get you posted shortly

 

[Tommeck37]

okie dokie date is a date

but the format is not the one system will accept it has to be like this 23012015 which is not available as date in excel I think

 

[SkipVought]

What code are you using that gives you these results???

 

[Tommeck37]

ThisWorkbook.Sheets("Day1").Range("A1") = Date - 1
sDate = Format(Date - 1, "ddmmyyyy")


I remember now that I have went it through during the past week. Even if you force the date to look like that 23012015, excel macro will still take it as different (sorry I don't remember which precisely one) so the best solution is put it as text

But this is not a problem, the main problem is probably getstring and looping

 

[SkipVought]

The date that the codes input to cell A1 is "42 027.00" this is not even a date."

If A1 has a custom number format, then that's what you'll see. You need a simple Date Number Format in Excel, because DATES ARE NUMBERS, not what you see displayed.

The Format() function in VB converts the Date Serial value to TEXT like 23012015. If this last sheet has no other function than to hold this date for the macro, then the sheet is useless.

The problem is that macro does not update the sheets.

The macro goes into the right screen and that's all. It does not getstring and paste into cell B10.

What code produced this result? As I stated previously, your code was incorrect!

 

[SkipVought]

The date that the codes input to cell A1 is "42 027.00" this is not even a date."

I didn't get to thinking about it until now, but 42027 is Friday's DateSerial Value! So you DO have a Custom Number Format on that cell.

FYI: faq68-5827

 

[Tommeck37]

That's true

You can force make it custom and look as you want. If I force date 2015-01-23 to be custom and look like 23012015 the macro will still take it as 2015-01-23.
I have tried that few days ago. The only way I find reliable is to input it as text. Then macro really take it as it looks like.

 

[SkipVought]

That is what my code...

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

Does!

 

[Tommeck37]

Hello,

So the macro now goes into screen (only one as I think "going out does not work" so it only goes inside and cannot go out this should be achieved by pressing F3 but I have probably incorrect code for this keystroke) and moreover, it updates all sheets with the same figure which is incorrect. It should update one sheet per one screen (one sheet = one bank)

Do you know what is the correct function keystroke for F3 in code?

Kind Regards
Tommeck37

 

[Tommeck37]

almost forgot about the code;


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")
    Set xlApp = CreateObject("Excel.Application")


' 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


With Screen

        .PutString "S", 5, 20
       .WaitHostQuiet (100)
       .PutString gdzie, 13, 49
       .WaitHostQuiet (100)
       .PutString "22012015", 20, 51
       .WaitHostQuiet (100)
       .SendKeys ("<enter>")
       .WaitHostQuiet (1000)
       ThisWorkbook.Sheets(i).Range("B10").Value = .GetString(6, 52, 16)
       .WaitHostQuiet (1000)
       .SendKeys ("{F3}")

    End With
Next i
    
End Sub

 

[SkipVought]

<F3>"

However, your particular emulator may have different mapping for PF3, which may be what you really need.

And again, you are needlessly waiting after each PutString, AND, worse yet, you are simply waiting a fixed duration after each SendKeys and then blindly proceeding without regard to the RESOPNSE of the mainframe. You may be waiting too long (ech, so your procedure runs slow, ech) or not long enough (woah, now yer losin' data, pard'!)

 

[Tommeck37]

at my side "<PF3>" works fine

Regardless the speed, macro goes into the screen from first sheet goes out and copies the balance into the correct cell.
That's some success! However, the road is still long.

Would you perhaps know how to make the loop go through all the sheets of the excel spreadsheet the check all the banks and update the corresponding sheets? The below code loops but does not step one sheet forward.

Sub RapidBalances()
Dim Sys As Object, Sess As Object, Screen As Object
Dim i As Integer
    Set Sys = CreateObject("EXTRA.System")
    Set xlApp = CreateObject("Excel.Application")


' 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 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)
       .WaitHostQuiet (500)
       .SendKeys "<PF3>"
       .WaitHostQuiet (100)

    End With
    
    Loop
Next i
    
End Sub

Regards
Tomek

 

[SkipVought]

"...does not step one sheet forward"

Did you actually check the other sheets?

 

[SkipVought]

Regardless the speed..."

You could cross a street safely 1000 times by merely waiting 5 seconds. But the time WILL COME that merely waiting 5 seconds before entering the thoroughfare will result in an unintended fatal event!