×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Extra Help, pull String and put into excel....

Extra Help, pull String and put into excel....

Extra Help, pull String and put into excel....

(OP)
I'm wanting to pull a string of info from extra based on what I put into a macro. So for instance say I need to find a string that says  "ABCDEFG12345 10/1/09". Is it possible to have the macro look for that string in the range of 10/20 thru 20/20 in extra?

RE: Extra Help, pull String and put into excel....



Hi,

A qualified yes.

1. is there a particular location, a column or a row, on any screen that this string might occur?

2. your "date" in your example, is 10/1/09. Then you state, "the range of 10/20 thru 20/20"  So what is the format of your "date" in extra?

3.  are you looking for the string that you posted and how does that relate to a date range?  it's not clear.

Please answer ALL questions.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
1. no there isn't a particular location unfortunately, it can be random. But is generally going to be between the range of 10/20 down to 20/20 on the screen in extra.

2. The date actually shows up twice. Once before the location of 10/20 it will show up something like 10122009 and then at the end of the row of 10/20 it would be 10/14/09

3. I'm not looking for the string I posted, that was just an example. I would actually be looking for a few different strings, probably about 10 of them. So if the macro could look for all 10 that would be great, if not then I can change what it is looking for each time I guess, but that might defeat the purpose of what I'm trying to do.

Also I would want to put this info in an excel file. So for example I have a list of account numbers in excel. The macro pulls the first account number, puts into extra. Then looks for the specified strings, if it finds it puts it back into excel. Rinse repeat for the next account number

Sorry wasn't more clear on that earlier. Thanks

RE: Extra Help, pull String and put into excel....



Please copy the entire screen and post this example.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
I can't post a screen of extra due to it have sensitive info. I guess what I can start with is if you could tell me how I would search for a specific string in extra if that string or strings where in the macro???  

RE: Extra Help, pull String and put into excel....

questions:
(1)the 10 strings you mention, will the strings be the same each time?
(2)what are the lengths of each string?

would this help you?
http://www.tek-tips.com/viewthread.cfm?qid=1570087&page=1

this searches the screen area for a string and moves the cursor to the beginning of the found string

RE: Extra Help, pull String and put into excel....




How about listing the 10 strings.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
Here is an example of one of the strings. I can list all 10 but they are pretty much the same thing just the letter part will be a different name

101909 ASD   999  REG LETTER MAIL - 10/19/09

RE: Extra Help, pull String and put into excel....



BTW, 10/19/09 is a STRING and not a DATE.

I'm an Excel guy, so I'd be parsing the string (101909 ASD   999  REG LETTER MAIL - 10/19/09) in Excel, where the 10/19/09 would convert to a DATE in the Text to Columns process.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
101909 ASD   999  REG LETTER MAIL - 10/19/09
This is the hole string in Extra. I guess what I'm asking is how would I search just for a string like this and put it into excel. Except there are 10 different strings.

RE: Extra Help, pull String and put into excel....


I asked you earlier to post your screen, which you refuse to do, due to sensitivity of data.

Well CHANGE THE DATA.

Post the screen and then explain what you are looking for, remembering that we know NOTHING about your process or your data.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

Suggestion. If the screen has sensitive information, you can edit it before posting.
As they say, a picture is worth 1000 words.
Copy the screen and paste it into Notepad.
Edit out the sensitive info using xxx for text, 123 for numbers. Leave dates as is if you can.
Then post.

RE: Extra Help, pull String and put into excel....

Here's an example of a scrubbed screen shot.  

Use [tt]...[/tt] TGML tags.  If you don't know what they are, SEARCH on TGML in this page for the LINK.

----+---10----+---20----+---30----+---40----+---50----+---60----+---70----+---80
  AP412431                 APS TRAVELERS BY PART NUMBER     11/13/09 07:05:38   
                                                                     PAGE LAST  
  PART NUMBER   xxx-xxx-xxx-xxx    PLATE SET                                    
                                                                         Q      
  TRAVELER     ACT        PLND PLND  MRP TYPE ----CURRENT---  LAST HOLD  M NET  
   NUMBER CNF  QTY  PC LC INWK COMP NEED NEED OPER C/C LOCTN  MOVE CODES S GRP  
  xxxxxxx        5  02  C 0308 0318 0316  W   00B  WFT M-FILE  781     H   CCCC
  xxxxxxx        6  02  C 0447 0457 0455  W   00B  WFT M-FILE  781     H   CCCC
  xxxxxxx        3  02    0600 0610 0610  W   00B  WFT M-FILE  781     H   MMMM
  xxxxxxx  C     5  02    0531 0545 0543  W   00B  WFT FILE    842     H   CCCC
  xxxxxxx        2  02    0840 0852 0852  W   00B  WFT FILE    842     H   MMMM
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
     TOTAL QTY IN WORK      21                                                  
 LAST PAGE                                                                      
 PFKEY 4-SPF45555 6-SF60 9-SF90 15-SF15 12-SFCS MENU         NEXT PF24-         
