Re: I'm desperate!! Search, if found, insert, else search for nex

Thank you Graham.

My reference document (only used for finding the text to replace the codes
in the data source) is the one that has two columns. It finds the code that
needs to be replaced in the data source, goes to column one in the reference
document, selects the corresponding text in column two, goes BACK to the data
source and replaces the matching code (in either column 14 or 15) with the
appropriate text.

There is really no difference in how the codes in column 14 or 15 in the
data source are replaced. The difference is how they are used in the mail
merge. All codes in column 14 begin with "P" and are followed by 3 numbers
and a comma, all codes in column 15 begin with "C" and are also followed by 3
numbers and a comma. They're placed in separate columns based on the type of
requirement they are. I used "P" to indicate "prerequisite" for the end
users and "C" to indicate "comment" for the end users. The verbiage from the
"P" code column 14 goes into one area of the letter, the verbiage from the
"C" code column 15 goes into another area of the letter. For searching and
replacing with text, the only difference is the location in the data source.

All possible codes are included in the reference document in the first
column, with their corresponding text in the second column. If it's better,
I can easily create two reference documents - one for "P" codes and one for
"C" codes.

Does that help clarify?

"Graham Mayor" wrote:

The original macro looks for the codes from the first column of the
reference document wherever they exists and puts the corresponding text from
the reference document into the cell to the right of the cell containing the
code (in place of whatever is already there). Your revised comments suggest
that there may be codes in that cell also, which presumably you wish to
keep? So where do you want the corresponding text to be placed with
reference to the code that may be in column 14 or 15? Do you want it to be
placed *in place of the code* or *in some other cell on the same row*, or
somewhere else?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>



bjr wrote:
Thank you, Graham. You're absolutely right about what I'm trying to
do. The reference document (PandCCODE.FOR MACRO REFERENCE.docx) does
contain two columns, the first containing the code, the second
containing the corresponding text. The data source table, however,
contains 16 columns. The codes are located in the 14th and 15th
columns (there are actually two "types" of codes and they are
formatted C###, and P###,). The "P" codes (those formatted as
'P###,') are located in the 14th column, the "C" codes (those
formatted as 'C###') are located in the 15th column. It looks like
this:

P-Code Cell, located in row 2, column 14:
"P006,P009,P012,P015,P024,P033,P045,P054,P057, "

C-Code Cell, located in row 2, column 15: "C025,C007,C046, "

If I understand the macro you sent, I can change the reference to the
data source to reflect the 14th or 15th column, depending on which
code is being changed...is that right.

"Graham Mayor" wrote:

If your changes source (PandCCODE.FOR MACRO REFERENCE.docx) is
configured as a two column table with the references to find in the
first column and the associated texts in the second column and the
(open in Word) document that you want to change is also a two column
table with the codes to find in the first column then the following
(based on another of Doug's macros ;)) should work. You will need to
add the path to PandCCODE.FOR MACRO REFERENCE.docx

It looks for the reference in the table and inserts the text
associated with the reference in the cell to the right of the cell
that contains the found reference which I believe is what you are
trying to do.

Dim ChangeDoc As Document
Dim RefDoc As Document
Dim cTable As Table
Dim oldPart As Range, newPart As Range
Dim i As Long
Dim sFname As String
Dim oRng As Range

sFname = "d:\My Documents\Word Documents\PandCCODE.FOR MACRO
REFERENCE.docx" Set RefDoc = ActiveDocument
Set ChangeDoc = Documents.Open(sFname)
Set cTable = ChangeDoc.Tables(1)
RefDoc.Activate

For i = 1 To cTable.Rows.Count
Selection.HomeKey wdStory
Set oldPart = cTable.Cell(i, 1).Range
oldPart.End = oldPart.End - 1
Set newPart = cTable.Cell(i, 2).Range
newPart.End = newPart.End - 1
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
While .Execute(findText:=oldPart, _
MatchWholeWord:=True)
Set oRng = Selection.Range
With oRng
.MoveStart wdCell, 1
.MoveEnd wdCell, 1
.Text = newPart
.Collapse wdCollapseEnd
End With
Wend
End With
Next i
ChangeDoc.Close wdDoNotSaveChanges


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>



bjr wrote:
Thank you, Doug.

To explain a bit better...I'm modifying an existing macro that I
originally wrote in Word 2003. I've tweaked it many times over the
years as our correspondence needs have changed and continue to do so
in Word 2007. I work at Spokane Community College and the macro is
used to "seamlessly" prepare information downloaded from our Student
Management Database into mail merge letters. The end users
download, run Excel macro, run Word macro, merge into letter...and
voila, they're done!!!

The codes I need to "change the values for" are used in SMS (our
student database) in a user defined field to reflect what a specific
student needs to fulfill their requirements to enter a
technical/vocational program. There are around 100 codes, each
representing a different string of text (i.e. P006 from the database
may mean "Biology 101" in the letter, or P012 may represent an
entire paragraph). I was originally using search and replace,
which worked well until the staff needed to use much longer text
strings.

I tried using Building Blocks, which worked great, except that I'm
not able to save them in the Normal.dotx template, due to user
differences in their own Normal template. When I tried to save the
Building Blocks to a different template, they worked fine while
building the macro, but when it was run, it failed. I still like
that idea, but can't figure out how to make it work.

Here's what I'm now trying...I have the mail merge data source
(RegAppt.docx) open. Each row contains information from a different
student record. One of the columns has codes separated by a comma
(i.e. P006,P035,C002, would represent a student that has three
different requirements). Each student record (row) has a different
set of codes, depending upon that student's specific requirements.

I set up a separate reference document in Word that contains a table
with two columns, the first containing the 4 character code (1
letter followed by 3 digits), the second column containing the text
associated with each code. It's a mini-database table.

The macro needs to search for each of the 100 possible codes in the
data source.

If it finds the code in the data source, it goes to the reference
document, finds the code, moves to the cell next to the code that
contains the text associated with the code, and copies that text
back to the data source, overwriting (replacing) the code that was
originally downloaded from the Student Management System.

If it does not find the code in the data source, it should move on
and search for the next code without looking at the reference
document.

This process should continue until each possible code has been found
and replaced, or not found and "skipped".

I didn't understand exactly what you meant about looping (although I
do know what looping is), but I don't think that I provided enough
information.
I sincerely appreciate your help.

