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

Question

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

Tommeck37 (Vendor)

(OP)

24 Jan 15 10:13

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

http://files.engineering.com/getfile.aspx?folder=9472681f-5bb4-4f4e-b87b-8e

SkipVought (Programmer) · Answer 1 · 2015-01-24 16:33:58

Tom,

Your code with modifications. I commented throughout the code.

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)

SCREEN NAME
FIELD NAME
ROW FROM
ROW THRU
COLUMN
LENGTH
TEXT/NUM

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 (Vendor) · Answer 2 · 2015-01-24 19:05:11

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 (Programmer) · Answer 3 · 2015-01-24 19:11:04

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

SkipVought (Programmer) 24 Jan 15 19:11

So where is the problem?

Tommeck37 (Vendor) · Answer 4 · 2015-01-24 19:15:28

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 (Programmer) · Answer 5 · 2015-01-24 19:28:50

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

You original code was incorrect!

Tommeck37 (Vendor) · Answer 6 · 2015-01-24 19:35:39

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 (Vendor) · Answer 7 · 2015-01-24 19:41:55

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 (Programmer) · Answer 8 · 2015-01-24 20:02:34

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

SkipVought (Programmer) 24 Jan 15 20:02

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

Tommeck37 (Vendor) · Answer 9 · 2015-01-24 20:20:16

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 (Programmer) · Answer 10 · 2015-01-24 20:42:52

"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.

Quote:

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 (Programmer) · Answer 11 · 2015-01-24 22:04:43

"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: Understanding Dates and Times & why they seem to be so much trouble?

Tommeck37 (Vendor) · Answer 12 · 2015-01-25 08:18:02

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 (Programmer) · Answer 13 · 2015-01-25 10:03:00

That is what my code...

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

Does!

Tommeck37 (Vendor) · Answer 14 · 2015-01-26 08:09:47

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 (Vendor) · Answer 15 · 2015-01-26 08:13:21

almost forgot about the code;

here is the code

CODE --> vba

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 (Programmer) · Answer 16 · 2015-01-26 14:38:34

"<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 (Vendor) · Answer 17 · 2015-01-26 15:17:43

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.

CODE --> VBA

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 (Programmer) · Answer 18 · 2015-01-26 15:40:26

"...does not step one sheet forward"

Did you actually check the other sheets?

SkipVought (Programmer) · Answer 19 · 2015-01-26 15:49:55

"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!

Tommeck37 (Vendor) · Answer 20 · 2015-01-26 15:53:08

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 (Programmer) · Answer 21 · 2015-01-26 16:17:13

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 (Programmer) · Answer 22 · 2015-01-26 16:27:49

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!

CODE

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")   '<<< You do not need this statement if you are running the code in Excel
    
    
    ' 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 (Vendor) · Answer 23 · 2015-01-26 18:01:43

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

CODE --> vba

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 (Programmer) · Answer 24 · 2015-01-26 18:21:03

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

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

Tommeck37 (Vendor) · Answer 25 · 2015-01-27 07:51:37

Hello,

I've tried with

CODE --> vba

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 (Programmer) · Answer 26 · 2015-01-27 13:21:35

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 (Programmer) · Answer 27 · 2015-01-27 13:58:49

"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 (Vendor) · Answer 28 · 2015-01-27 14:08:51

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

CODE --> vba

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 (Programmer) · Answer 29 · 2015-01-27 14:16:03

One more thing before we proceed.

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

Tommeck37 (Vendor) · Answer 30 · 2015-01-27 20:08:01

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

SkipVought (Programmer) · Answer 31 · 2015-01-27 20:19:18

So what's wrong with

CODE

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 (Vendor) · Answer 32 · 2015-01-28 07:57:16

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

CODE --> vba

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 (Vendor) · Answer 33 · 2015-01-28 08:17:39

OK problem solved. I moved statement

CODE --> vba

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 (Vendor) · Answer 34 · 2015-01-28 15:32:59

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 (Programmer) · Answer 35 · 2015-01-28 18:21:00

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 (Vendor) · Answer 36 · 2015-01-28 19:38:43

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

Tommeck37 (Vendor)

(OP)

28 Jan 15 19:38