----+---10----+---20----+---30----+---40----+---50----+---60----+---70----+---80

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
Sorry for the late response, but here is a sample, using 'X' for letters and '123...' for numbers, the dates of no importance I used 01/01/10. This is a basic note screen where notes are kept. Most of it is under 'XXX' due to certain conversations. The last note 'LETTER1  REG  MAIL' is something that I'm looking for, there are about 10 different kinds of letters and the only thing that would change would be the letter name, so instead of 'LETTER1  REG  MAIL' it may say something like 'WELCOME LETTER'  and then the date and so on for diff names. Thanks


            NOTE SCREEN
                                                                                
 LOAN #: 123456789  0  CUSTOMER: John Doe            TYP: 008      
 DEPT ID: ALL (ALL/SEL/ACRONYM)  BAL: N  TYPE: B (F=FINANCIAL,D=DATAMSGS,B=BOTH)
 START DATE: 01/01/10   STOP DATE: 00/00/00   PRINT: N (Y/N)   PRINTER:         
 INV: 05624/00000             BLK: 071/000          UNAP:        0.00  CD:      
 PDTO: 01/01/10 PBAL:     123456.78 EBAL:      -6163.54 WARN: 0 LOCK: 0 STOP: 00
 I-YTD:        0.00                                 TIED:                       
  DATE  TRAN PDTO    TRAN AMT        PRIN       INT        ESC      L/C   OT-AMT
 102409 SYS    90  10:49                                                        
 102009 SYS   999  xxxxxxxxxxxxxxxxxxxxxxxxxxx               
 102009 SYS    90  11:01 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx           
 102009 SYS    90  11:01 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx           
 102009 SYS    90  11:01                                                        
 101909 SYS 99999  01:10 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx              
 100709 CBR 99999  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx                     
 100109 FEA  0501      -12.34 xxxxxxxxxxxxxxxxxxxxxxxx                          
 093009 DEF    90  21:30 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx           
 093009 DEF    90  21:30 xxxxxxxxxxxxxxxxxxx                   
 093009 SYS    90  21:30 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 080408 IPD   999  LETTER1  REG  MAIL -  8/04/08      
           
 

RE: Extra Help, pull String and put into excel....



did you NOT find the TGML link????

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
let me try it again


            NOTE SCREEN
                                                                                
 LOAN #: 123456789  0  CUSTOMER: John Doe            TYP: 008      
 DEPT ID: ALL (ALL/SEL/ACRONYM)  BAL: N  TYPE: B (F=FINANCIAL,D=DATAMSGS,B=BOTH)
 START DATE: 01/01/10   STOP DATE: 00/00/00   PRINT: N (Y/N)   PRINTER:         
 INV: 05624/00000             BLK: 071/000          UNAP:        0.00  CD:      
 PDTO: 01/01/10 PBAL:     123456.78 EBAL:      -6163.54 WARN: 0 LOCK: 0 STOP: 00
 I-YTD:        0.00                                 TIED:                       
  DATE  TRAN PDTO    TRAN AMT        PRIN       INT        ESC      L/C   OT-AMT
 102409 SYS    90  10:49                                                        
 102009 SYS   999  xxxxxxxxxxxxxxxxxxxxxxxxxxx               
 102009 SYS    90  11:01 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx           
 102009 SYS    90  11:01 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx           
 102009 SYS    90  11:01                                                        
 101909 SYS 99999  01:10 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx              
 100709 CBR 99999  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx                     
 100109 FEA  0501      -12.34 xxxxxxxxxxxxxxxxxxxxxxxx                          
 093009 DEF    90  21:30 xxxxxxxxxxxxxxxxxxxxxxxxxxxxx           
 093009 DEF    90  21:30 xxxxxxxxxxxxxxxxxxx                   
 093009 SYS    90  21:30 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 080408 IPD   999  LETTER1  REG  MAIL -  8/04/08      
           

