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]

By pressing Pre from the above list?

 

[SkipVought]

In fact here's what I often did to aid in determining the row/column and for my documentation of the system I was generating. Here's just rows 1 & 24 hypothetically and the other helps I made

[pre]
+--+--------------------------------------------------------------------------------+--+
| |----+---10----+---20----+---30----+---40----+---50----+---60----+---70----+---80| |
| +--------------------------------------------------------------------------------+ |
|01| CRJ01A2 S U M M A R Y S T A T E M E N T S 02/02/15 |01|

|24| MSG: |24|
| +--------------------------------------------------------------------------------+ |
| |----+---10----+---20----+---30----+---40----+---50----+---60----+---70----+---80| |
+--+--------------------------------------------------------------------------------+--+
[/pre]

 

[SkipVought]

We have TOO MANY CONVERSATIONS going without resolving any!

Regarding PRE tgml:

ENTER in the text you want to enclose in PRE tags

SELECT that text

Hit the Pre control above the reply window.

Check your post by hitting the [highlight #EF2929]Preview[/highlight] control

 

[SkipVought]

Now lets use your summary screen shot to address the next issue of loading the data into columns B, C & D.

 

[Tommeck37]

I am out of the office at the moment so I am not able to paste the screen here now

I think all localization of data is included in the vba. I cannot give you more than that until tomorrow.

Is there a way to make the code

ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "C").End(xlUp).Offset(1, 0).Value

copy to first empty cell starting from C9 and not C31. That will resolve the issue.

I will put here whatever you want tomorrow

 

[SkipVought]

so the sheet rows to process are 10:30, correct?

Can we assume that if we COUNTA() the range in a column for those rows, that that count+1 will reference the next row in that range to populate? In other words, you will NEVER EVER skip a row in that range, correct?

 

[Tommeck37]

Assumptions correct

I will never skip a row in a table.

 

[Tommeck37]

So what is the code?

 

[SkipVought]

okay, within your For...Next to process sheets, you need a Do...Loop to process the screen rows that are repeated in the 3 columns of data. I cant tell what rows they are but that number of time is the max number of times to loop and the min number of times will be when the date is EMPTY--so exit do.

In each of the iterations of this loop you will GetString for the date, Credit and/or Debit and assign to the referenced row in your sheet.

So before entering this loop get the row offset

lRow = Application.COUNTA(ThisWorkbook.Sheets(i).Range(Cells(10, "C"), Cells(30, "C"))) + 1

rw=???    'whatever screen row 
Do Until Trim(GetString(rx, ???, 8)) = "" or rw > ???   'when the date is EMPTy whatever column that is
   ThisWorkbook.Sheets(i).Cells(10+lRow."C").value = GetString(whatever)
   lRow = lRow + 1     'incriment the sheet row
   rw = rw + 1         'incriment the screen row
Loop

 

[SkipVought]

Sorry for the rx rather than rw in the GetString

 

[Tommeck37]

many thanks I will try it out tomorrow once in office, of course I will get you posted

 

[Tommeck37]

[pre] COP041D S T A T E M E N T S S U M M A R Y 03/02/15

CURRENCY : CORRESPONDENT : CRGWP STATEMENT DATE : 30/01/15

FIRST OPENING BALANCE : 7,665,530.19 CR
LAST CLOSING BALANCE : 9,909.16 CR

AVAILABLE BALANCE : 9863.52 CR

VALUE DATE CREDIT DEBIT
---------- ----------------------- -----------------------
30/01/15 6,500,000.00 14,155,666.67
01/02/15 45.64 0.00









PF1 : UP PF2 : DOWN PF3/ENTER : RETURN


[/pre][/pre]

 

[Tommeck37]

I included the copy within pre tags but it is anyway no right I think

 

[SkipVought]

Thanks for the screen copy. It has already come in handy.

Here's my question about your screen navigation (getting from the initial to summary:

In the initial screen you enter your 3 pieces of info and hit ENTER

If all is well, the summary screen is displayed with data related to the info you entered on initial.

If all is NOT well, then tell me exactly what happens. What message on what screen and where. From what I take the message is on the initial screen at 23, 16

 

[Tommeck37]

hello,

so... if there is no data in summary screen there is a short info displayed at the bottom of screen
then I ask the loop to go out and start again with the next sheet (bank)

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

Yes the message is on the same, initial screen right below all info.

 

[SkipVought]

One other question regarding screen navigation.

When the initial screen is displayed, where is the cursor (referred to as the cursor rest coordinates)?

Where are the cursor rest coordinates for the summary screen?

 

[Tommeck37]

when the initial screen is displaye the first place for the cursor to be is 5, 20

Rest coordinates are below the screen, in the right corner ( this part of screen is not copiable)

 

[SkipVought]

and the summary screen rest coordinates???

This is like pulling teeth!!!

BTW the coordinates in the lower RH corner are the CURRENT cursor coordinates, not necessarily the rest coordinates.

 

[Tommeck37]

Do not know where I can get that info

Apologies for me being so green in here

 

[SkipVought]

Here's what you do:

Call up your summary screen, however it is that you do that!

Then tell me where the cursor is before anything else is done to the summary screen.