Export 2 bits of data from Extra to Excel 2015

[Silver180]

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.

 

[SkipVought]

Hi,

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
[tt]
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?
[/tt]

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
[pre]
ScreenID
FieldID
FromRow
ThruRow
Col
Len
Type
[/pre]
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.

 

[Silver180]

Hi Skip,

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?

 

[Silver180]

Hmm, I guess I can do,

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.

 

[SkipVought]

Are you planning to do something like...
[tt]
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
[/tt]

Or....
[tt]
run the macro for ALL the data in column A as quickly as possible
[/tt]

???

 

[Silver180]

Pretty much, I'm correcting errors like Mr. Mrs. Ms. and Male or Female for a data migration to a different system. So its pretty random when it comes to what needs to be corrected. Though I have to go through a large list and multiple screens before I can even correct 1 persons profile.

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

 

[SkipVought]

Please post your code.

 

[remy988]

Silver180,

Sess1.Screen.SendKeys ("<End>")

maybe ?

Sess1.Screen.SendKeys ("<ErEOF>")

if you look at the key properties in the Keyboard Map Editor, it will list all the available strings

 

[remy988]

So i'm making a simple macro that will go through through all the screens. Let me correct the error.

are you correcting the screens manually?

Then run the macro again for the next person on the list.

you can/[blue]should[/blue] 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.

 

[Silver180]

Here is my code, I got my program to work. Instead of keypress "end". I just decided to space out enough to delete the extra id. Now....sure wish I could run this with a simple Ctrl + Z or something. It seems excel won't accept shortcut keys if i'm working in the extra window. I have to click on the excel sheet to use the shortcut key.

Sub Extract()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess1 = System.ActiveSession
'----------------------------------------------------

    With Worksheets("Data") '**Set sheet data will go too

    Rw = .Cells(1, "C").Value
    Pick = .Cells(Rw, "E").Value
    
    
   Sess1.Screen.Putstring Pick, 3, 41
   Sess1.Screen.WaitHostQuiet 100
   Sess1.Screen.MoveTo 3, 50
   Sess1.Screen.WaitHostQuiet 100
   Sess1.Screen.SendKeys ("           ")
   Sess1.Screen.WaitHostQuiet 100
   Sess1.Screen.SendKeys ("<enter>")
       
    Row = 1 '**Set excel row
       Pull = .Cells(Row, "B").Value
            Sess1.Screen.WaitHostQuiet 100
            Sess1.Screen.SendKeys ("<enter>")
            
            Sess1.Screen.WaitHostQuiet 100
            Sess1.Screen.SendKeys ("<enter>")
            
            Sess1.Screen.WaitHostQuiet 100
            Sess1.Screen.SendKeys ("<enter>")
            
            Quitcheck1 = (Sess1.Screen.Getstring(22, 13, 4))


            
            '**This will pull information from Extra, use your own coordinates
            .Cells(Row, "A").Value = Quitcheck1
                       
       
                       
            Sess1.Screen.WaitHostQuiet 100

          
        
        If .Cells(Row, "A").Value <> "" Then
                
        Sess1.Screen.Putstring Pull, 22, 10
        Sess1.Screen.WaitHostQuiet 100
        Sess1.Screen.SendKeys ("<enter>")
               
        End If
                

       

           .Cells(Rw, "K").Value = "X"
           .Cells(1, "C").Value = Rw + 1
       

          

   End With

End Sub

 

[SkipVought]

If you're in Extra, then you can't use Excel shortcuts!

 

[SkipVought]

Have your macro store the NEXT CELL REFERENCE in a cell. Use that cell's value (the NEXT CELL REFERENCE) as the start cell for the macro.