yes, Replace, If and Instr but how?

SkipVought (Programmer) · Answer 37 · 2015-01-28 22:12:32

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

Tommeck37 (Vendor) · Answer 38 · 2015-01-31 16:10:14

Hi,

I have managed to resolve it with replace if and instr

here is the code:

CODE --> vba

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 (Vendor) · Answer 39 · 2015-01-31 16:28:52

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:

CODE --> vba

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

CODE --> vba

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

SkipVought (Programmer) · Answer 40 · 2015-01-31 18:48:47

Let's start from the top.

CODE

'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...

Quote:

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

What is THIS? Please explain in detail.

I need the answers to these two questions before proceeding.

Tommeck37 (Vendor) · Answer 41 · 2015-01-31 19:16:57

Hi,

I had to ignore

CODE --> vba

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

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

CODE --> vba

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.

Quote:
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 (Programmer) · Answer 42 · 2015-01-31 19:24:15

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 (Vendor) · Answer 43 · 2015-01-31 19:56:21

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 (Programmer) · Answer 44 · 2015-01-31 22:23:36

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 (Vendor) · Answer 45 · 2015-02-01 12:46:47

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

Tommeck37 (Vendor)

(OP)

1 Feb 15 12:46

I will check tomorrow once I am in office.

Tommeck37 (Vendor) · Answer 46 · 2015-02-01 18:06:32

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

CODE --> vba

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 (Vendor) · Answer 47 · 2015-02-01 18:08:03

...CONTINUATION

CODE --> vbA

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 (Programmer) · Answer 48 · 2015-02-01 18:52:29

interestingly in my first reply is this comment...

CODE


'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)

So the same question arises with respect to the '+ TO BE BOOKED - FUTURE VALUE (2)* 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 (Vendor) · Answer 49 · 2015-02-01 18:58:39

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

SkipVought (Programmer) · Answer 50 · 2015-02-01 19:01:16

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

SkipVought (Programmer) 1 Feb 15 19:01

Tommeck37 (Vendor) · Answer 51 · 2015-02-02 14:12:11

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

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

http://files.engineering.com/getfile.aspx?folder=08a677bd-ce08-4275-aeb1-09

Tommeck37 (Vendor) · Answer 52 · 2015-02-02 14:13:36

...second screen... more important

http://files.engineering.com/getfile.aspx?folder=c1b0e5fa-64bb-4eb6-99e8-5f

Tommeck37 (Vendor) · Answer 53 · 2015-02-02 15:53:36

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

CODE --> VBA

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

CODE --> vba

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 (Vendor) · Answer 54 · 2015-02-02 15:54:54

forgot about the file here it is

http://files.engineering.com/getfile.aspx?folder=41916d63-7ff1-41ea-b9f5-1a

SkipVought (Programmer) · Answer 55 · 2015-02-02 16:09:27

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 (Vendor) · Answer 56 · 2015-02-02 16:23:01

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 (Vendor) · Answer 57 · 2015-02-02 16:23:46

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

SkipVought (Programmer) · Answer 58 · 2015-02-02 16:33:11

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.

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

Tommeck37 (Vendor) · Answer 59 · 2015-02-02 16:43:59

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

Tommeck37 (Vendor)

(OP)

2 Feb 15 16:43

How do I get those tags into reply window?

Tommeck37 (Vendor) · Answer 60 · 2015-02-02 16:50:04

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

Tommeck37 (Vendor)

(OP)

2 Feb 15 16:50

By pressing Pre from the above list?

SkipVought (Programmer) · Answer 61 · 2015-02-02 17:01:15

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

+--+--------------------------------------------------------------------------------+--+
|  |----+---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|  |
+--+--------------------------------------------------------------------------------+--+

SkipVought (Programmer) · Answer 62 · 2015-02-02 17:20:36

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 Preview control

SkipVought (Programmer) · Answer 63 · 2015-02-02 17:22:34

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

Tommeck37 (Vendor) · Answer 64 · 2015-02-02 17:54:47

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

CODE --> vbA

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 (Programmer) · Answer 65 · 2015-02-02 18:10:55

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 (Vendor) · Answer 66 · 2015-02-02 18:14:32

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

