Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
(OP)
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
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Your code with modifications. I commented throughout the code.
CODE
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!
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
You original code was incorrect!
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Further code I will be able to test only on Monday. Will get you posted shortly
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
What code produced this result? As I stated previously, your code was incorrect!
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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?
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
sDate = Format(Date-1,"ddmmyyyy")
Does!
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
here is the code
CODE --> vba
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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'!)
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
Regards
Tomek
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
"...does not step one sheet forward"
Did you actually check the other sheets?
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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!
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each 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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
CODE --> vba
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Your For...next IS advancing thru ALL the sheets in your workbook!
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
I've tried with
CODE --> vba
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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?
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Exactly what is sheet Day1 used for OTHER THAN THIS DATE?
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
CODE
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each 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
I think I need to have the Do....Loop statement. I could not recognize an error without it.
Kind Regards,
Tommeck37
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
CODE --> vba
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Also, you need to remove COMMAS in the string of numeric characters before assigning to a cell in Excel.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
I have managed to resolve it with replace if and instr
here is the code:
CODE --> vba
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
Here is the code
CODE --> vba
Regards
Tommeck37
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Let's start from the top.
CODE
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...
What is THIS? Please explain in detail.
I need the answers to these two questions before proceeding.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
I had to ignore
CODE --> vba
CODE --> vba
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.
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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?
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
How would, "from memory in paint" be easier than actual characters???
What is good is where it is easy to count rows and columns.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
Is it possible to sent a initial cell to count from empty cells?
I tried with
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
CODE --> vbA
but it does not work
Kind Regards
Tommeck37
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
CODE
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
I attached two files below as I am not able to do the tgml
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
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
I hope I was clear enough. If not, let me know
Regards
Tommeck37
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Now lets use your summary screen shot to address the next issue of loading the data into columns B, C & D.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
I will put here whatever you want tomorrow
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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?
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
I will never skip a row in a table.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
Yes the message is on the same, initial screen right below all info.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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?
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Rest coordinates are below the screen, in the right corner ( this part of screen is not copiable)
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
This is like pulling teeth!!!
BTW the coordinates in the lower RH corner are the CURRENT cursor coordinates, not necessarily the rest coordinates.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Apologies for me being so green in here
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
CODE
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
How can that be? I have no precise wait ANYWHERE in my code!
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Odd it is
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Please explain so that it is clear.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
CODE --> vba
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
...in which case the MESSAGE is posted in 23,16, correct?
So what's happeneng there? You have not said anything about the message.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
then there is message below "NO INFORMATION FOUND ***" but this you already know
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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?
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
I have corrected that small glitch.
I was referring to problem 1. If that is not solved, then we cannot proceed.
CODE
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
We wrote a lot but really litlle was done. Nevermind. Thanks for trying anyway
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
Here is my latest code, for what its worth, that addresses several of your 5 issues...
CODE
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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
RE: Copy data from Attachmate to Excel in a Loop to a single cell in each sheet
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.