Excited you're taking a look Macropod - I've seen you do some pretty awesome things in the past! ThxInAdvc!
(1)So why wouldn't you want the code to automate the file opening & closing as well?
.A: You totally could (and it wouldn't be a bad thing) - Was just trying to keep it less complex since time is of the essence - less code to write and less to have to change file names for each time (unless of course, there were some sort of prompt that prompted the User to navigate to the file each time and open it wherever it may be) - this would be fabulous!.. but not at the top of criticalities..
(2a) Are these words to be found anywhere in the document, or in a particular location (e.g. the first table, a bookmarked range or content control, a specified Section)?
.A: Yes (anywhere), the word docs we receive from the programmers have the content spread throughout -- it is not sitting in tables and sits in a staggered format such as this:
---------------------------------------
Code:
IF ACJ-TRANS-LR-CODE = '05'
IF (WS-DOC-ID = 'F7A') AND
(WS-RECORD-CD = '4')
MOVE SPACES TO ACN-KEY-SUF
ELSE
IF WS-DOC-NR1 = 'M' AND
WS-RECORD-CD = '4' AND
WS-BGCD = '8'
IF (WS-DOC-ID = 'D7A' AND WS-TT = 'N') OR
(WS-DOC-ID = 'D6R' AND WS-TT = 'T') OR
((WS-DOC-ID = 'D6A') AND
(WS-TT = 'N' OR SPACES))
MOVE 'M' TO ACN-KEY-SUF
ELSE
MOVE WS-TT TO ACN-KEY-SUF
ELSE
MOVE WS-TT TO ACN-KEY-SUF
(2b) Can only one of these words appear in a given document, or can they all appear in the same document?
.A: All could potentially appear in a single Word doc
(2c) Can the words occur more than once?
.A: Yes, see example above, e.g., "IF" "AND" "OR" appear multiple times (and so could the others)...
(2d) If so, then what do you want to do about that?
.A: just list it on a new row of the spreadsheet
(for example WS-TT appears more than once (3xs below), so it gets listed each time it appears, along w/ whatever Code is listed (but here's where it gets tricky).......,
Code:
IF (WS-DOC-ID = 'D7A' AND WS-TT = 'N') OR
(WS-DOC-ID = 'D6R' AND WS-TT = 'T') OR
((WS-DOC-ID = 'D6A') AND
(WS-TT = 'N' OR SPACES))
MOVE 'M' TO ACN-KEY-SUF <<< everything abv relates to this Output name
The 3rd time WS-TT is listed, there's a code in single quotes 'N', (and) there's an "OR" present
which means we'll need to list WS-TT a 4th time in order to record the other code "SPACES" (or)
if there's an easy way to list it on a single line like this: (it would be just as good) - whichever is easier!
Column C..................Col D..........Col E............Col F...........
Field Name................Condition....Rule Name....Output.........
ACJ-TRANS-LR-CODE..05..............AA..............ACN-KEY-SUF
WS-DOC-ID...............F7A............AA..............ACN-KEY-SUF
WS-RECORD-CD........4................AA..............ACN-KEY-SUF
WS-DOC-NR1............M...............AA..............ACN-KEY-SUF
WS-RECORD-CD........4................AA..............ACN-KEY-SUF
WS-BGCD.................8................AA..............ACN-KEY-SUF
WS-DOC-ID..............D7A............AA..............ACN-KEY-SUF
WS-TT.....................N................AA..............ACN-KEY-SUF
WS-DOC-ID..............D6R............AA..............ACN-KEY-SUF
WS-TT.....................T.................AA..............ACN-KEY-SUF
WS-DOC-ID..............D6A.............AA.............ACN-KEY-SUF
WS-TT.....................N.................AA.............ACN-KEY-SUF
WS-TT.....................SPACES........AA.............ACN-KEY-SUF
or can do it like this:
WS-TT.....................N,SPACES.....AA.............ACN-KEY-SUF
----------------------------------------------------------------------------------
the 'Rule Name' is simply the Word doc file name repeated all the way down,
and the 'Output' is re-used as it is relational (meaning all the line items listed in that little chunk all tie to/relate to the 'Output' name listed at the end of that chunk signified by preceding the word "TO":
Code:
IF (WS-DOC-ID = 'D7A' AND WS-TT = 'N') OR
(WS-DOC-ID = 'D6R' AND WS-TT = 'T') OR
((WS-DOC-ID = 'D6A') AND
(WS-TT = 'N' OR SPACES))
MOVE 'M' TO ACN-KEY-SUF <<< everything abv relates to this Output name
(2e) How is the macro to determine where the required content ends (so far you've only described (apparently) where it starts)?
.A: I saw that it would be labor intensive to try to build in all the starts and stops into the code (considering the staggered format this content arrives to us in AND the inconsistency of CODES being/not being all housed between single quote marks)
- so with time being short, I mentioned
just pick up the rest of that line
with the intention to parse off the extra garbage later - either manually or using some RIGHT,MID,LEFT functions.. even that, given the format it arrives in - will be difficult - so this may have to be done manually. (hate that word) =-)
(3) That's all very well, but a macro needs to know what to expect. Other that the quotes (single?/double?) that might appear after your keywords, what else always occurs and what else might occur be between them and the '=' (spaces, tabs, letters, numbers, etc.)?
.A: Well, that's where it gets really crappy because those rules are exactly what I looked for -- taking a handful of the huge stack of Word docs to try to identify patterns and inconsistencies..
It's safe to say these are the ALWAYS DO rules:
=1= After the word "TO", always pick up whatever content is sitting to the immediate RIGHT of "TO" (up until a space occurs/stop at space or end of line whatever comes first) to capture the Output Name
=2= Whenever an equals sign is encountered, always pick up content LEFT of it (up until a space or parens occurs/stop) to capture the Field Name
Code:
(WS-DOC-ID = 'D6R' AND WS-TT = 'T') OR
=3= Whenever the word "OR" appears followed by single quotes (like the below example), record both codes either in a single cell or separately on diff lines as demonstrated below:
Code:
IF HOLD-REIMB = ‘A’ OR ‘Y’<<<<<<<<<<<<< A & Y can be pasted into Col C either together or individually
IF PUBLIC-SALES-CODE AND PUBLIC-TIV-12
MOVE ‘12’ TO ACH-KEY-IND-VAL
Column C..................Col D..........Col E............Col F...........
Field Name................Condition....Rule Name....Output.........
HOLD-REIMB.............A.................AA..............ACN-KEY-SUF
HOLD-REIMB.............Y.................AA.............ACN-KEY-SUF
..or can do it like this (whichever is easier):
HOLD-REIMB.............A,Y..............AA.............ACN-KEY-SUF
----------------------------------------------------------------------------------
=4= Whenever "PUBLIC-SALES-CODE" (or) "PUBLIC-TIV-12" (or) NOT-PUBLIC-SALES-CODE are encountered like the below,
Code:
IF HOLD-REIMB = ‘A’ OR ‘Y’<<<<<<<<<<<<< A & Y can be pasted into Col C either together or individually
IF PUBLIC-SALES-CODE AND PUBLIC-TIV-12
MOVE ‘12’ TO ACH-KEY-IND-VAL
list them as individual line items as follows:
---------------------------------
Column C..................Col D..........Col E............Col F...........
Field Name................Condition....Rule Name....Output.........
PUBLIC-SALES-CODE..........YES.......AA.............ACN-KEY-SUF
PUBLIC-TIV-12...................YES.......AA.............ACN-KEY-SUF
NOT PUBLIC-SALES-CODE....NO........AA.............ACN-KEY-SUF<<< "NOT" INDICATES "NO" Condition
(4) Clear as mud. Now you're introducing [TO] but you give no explanation of how this relates to your other rules.
.A: Sorry, will try to be more clear: IF the word "TO" is encountered, copy the content that sits immediately to the RIGHT of "TO" and paste it into Col F Output field.
(4a) How much content gets copied?
.A: See below: (copy until the first space or end of line occurs after the content) "ACH-KEY-IND-VAL" then stop.
Code:
IF HOLD-REIMB = ‘A’ OR ‘Y’<<<<<<<<<<<<< A & Y can be pasted into Col C either together or individually
IF PUBLIC-SALES-CODE AND PUBLIC-TIV-12
MOVE ‘12’ TO ACH-KEY-IND-VAL
(5) It's not clear whether your LOOKUP/INDEX reference is supposed to be for Excel's benefit or for Word's; Word has no such functions.
.A: disregard that terminology, the goal is to use EXCEL to extract content from WORD docs; achieved in the fastest, most efficient way possible... if it's a little messy with picking up a little extra garb at the end of a line, so-be-it- as long as I can collect all the content needed from the Word doc and pass it on to programmers in a table such as is shown below. Hope that makes sense and THANKS AGAIN!
Column C.........................Col D..........Col E..............Col F...........
Field Name...................Condition......Rule Name........Output.........
ACJ-TRANS-LR-CODE...........05..............AA..............ACN-KEY-SUF
WS-DOC-ID........................F7A............AA..............ACN-KEY-SUF
WS-RECORD-CD.................4................AA..............ACN-KEY-SUF
WS-DOC-NR1.....................M...............AA..............ACN-KEY-SUF
WS-RECORD-CD.................4................AA..............ACN-KEY-SUF
WS-BGCD..........................8................AA..............ACN-KEY-SUF
WS-DOC-ID.......................D7A............AA..............ACN-KEY-SUF
WS-TT..............................N................AA..............ACN-KEY-SUF
WS-DOC-ID.......................D6R............AA..............ACN-KEY-SUF
WS-TT..............................T.................AA..............ACN-KEY-SUF
WS-DOC-ID.......................D6A.............AA.............ACN-KEY-SUF
WS-TT...............................N.................AA.............ACN-KEY-SUF
PUBLIC-SALES-CODE..........YES..............AA.............ACN-KEY-SUF
PUBLIC-TIV-12...................YES..............AA.............ACN-KEY-SUF
NOT PUBLIC-SALES-CODE....NO...............AA.............ACN-KEY-SUF<<< "NOT" INDICATES "NO" Condition
----------------------------------------------------------------------------------
eek! sorry all the ANSWERS to your questions are forcibly in italics because it's included within the quote area.. sorry!
(also note, there's an orange cross-post link to an actual example xlsm file if that helps - in the orig post above)