Tommeck37 (Vendor)

(OP)

2 Feb 15 18:14

Assumptions correct

I will never skip a row in a table.

Tommeck37 (Vendor) · Answer 67 · 2015-02-02 18:34:23

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

Tommeck37 (Vendor)

(OP)

2 Feb 15 18:34

So what is the code?

SkipVought (Programmer) · Answer 68 · 2015-02-02 19:23:25

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

CODE

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 (Programmer) · Answer 69 · 2015-02-02 19:29:20

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

SkipVought (Programmer) 2 Feb 15 19:29

Sorry for the rx rather than rw in the GetString

Tommeck37 (Vendor) · Answer 70 · 2015-02-02 20:25:31

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

Tommeck37 (Vendor) · Answer 71 · 2015-02-03 08:13:53

 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]

Tommeck37 (Vendor) · Answer 72 · 2015-02-03 13:04:02

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

SkipVought (Programmer) · Answer 73 · 2015-02-03 14:20:15

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 (Vendor) · Answer 74 · 2015-02-03 14:26:10

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)

CODE --> vba

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 (Programmer) · Answer 75 · 2015-02-03 14:28:40

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 (Vendor) · Answer 76 · 2015-02-03 14:39:31

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 (Programmer) · Answer 77 · 2015-02-03 15:02:18

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 (Vendor) · Answer 78 · 2015-02-03 15:10:24

Do not know where I can get that info

Apologies for me being so green in here

SkipVought (Programmer) · Answer 79 · 2015-02-03 15:16:11

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.

Tommeck37 (Vendor) · Answer 80 · 2015-02-03 15:33:24

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

Tommeck37 (Vendor)

(OP)

3 Feb 15 15:33

The cursor resides in 1, 1 position

SkipVought (Programmer) · Answer 81 · 2015-02-03 15:37:43

Okay, here's how I see it...

CODE

