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

[ptw78]

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?

 

[SkipVought]

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,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

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

 

[SkipVought]

Please copy the entire screen and post this example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

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

 

[vzachin]

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

 

[SkipVought]

How about listing the 10 strings.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

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

 

[SkipVought]

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,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

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.

 

[SkipVought]

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,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[denniscay]

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.

 

[SkipVought]

Here's an example of a scrubbed screen shot.

Use [ignore][tt][/ignore]...[ignore][/tt][/ignore] TGML tags. If you don't know what they are, SEARCH on TGML in this page for the LINK.
[tt][highlight white]
----+---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[/highlight][/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

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

 

[SkipVought]

did you NOT find the TGML link????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

let me try it again

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

[/tt]

 

[SkipVought]

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,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

assuming that it is...


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

Check for date range...

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,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ptw78]

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

[tt]080408 IPD 999 LETTER1 REG MAIL - 8/04/08 [/tt]

 

[SkipVought]

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

Is there another question?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}