Re: I'm desperate!! Search, if found, insert, else search for nex
- From: "Graham Mayor" <gmayor@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 13 Feb 2009 15:59:42 +0200
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
.
- Follow-Ups:
- References:
- I'm desperate!! Search, if found, insert, else search for next
- From: bjr
- Re: I'm desperate!! Search, if found, insert, else search for next
- From: Doug Robbins - Word MVP
- Re: I'm desperate!! Search, if found, insert, else search for nex
- From: bjr
- Re: I'm desperate!! Search, if found, insert, else search for nex
- From: Graham Mayor
- Re: I'm desperate!! Search, if found, insert, else search for nex
- From: bjr
- I'm desperate!! Search, if found, insert, else search for next
- Prev by Date: Re: Select whole word at cursor position
- Next by Date: Checking whether paragraphs have been formatted with outline numbered headings or manually?
- Previous by thread: Re: I'm desperate!! Search, if found, insert, else search for nex
- Next by thread: Re: I'm desperate!! Search, if found, insert, else search for nex
- Index(es):
Relevant Pages
|