Sub RapidBalances()

    Dim Sys As Object, Sess As Object, Screen As Object
    Dim i As Integer
    Dim lRow As Long, rw 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
    '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 i = 1 To ThisWorkbook.Sheets.Count
        gdzie = ThisWorkbook.Sheets(i).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(ThisWorkbook.Sheets(i).Range(Cells(10, "C"), 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
               ThisWorkbook.Sheets(i).Cells(10 + lRow, "C").Value = Trim(GetString(rw, 22, 23)) * -1
               ThisWorkbook.Sheets(i).Cells(10 + lRow, "D").Value = "cr val" & GetString(rw, 9, 8)
               
               lRow = lRow + 1     'incriment the sheet row
               ThisWorkbook.Sheets(i).Cells(10 + lRow, "C").Value = Trim(GetString(rw, 47, 23)) * 1
               ThisWorkbook.Sheets(i).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
            Loop
        
            
            Screen.SendKeys "<PF3>"
            Do Until Sess.Screen.WaitForCursor(5, 20)  'coordinates for the initial screen
                DoEvents
            Loop
        End If
        
'now get another sheet
    Next i
End Sub

Tommeck37 (Vendor) · Answer 82 · 2015-02-04 12:58:32

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 (only line rw, 31, 17, but should also rw, 58, 17
4. macro copies to spreadsheet C10 and on, but...it goes to next below cells in the next sheet, so when macro reached 40th sheet (as there are around 60) the figures were then copied outside the table. It should copy from C10 and below and restart on next sheet again from C10 not C11 on next sheet
5 the last problem but not least is decision making, there is no decision as to which figures to copy, it copies all if so
I need a decision if value date is > than current month then copy figures (debits and credits) otherwise do not copy anything

I think you are close to perfect solution just those 5 points need adjustment

Kind Regards,
Tommeck37

SkipVought (Programmer) · Answer 83 · 2015-02-04 14:30:45

1. "waits precisely 30 seconds"

How can that be? I have no precise wait ANYWHERE in my code!

Tommeck37 (Vendor) · Answer 84 · 2015-02-04 14:55:15

I can confirm, there is no waiting time at all, let alone precisely 30 sec.

Odd it is

SkipVought (Programmer) · Answer 85 · 2015-02-04 14:56:56

Well then I don't understand problem 1.

Please explain so that it is clear.

Tommeck37 (Vendor) · Answer 86 · 2015-02-04 15:30:49

when it gets to sheet which there is no information in below line, then it waits that time for 30 seconds
why? do not know

why 30sec not 10sec I could ask or why not an hour? when it runs nothing specific happens on the screen

this is the culprit

CODE --> vba

Do Until Sess.Screen.WaitForCursor(1, 1) Or Sess.Screen.GetString(23, 16, 14) = "NO INFORMATION"
            DoEvents
        Loop

Nothing happens on the initial screen then it takes another sheet and gets inside swiftly until it stops again on the bank that has no summary screen

Kind Regards

SkipVought (Programmer) · Answer 87 · 2015-02-04 15:40:43

Lets go step by step here.

What's the bank code where this happens? We want to set up a test situation where we can look at this case in isolation.

The initial screen is loaded with S, the bank code and the date string, correct?

Then ENTER is sent.

Now the code is in a loop waiting until the cursor rests at 1,1 (on the summary screen) or until "NO INFORMATION" at 23,16 on the initial screen, correct?

Please answer these three questions.

Tommeck37 (Vendor) · Answer 88 · 2015-02-04 16:05:10

The first bank that has no summary screen is from sheet 80012

Answer for second question is yes
Then enter is sent

For the last question I am not sure as Icannot tell it really rests in 1, 1 I cannot see it rest anywhere. No cursor is visible then

SkipVought (Programmer) · Answer 89 · 2015-02-04 16:06:56

no cursor is visible? Then what about the cursor coordinates at Lower RH corner?

Tommeck37 (Vendor) · Answer 90 · 2015-02-05 08:20:15

when I executed step by step

CODE --> vba

Do Until Sess.Screen.WaitForCursor(1, 1) Or Sess.Screen.GetString(23, 16, 14) = "NO INFORMATION"
            DoEvents
        Loop

I do not see the cursor, if it cannot enter summary screen; letters S, bank name, and date get red and the initial screen stands still. I am not able to trace anything that lets the macro continue with the next bank

I think we confused rest position 1, 1 with summary screen. This position (1, 1 ) relates to summary screen.
The rest position for initial screen is (5, 20) it is where the letter S is positioned.
I've tried to exchange 1, 1 in code with 5, 20 but the result is even worse. The macro got inside of summary screen and stopped there or waitied for so long that I took it as if it stopped.

Kind Regards,

Tommeck37

SkipVought (Programmer) · Answer 91 · 2015-02-05 13:26:54

Here's the deal: either the screen goes to summary, where 1,1 are the rest coordinates, OR there is a mInframe error and it stays on initial...

...in which case the MESSAGE is posted in 23,16, correct?

So what's happeneng there? You have not said anything about the message.

Tommeck37 (Vendor) · Answer 92 · 2015-02-05 14:56:54

yes, when bank is empty there is system error and it stops, font gets red...

then there is message below "NO INFORMATION FOUND ***" but this you already know

SkipVought (Programmer) · Answer 93 · 2015-02-05 14:59:18

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

SkipVought (Programmer) 5 Feb 15 14:59

So does that message appear 'immediately'?

Tommeck37 (Vendor) · Answer 94 · 2015-02-05 16:13:15

yes, at once

In the meantime I checked with my code and it has resolved one issue of the two I had. Now it is only one issue to resolve.
The problem is that all data is copied to specific cell not taking into account the fact that it can already be non-empty.

My question is if it is possible to modify the below code so that it copies figures and sentences to first empty cell counting from C10 and D10 and below?

Here is the code

CODE --> vba

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 - 1
gdzie = ThisWorkbook.Sheets(i).Range("I5").Value
On Error Resume Next
Do

'With Session

       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) < 2 Then
       ThisWorkbook.Sheets(i).Range("C10").Value = Sess.Screen.getstring(12, 31, 17) * -1
       If Trim(Sess.Screen.getstring(12, 13, 2)) = "" Then Exit Do
       ThisWorkbook.Sheets(i).Range("D10").Value = "cr val" & Sess.Screen.getstring(12, 10, 8) & " on stt" & dzien
       ThisWorkbook.Sheets(i).Range("C11").Value = Sess.Screen.getstring(12, 58, 17)
       ThisWorkbook.Sheets(i).Range("D11").Value = "db val" & Sess.Screen.getstring(12, 10, 8) & " on stt" & dzien
       End If
       Screen.WaitHostQuiet (1000)
       If Sess.Screen.getstring(13, 13, 2) < 2 Then
       ThisWorkbook.Sheets(i).Range("C12").Value = Sess.Screen.getstring(13, 31, 17) * -1
       If Trim(Sess.Screen.getstring(13, 13, 2)) = "" Then Exit Do
       ThisWorkbook.Sheets(i).Range("D12").Value = "cr val" & Sess.Screen.getstring(13, 10, 8) & " on stt" & dzien
       ThisWorkbook.Sheets(i).Range("C13").Value = Sess.Screen.getstring(13, 58, 17)
       ThisWorkbook.Sheets(i).Range("D13").Value = "db val" & Sess.Screen.getstring(13, 10, 8) & " on stt" & dzien
       End If
       Screen.WaitHostQuiet (1000)
       Screen.WaitHostQuiet (50)
       Screen.SendKeys "<PF3>"
       Screen.WaitHostQuiet (10)

    
    
Loop Until True
Next i
    'End With
End Sub

The only thing missing is making code to copy data to first empty cell starting from C10 and D10 in paralel.

I am enclosing the file I have managed so far.

http://files.engineering.com/getfile.aspx?folder=6c6ede39-a520-4762-b754-17

SkipVought (Programmer) · Answer 95 · 2015-02-05 16:23:23

Strange, that now there is but one issue.

Your Do...Loop includes the initial screen and the summary screen. WHY?

The only thing that needs to loop are rows 12-22 in the summary as I posted in my code. You have removed the rw counter for those screen and the lRow counter for the sheet.

What's going on?

Tommeck37 (Vendor) · Answer 96 · 2015-02-05 16:33:10

The thing is that I do not fully understand your code.
For your code there are still 5 problems

My old code is not perfect, but almost does the necesarry job with the exception that it always copies to the specific cells. The problem comes when I have to run the process again with different date for initial screen. Then the cells will already be filled in with data so the new one will overwrite those and that' useless.

I would utilize your code for copying to next empty cell from C9 but it copies incorrectly as it goes with the next cell on the next sheet while it should restart from C9 and D9. and not C11 and D11.

Regards
Tommeck37

SkipVought (Programmer) · Answer 97 · 2015-02-05 16:43:59

I have corrected that small glitch.

I was referring to problem 1. If that is not solved, then we cannot proceed.

CODE

Do Until Sess.Screen.WaitForCursor(1, 1) Or Sess.Screen.GetString(23, 16, 14) = "NO INFORMATION"
            DoEvents
        Loop

Tommeck37 (Vendor) · Answer 98 · 2015-02-05 16:50:27

Quote:
I have corrected that small glitch.

What are you referring to?

For the problem no. 1 I myself have no idea what to do. I have no idea of function "WaitForCursor" What is the idea behind that?

Regards

SkipVought (Programmer) · Answer 99 · 2015-02-05 16:57:51

Sorry, the small glitch was in regard to the cell reference ib the sheet. But as I implied, that's minor with respect to screen navigation, which must be perfected before the contents are scrapped and transfered to any other system.

More importantly, it seem you have ignored lots that i have put forward, including what WaitForCursor does.

So you can have a go at it yourself, as you appear to discount my suggestions.

Tommeck37 (Vendor) · Answer 100 · 2015-02-05 18:45:32

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 (Programmer) · Answer 101 · 2015-02-05 19:02:53

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 (Programmer) · Answer 102 · 2015-02-06 03:39:13

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

CODE

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 (Vendor) · Answer 103 · 2015-02-06 13:54:52

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 (Programmer) · Answer 104 · 2015-02-06 14:58:18

Here's part of your post on Feb 15.

Notice the row numbers in the highlighted text. 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.

Quote:

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 (only line rw, 31, 17, but should also rw, 58, 17
....

Come Join Us!

Posting Guidelines

Jobs

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