bjr

"Doug Robbins - Word MVP" wrote:

I cannot follow what your code is doing, but to only do something
while the text that you are looking for is found, use a Do While
Loop construction

With Selection.Find
Do While .Execute(FindText:="", Forward:=True, _
MatchWildcards:=False, Wrap:=wdFindStop, MatchCase:=False) =
True Selection.Range.HighlightColorIndex = wdYellow
Selection.Collapse wdCollapseEnd
Loop


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of
my services on a paid consulting basis.

Doug Robbins - Word MVP

"bjr" <bjr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CCBACC8D-A37E-489E-BAED-DF5367EF1F15@xxxxxxxxxxxxxxxx
I'm attempting to build into an existing Word macro to search for
a specific
code within a table that's being used as a mail merge data source.
If the code is found, activate a separate document to copy
specific information related to that code back to the
table...that part's working.

I don't know how tell it NOT to activate the separate document if
the code isn't found. At that point, I need it to continue to the
next code. The macro looks at for about 100 different codes that
MAY be in the table. Here's a piece of my current macro
("regappt.txt" is the data source that will be saved as a .docx at
the end of the macro, "PandCCode.for macro reference.docx" is the
document that contains all of the codes with their associated
text):

With Selection.Find
.Text = "P006,"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
Documents.Open FileName:="PandCCode.for macro reference.docx"
Selection.Find.ClearFormatting
With Selection.Find
.Text = "P006,"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
Selection.Copy
Windows("REGAppt.txt").Activate
Selection.PasteAndFormat (wdPasteDefault)
Selection.Find.ClearFormatting
With Selection.Find
.Text = "P009,"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
Windows("PandCCODE.FOR MACRO REFERENCE.docx").Activate
Selection.Find.ClearFormatting
With Selection.Find
.Text = "P009,"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
Selection.Copy
Windows("REGAppt.txt").Activate
Selection.PasteAndFormat (wdPasteDefault)
Selection.Find.ClearFormatting



.

Relevant Pages

  • Re: Im desperate!! Search, if found, insert, else search for nex
    ... Word MVP web site http://word.mvps.org ... The data source table, however, ... If I understand the macro you sent, I can change the reference to the ...
    (microsoft.public.word.vba.general)
  • Re: Im desperate!! Search, if found, insert, else search for nex
    ... reference document wherever they exists and puts the corresponding text from ... Word MVP web site http://word.mvps.org ... If I understand the macro you sent, I can change the reference to the ... Dim ChangeDoc As Document ...
    (microsoft.public.word.vba.general)
  • Re: Im desperate!! Search, if found, insert, else search for nex
    ... The original macro looks for the codes from the first column of the ... reference document wherever they exists and puts the corresponding text from ... Word MVP web site http://word.mvps.org ... The data source table, however, ...
    (microsoft.public.word.vba.general)
  • Re: Im desperate!! Search, if found, insert, else search for nex
    ... You will need to add the path to PandCCODE.FOR MACRO ... the reference in the cell to the right of the cell that contains the found ... used to "seamlessly" prepare information downloaded from our Student ... Here's what I'm now trying...I have the mail merge data source ...
    (microsoft.public.word.vba.general)
  • Re: Im desperate!! Search, if found, insert, else search for nex
    ... The data source table, however, contains 16 columns. ... If I understand the macro you sent, I can change the reference to the data ... used to "seamlessly" prepare information downloaded from our Student ...
    (microsoft.public.word.vba.general)
Quantcast