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]

Let's start from the top.

'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

Your For...Next loop is going too far. I guess you are keeping sheet Day1, Why? Seems what we discuss gets ignored. You will notice in some previous code I posted that my For...Next loop used one less sheet count, to account for the Day1 sheet. But I guess you ignored that.

But it seems that you are doing something else with Day1 sheet in A2. But what is it? It's just magically there, without reference to anything else. So can you please explain how A2 is populated?
And...

[highlight #FCE94F]If there is "value date" lower or equal to 1[/highlight] (this is input in a cell of spreadsheet) then it copies further content of line 12 column 31 and then column 58

What is [highlight #FCE94F]THIS?[/highlight] Please explain in detail.

I need the answers to these two questions before proceeding.

 

[Tommeck37]

Hi,

I had to ignore

For i = 1 To ThisWorkbook.Sheets.Count - 1

as I have to have a condition to leave a loop in the middle:

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

.
If it is possible to replace that with something in For i loop then I am OK.

As far as Day1 sheet goes, I think I will have to have such last sheet as I will have to instal few buttons there for executing modules separately. As you might not know, the report will be generated in three working days so it will need three dates of statements, and also other three othe data to use for the code to perform.
The cell As in sheet Day1 is just a date to compare to.
On the first day of the report, I have to copy all debits and credits from the statement from the last but one day of a month that have value date in the future, which means that value date belongs to next month (e.g. February)
Then on second day I have to copy debits and credits from the statement from last day of a month.
Then there is third day and the last fourth day in which I copy cr and db that have past value day, which means value dates of January, as I will be then in February.

So... all in all what I need is an exit from the middle of the loop if certain condition is met.

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

This means that in each line there is a value date (e.g. 20/01/2015), amount of credit (e.g. 1,500,000.00) and amount of credit (e.g. 2,000,000.00) all in separate columns. My job is to copy only those credit and debits that meet criteria of future or past value date in the leftmost column of screen.
Those amounts are copied to spreadsheet to column C, then a sentence is written to column D, but only when there is credit or debit copied. But now the macro copies sentences to column D whether there is credit or debit in column C or not. It should not if there is not.

Kind Regards
Tommeck37

 

[SkipVought]

Exit Do only gets you out of a Do...Loop. Yer still in the For...Next loop!!!

Please COPY the emulator SCREEN and paste it here using PRE TGML tags for proper spacing. Then refer to the data on the screen for your explanation of what you need.

 

[Tommeck37]

I am sorry I cannot paste data from banking system onto the internet

Secondly, I am currently at home so have no such system available. What I can do is try reflect this from memory in paint. Will that do?

 

[SkipVought]

Paint is not good. A picture is not good.

How would, "from memory in paint" be easier than actual characters???

What is good is where it is easy to count rows and columns.

 

[Tommeck37]

I will check tomorrow once I am in office.

 

[Tommeck37]

But before that...

Is it possible to Copy content of screen to spreadsheet cell C10 and then to the next empty cell.

I have a code

ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Sess.Screen.getstring(12, 31, 17) * -1

But this copies to cell C32 as there is formula in Cel C31. Cells between C10 and C30 are empty and there I would like to copy data to.

Is it possible to sent a initial cell to count from empty cells?

I tried with

 

[Tommeck37]

...CONTINUATION

ThisWorkbook.Sheets(i).Cells(Sheets(i).Rows.Count, "C10").End(xlUp).Offset(1, 0).Value = Sess.Screen.getstring(12, 31, 17) * -1

but it does not work

Kind Regards
Tommeck37

 

[SkipVought]

interestingly in my first reply is this comment...

[b]
'what happens next time when data is already in B10? will the data go into B11?[/b]
            ThisWorkbook.Sheets(i).Range("B10").Value = .GetString(5, 20, 10)

So the same question arises with respect to the [highlight #FCE94F]'+ TO BE BOOKED - FUTURE VALUE (2)*[/highlight] column.

So you get a value every time from Sess.Screen.getstring(12, 31, 17) * -1

Do you want that SAME VALUE in C10:C30?

I'm sitting here TOTALLY BLIND to what's going on and your explanations are woefully scant! I'v been seeing all kinds of potential issues like this and with respect to waiting, not just for a fixed period, but until the system responds, and the like and you go on as if nothing has been said. How long did you insist, "The below code loops but does not step one sheet forward." when my code put stuff on every sheet???

I am so in the dark! lets get some concrete examples (a specific screen with characters, sanitized and posted here)

Until that happens, I'm out.

 

[Tommeck37]

In that case I will get back once I'm in office with all the screens etc

 

[SkipVought]

 

[Tommeck37]

...so I am with the print screens...

I attached two files below as I am not able to do the tgml

 

[Tommeck37]

...second screen... more important

 

[Tommeck37]

So there are several problems.

Let's begin with the two.

Firstly, macro copies figures (debits and credits) from screen (line 12, 31 and 12, there are many more lines like 13, 31 and 13, 58 then 14, 31 and 14, 58 all until twenty, this is how many can be viewed at one screen)
It copies those figures to column C. Now macro copies them to next empty cell. The problem is that is should copy to C10, C11, C12t etc but it copies to next empty cell. As there is formula in C31 it copies to the next empty cell which is C32. This is out of table. It should be included in table in cell C10 etc.
I guess the ideal resolution would be to copy to next empty cell starting from C9. How to achieve that?


Second problem is that at the same time while the above figures are copied, there is a one sentence (for one figure) written in column D. The form of the sentence is "cr or db val, here is value date given, on stt, date of statement is given (the same date with variable dzien). Problem here is that it should write those sentences only when figures are copied to column C. Now it copies even when there are no figures to copy from. So when column C is empty, column D has got unnecesarry sentences like db val 30/01 on stt 30012015. Here I've tried with

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

by going out of the loop if there is no figures in line. Precisely if there is no value date written on the screen then the loop should go out of screen and proceed with next sheet.

To sum up, 1st problem is to set up first non-empty cell as C9 and not any other, second problem is to close loop if there are no figures on screen. Second seems simple but I do not know why, macro ignores my

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

.

I hope I was clear enough. If not, let me know

Regards
Tommeck37

 

[Tommeck37]

forgot about the file here it is

 

[SkipVought]

so you send me PICTURES when I specifically stated that I didn't want pictures! When I need to verify where 13, 49 is on the screen, for instance, a picture is practically USELESS!

No ideal, but it will do.

So you load the 3 items in the initial screen and hit ENTER and WAIT UNTIL the summary screen loads.

Then you scrape the summary screen and put data on the appropriate sheet. As far as I know, you seem to want to put data in columns B, C, D. You want the data to reflect the DB or CR (- or +). It would seem to me you'd want to do the same things to G, H, I.

So lets say that you got everything off the summary into the proper sheet. Then what?

When you're coding emulator screens, its best to FIRST OFF, manage screen navigation--all the logic for handling screen messages.

 

[Tommeck37]

We can skip line 13, 49. This is the place from where I copy closing balance in format 1,000.00DB or 1,000.00CR which I then convert to -1,000.00 or 1,000.00
Pleaase ignore that. This is already done and it works fine. No need to do anything about it.

The problem is that content of summary screen does not get loaded properly into spreadsheet. There are two problems I was writing about in previous post.

If we get all data loaded propery into spreadsheet, loop will end and stop. All job will be done. I will repeate this activity for 4 dats with different dates of "dzien" variable and different value dates

All in all activity of proper copying data into spreadsheet would take place 4 times. Each day variable "dzien" would change so that I would copy statements from different days.

A possible problem can occur when I mulitply the coding for more lines. At least this is what I came across. When I made coding for more lines (more that line 12 and 13, that is, up to 20) error message showed that there is Loop without do. Strange because it was still there. But I would not get concerned with it now.

What makes the project stop are the two problems

Kind Regards
Tommeck37

 

[Tommeck37]

.....one more thing.... can you write how I should process the print screen so that I would be suitable for you

 

[SkipVought]

You COPY the entire screen. you PASTE it into the reply window between a set to PRE tgml tags.

I mocked up just what one line might look like, however I had NOTHING to copy and I'm not about to count characters and spaces. I have other things to do.

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

 

[Tommeck37]

How do I get those tags into reply window?