RE: Extra Help, pull String and put into excel....



So the next question is, columns 2-7 seem to be a 'date' and that 'date' seems to correspond to the 'date' in  columns 20-80.

Is that always true?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....



assuming that it is...


Search for 'LETTER' and return the screen row number.

Check for date range...

CODE

dim TestDate as Date, sDate as String

sDate = Scr.GetString(SrcnRowNbr, 2, 6)
TestDate = DateSerial("20" & Mid(sDate,5,1),Mid(sDate,1,2),Mid(sDate(,3,2))

' now see it the TestDate is between your limits.
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
I'm not sure about those locations but if you're asking if
the date on the left here is always the same as the one at the end of the note, then yes. Also I forgot to mention this is just one of many pages of notes, you can PF8 to go forward and PF7 to go back

080408 IPD   999  LETTER1  REG  MAIL -  8/04/08

RE: Extra Help, pull String and put into excel....



Your original question related to finding a string and determining if the 'date' was between two limits.

Is there another question?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
I think I didn't elaborate enough on my original questions. What I'm wanting to do is find specific letters w/in the different note pages. So for instance I'd be looking to pull
LETTER1  REG  MAIL -  8/04/08  
and put it into excel, and do this for 10 diff letters and any number of accts. There would be an excel file where the accts numbers would be pulled from.

RE: Extra Help, pull String and put into excel....



Quote:

for 10 diff letters and any number of accts.
So you search for LETTER or something more specific?

And what about acct??? Where is that?

Please focus like a laser.  Exactly what are the requirements?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....

(OP)
ok, so I have an excel sheet with a list of accts, i can be any where from a couple hundred to a couple thousand. I want to pull the acct number from that excel list, search the notes which can be any number of pages long for specific letters and the date of the letter. An example of one letter is above. Now  I'd have 10 more like that, not all accts will have all letters. I want to put that letter name and date onto an excel sheet w/the acct number.

RE: Extra Help, pull String and put into excel....




So for each screen that you access, you need to loop thru your Excel list to search for a string.  

When found, write to an Excel sheet.

that's your basic control structure.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Extra Help, pull String and put into excel....


do i understand this correctly?
1. excel sheet has many acct entries
2. find one acct in extra
4. call up notes screen
5. look for particular letter(s) (10 different) in notes
6. notes can be multiple pages, PF8 to go forward
7. if found, then place letter and acct back into excel

what does your code look like so far?
can you give us 10 examples of the string (make them up)?
will the 10 strings always be the same?


 

RE: Extra Help, pull String and put into excel....

how about something like this.

CODE

Sub Main
        Dim Sys As Object, Sess As Object, MyArea As Object
        Set Sys = CreateObject("EXTRA.System")
        Set Sess = Sys.ActiveSession
        Set Sess = Sess.Screen

        dim s(1 to 4) as String
        s(1) = "AAA"
        s(2) = "BBB"
        s(3) = "CCC"
        s(4) = "LETTER1"
        
'define For Excel data here
'find the first acct
    
        Do
    
        for x = 1 to 4
    
        Sess.MoveTo 1, 1
        
        mydat = s(x)
        
        Set MyArea = sess.Search(mydat,10)
        sess.MoveTo MyArea.Bottom, MyArea.Left
        MyRw = sess.Row
        MyCol = sess.Col
        if MyRw <> 1 or MyCol <> 1 then
        msgbox mydat
        
        FoundLtr = trim(sess.getstring (myRw, MyCol,60))
        msgbox FoundLtr
        'place FoundLtr into Excel here, then exit Do
        exit Do
        end if
    
        next x
        
        sess.sendkeys ("<pf8>")
    
        Loop 'Until No More Notes
        
        
'need next statement here to go to next acct
        
    
End Sub

RE: Extra Help, pull String and put into excel....

(OP)
vzachin  to answer your first post, you pretty much have it right on, i'm wanting to put the letter and date of the letter back into excel w/acct num. It can be a new excel file or the existing one where i get the acct nums from.

RE: Extra Help, pull String and put into excel....

ptw78,

try what i gave you and see if it's close to what you're looking for...
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close