I'm desperate!! Search, if found, insert, else search for next

[bjr]

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

 

[Doug Robbins - Word MVP]

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]

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

 

[Graham Mayor]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[bjr]

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]

I will be out most of the day, but will have a look at this when I get back.

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[bjr]

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]

It really doesn't make any difference where in the data document the P### or
C### codes are, the macro should find them (unless you want to restrict the
macro to looking in only colums 14 & 15, because the same codes are used
elsewhere).

If you merely want to replace the codes with the associated text from the
second column of the reference document, the macro can be simplified. I had
assumed that you wanted to put the text associated with the code in the
adjacent column

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

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
.Execute findText:=oldPart, _
ReplaceWith:=newPart, _
Replace:=wdReplaceAll, _
MatchWholeWord:=True, _
MatchWildcards:=False, _
Forward:=True, _
Wrap:=wdFindContinue
End With
Next i
ChangeDoc.Close wdDoNotSaveChanges


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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[bjr]

Also, just to give you a bit of my VB background. I haven't used Dim
statements before, so I did some research yesterday to learn how to use them
and what they mean. I think I now have a general understanding on their use.
Most of my experience has been writing macros by recording them, then going
in and making any necessary changes within the resulting VB code. I really
appreciate all your work on this to help me.

Barbara

 

[bjr]

Graham ~~~

I tried the macro you sent, and the same error message is coming up that did
in the very beginning. "Run-time error '5854': String parameter too long"
Argh!! That message is what caused me to try using an alternate method. I
think it has to do with the 'search and replace' limitations in VB.

It seems like it should be simple (haha):

1. Search the data source document for each possible code listed in column
1 of
the reference document.
a. If the code is found in the data source document, select/highlight
it and
move to step 2.
b. If the code is not found in the data source document, go back to
step 1.
2. Go to the reference document, find the code in column 1 and
select/highlight the associated text from column 2
3. Go back to the data source and replace the code with the text
from the reference document.
4. Continue this process until all codes have been searched for and
replaced, if
found.

You're right, there's no need to restrict the search to only columns 14 and
15, the codes will not be found elsewhere in the document.

Am I on the right track???

Barbara

 

[bjr]

I will be out of town until Friday, February 20 - I'll check back then!!!
Thanks!!

Barbara

 

[Graham Mayor]

It works fine in my tests here. Which line is highlighted by the error?
Are you able to send me sample documents to the link on the home page of my
web site (zipped!)?

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[bjr]

Hi, Graham ~~~

I'm back in town now (Vegas was great!!) and I'll send you the documents
first thing Monday when I'm back in the office. Thanks!!

Barbara

 

[bjr]

Hmmm...one should never go on vacation!!! I'd like to send you the zipped
files, but I didn't see the link to ftp them - how would you like me to get
those files to you? I'm not terribly versed in sending files, other than ftp
or e-mail.

Thanks!!

Barbara
(e-mail address removed)

 

[Graham Mayor]

The link on the home page of my web site will open an e-mail message. Attach
it to that. Don't change the subject line!

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>