Export 2 bits of data from Extra to Excel 2015
Export 2 bits of data from Extra to Excel 2015
(OP)
Good Morning Everyone, starting off I been notified my departments trying to see if they could pull data from Extra into Excel to work with instead of printing 8 pages every time we need to review clients. This is more of a optional side project to see if its possible.
They been looking into extra trying to figure out how they can program it. But as of today, I been notified of the project and instead of using Extra I figured why not use Excel and program it to pull the data. With no programming knowledge, I have been unable to get very far.
We use 3 windows of Extra.
1. Summary of Clients info.
2. Ledgers or whatever
3. Blank or whatever we want.
I been looking at a few links and they are a bit outdated so I don't know if anything changed in the coding,
http://www.tek-tips.com/viewthread.cfm?qid=1438081
http://www.tek-tips.com/viewthread.cfm?qid=1560989
http://www.tek-tips.com/viewthread.cfm?qid=1574182
We want to pull Total Salaries per year out of (Extra Session 2 window) to edit in Excel that is currently opened and named Salary.xlsm on a different sheet named Data.
The beginning always starts with 14-15 for current year. <Enter> goes to next page, which would be 13-14; Unless too much information was on page 1, then there will be two pages both assigned for the same year.
We are trying to pull in salaries based on our date range. So (14-15 to 02-0) or (05-06 to 99-00). I was hoping to assign A1 to end date and B1 to beginning Date. Run macro, then the macro will check the date on Extra screen, if it is equal to what is in B1, Copy Date from top right corner (idk,idk,5) and Salary from bottom left corner (7,20,10) to Excel, A2 for Salary and B2 for Date; If not B1, <Enter> repeat.
After it copies the first page its suppose to, It needs to go to the next page and check Date that it is not Equal to B2, If not, Copy again into A3 and B3; If equal then <Enter>, Repeat. ;3rd page will be checking date B3 and ect
Then finished when it reaches the page with the same date as A1 are equal and copy Date and Salary and End or STOP macro.
Lastly, copy clients name and ID# from (Extra session 1 window) and End
Any advice or helpful links or straight code for setting Date ranges and cross checking it with Extra would be helpful. Pulling code is one thing, I have no idea how to set the ranges and get them to work like i described. If I can get it to check dates its as simple as throwing a code to copy coordinates on a screen and repeat. I also don't know if the links of code I been reviewing is still usable. Sadly, don't have the exact coordinates of the text that needs to be copied so I made due.
They been looking into extra trying to figure out how they can program it. But as of today, I been notified of the project and instead of using Extra I figured why not use Excel and program it to pull the data. With no programming knowledge, I have been unable to get very far.
We use 3 windows of Extra.
1. Summary of Clients info.
2. Ledgers or whatever
3. Blank or whatever we want.
I been looking at a few links and they are a bit outdated so I don't know if anything changed in the coding,
http://www.tek-tips.com/viewthread.cfm?qid=1438081
http://www.tek-tips.com/viewthread.cfm?qid=1560989
http://www.tek-tips.com/viewthread.cfm?qid=1574182
We want to pull Total Salaries per year out of (Extra Session 2 window) to edit in Excel that is currently opened and named Salary.xlsm on a different sheet named Data.
The beginning always starts with 14-15 for current year. <Enter> goes to next page, which would be 13-14; Unless too much information was on page 1, then there will be two pages both assigned for the same year.
We are trying to pull in salaries based on our date range. So (14-15 to 02-0) or (05-06 to 99-00). I was hoping to assign A1 to end date and B1 to beginning Date. Run macro, then the macro will check the date on Extra screen, if it is equal to what is in B1, Copy Date from top right corner (idk,idk,5) and Salary from bottom left corner (7,20,10) to Excel, A2 for Salary and B2 for Date; If not B1, <Enter> repeat.
After it copies the first page its suppose to, It needs to go to the next page and check Date that it is not Equal to B2, If not, Copy again into A3 and B3; If equal then <Enter>, Repeat. ;3rd page will be checking date B3 and ect
Then finished when it reaches the page with the same date as A1 are equal and copy Date and Salary and End or STOP macro.
Lastly, copy clients name and ID# from (Extra session 1 window) and End
Any advice or helpful links or straight code for setting Date ranges and cross checking it with Extra would be helpful. Pulling code is one thing, I have no idea how to set the ranges and get them to work like i described. If I can get it to check dates its as simple as throwing a code to copy coordinates on a screen and repeat. I also don't know if the links of code I been reviewing is still usable. Sadly, don't have the exact coordinates of the text that needs to be copied so I made due.
RE: Export 2 bits of data from Extra to Excel 2015
People in my company had procedures written in Excel that were designed to grab from a specific screen/transaction using data based on a table in Excel.
So it seems that you have something similar, that will grab data from several screens/transactions.
The first thing to concern yourself with is how the screen navigation will take place
When a screen is called,
A) what are that screen rest coordinates
B) where is the message area
C) what are the various messages (Error, more, done/successful) and what is the action to pursue for each message?
Then you need the coordinates, length and type of each data element you need to extract. Of course ALL the data you scrape is TEXT, but you'll need to convert the text that are dates to Date Serial Values and text that are numbers to Numeric Values.
After I had done a half a dozen new screens, I decided to create an Excel application with a table to store all my screen data. Looks something like this
By the time I retired, I had about 50 screen I could scrape using one program, and setting up a new screen just took a matter of minutes to record the data that described it.
Bottom line for you here, get the preliminary work done. Describe the screen/data exactly, no guessing. Post a sample of the first screen, and we can use that to talk to.
RE: Export 2 bits of data from Extra to Excel 2015
After I got back to work, I was able to use the links to figure out how to link to extra and pull data and enter data into excel and extra. I spend a few hours learning and playing around but I think I have the project that everyone was trying to figure out...figured.
I do have many basic questions though.
I'm currently trying to do another macro that will pull data from excel into extra. I want to run the macro, Have the first A1 on excel go into extra. (do some work) Run the macro again, having A2 go into extra.
I can do the,
row = 1
Pull the data into extra code.
row = row + 1
But once the macro reset its always going to be on A1.
If I loop it, It will never stop, if it does stop its going to reset the macro because it ends after the loop.
How can I keep the row in the right spot every time I want to run the macro Or can I use a "waitforkeypress" command if it exist, then it can loop after the keypress and keep adding to row?
RE: Export 2 bits of data from Extra to Excel 2015
Row = .Cells(1, "B").Value
.Cells(1, "B").Value = Row + 1
This will keep count every time I run the micro. Keeping the place I need to be at every time I run the macro without needing a pause in a loop.
RE: Export 2 bits of data from Extra to Excel 2015
Run the macro
Go do something else
Run the macro
Take a break
Run the macro
Have lunch
Run the macro
Go to a meeting
Run the macro
Go home
Or....
run the macro for ALL the data in column A as quickly as possible
???
RE: Export 2 bits of data from Extra to Excel 2015
So i'm making a simple macro that will go through through all the screens. Let me correct the error. Then run the macro again for the next person on the list. Though I probably will still take a few days...or a month. Right now manually entering ID numbers and entering a lot to pass message screens and putting X here, X there, Enter, Just to get to the right screen is pretty time consuming.
I still have 1 last problem.
Sess1.Screen.SendKeys ("<End>") Doesn't seem to work. It just puts the ID number down then puts <End> next to it. when i'm in 1 persons profile, The ID number becomes, ID#/ID#2. So I use the end button to erase anything after the first ID#.
RE: Export 2 bits of data from Extra to Excel 2015
RE: Export 2 bits of data from Extra to Excel 2015
CODE
maybe ?
CODE
if you look at the key properties in the Keyboard Map Editor, it will list all the available strings
RE: Export 2 bits of data from Extra to Excel 2015
you can/should use a "helper" column in Excel that will mark off where you left off, so when you run the macro again, it will know where to begin.
another method would be to click in the cell and have your script recognize the cell you are in and use that as a starting point.
if you want to go down the rows, pause, manually correct errors, continue to next row, here's a thought for you:
you can have your script look for a key word or character (such as a comma that can be placed on a blank space of your screen) to either STOP or CONTINUE.
RE: Export 2 bits of data from Extra to Excel 2015
CODE --> VBA
RE: Export 2 bits of data from Extra to Excel 2015
RE: Export 2 bits of data from Extra to Excel 2015