A search macro with search data stored in a separate TXT file

[ivanov.ivaylo]

Hello,

I need a macro that will help me do some editor's checks in an MS Word
environment.

Can you help me write one because this is too complex for my
competence?

Specifications:

1. The macro must start from the cursor position and do a search (and
replace in certain cases).

2. The data that will feed the search field will be stored in a txt
file located in the same folder as the Word file. Each line contains
the search data, a separator "|" and a replace data after the
separator. If there is nothing after the separator, then no
replacement is needed and the macro stops for manual editing of the
text.

3. The macro will take the data from first line in the txt file, do a
search in the Word file for it and

3.1. if there is nothing after the separator "|", then the macro stops
where the search is positive (i.e. when there is a match in the Word
file for the data searched);
EXAMPLE:
, which| //A comma followed by the word "which". I'll check, for
example, if there is a need for a comma at this place. It doesn't
replace anything. The space after "|" is empty.

At this point, I'll do the editing if needed and hit a shortkey to
continue searching. The macro stops if there is a next match, I'll do
the editing, if needed and continue to the end.

3.2. if there is a further data after the separator "|", then the
macro replaces the search match with the value after the separator.
EXAMPLE:
([0-9]).([0-9])|\1,\2 //Find numbers containing a decimal point and
change them to numbers with a decimal comma

4. Then, the macro takes the data from the second line in the txt
file, do a search in the Word file for it.

5. The routine continues when there is no unsearched data left in the
text file, looping to the end of the document, then continuing from
the beginning, but always starting/resuming from the cursor position.

A sample txt file with a search data may look like the following:

, which| //A comma followed by the word "which". I'll check, for
example, if there is a need for a comma at this place. It doesn't
replace anything. The space after "|" is empty.
, that| //A comma followed by the word "that". I'll check, for
example, if there is a need for a comma at this place.It doesn't
replace anything. The space after "|" is empty.
<([0-9]@[/.-])([0-9]@[/.-])([0-9]@>)|MM/DD/YY(YY) //Find all numeric
dates from DD/MM/YY(YY) and changes them to format MM/DD/YY(YY)
([0-9]).([0-9])|\1,\2 //Find numbers containing a decimal point and
change them to numbers with a decimal comma

You can see that the list of search data may contain exact strings and
wildcard expressions. Is it possible for this macro or should it be
implemented in 2 macros - one for the normal search and another for
the wildcard (regex) search?

 

[Fumei2 via OfficeKB.com]

" then no replacement is needed and the macro stops for manual editing of the
text."

There is no way to (at least with a straight forward macro) to stop execution,
so you are stuck right there. The macro can NOT be stopped for manual
editing. Once a macro starts it continues on to the end. Yes, it can be
stopped, but once stopped, it does not start again from that point.

That being said, it may be possible to use a userform. In that case, you
build a userform and load your search parameters into it, and action then one
at a time.

Hello,

I need a macro that will help me do some editor's checks in an MS Word
environment.

Can you help me write one because this is too complex for my
competence?

Specifications:

1. The macro must start from the cursor position and do a search (and
replace in certain cases).

2. The data that will feed the search field will be stored in a txt
file located in the same folder as the Word file. Each line contains
the search data, a separator "|" and a replace data after the
separator. If there is nothing after the separator, then no
replacement is needed and the macro stops for manual editing of the
text.

3. The macro will take the data from first line in the txt file, do a
search in the Word file for it and

3.1. if there is nothing after the separator "|", then the macro stops
where the search is positive (i.e. when there is a match in the Word
file for the data searched);
EXAMPLE:
, which| //A comma followed by the word "which". I'll check, for
example, if there is a need for a comma at this place. It doesn't
replace anything. The space after "|" is empty.

At this point, I'll do the editing if needed and hit a shortkey to
continue searching. The macro stops if there is a next match, I'll do
the editing, if needed and continue to the end.

3.2. if there is a further data after the separator "|", then the
macro replaces the search match with the value after the separator.
EXAMPLE:
([0-9]).([0-9])|\1,\2 //Find numbers containing a decimal point and
change them to numbers with a decimal comma

4. Then, the macro takes the data from the second line in the txt
file, do a search in the Word file for it.

5. The routine continues when there is no unsearched data left in the
text file, looping to the end of the document, then continuing from
the beginning, but always starting/resuming from the cursor position.

A sample txt file with a search data may look like the following:

, which| //A comma followed by the word "which". I'll check, for
example, if there is a need for a comma at this place. It doesn't
replace anything. The space after "|" is empty.
, that| //A comma followed by the word "that". I'll check, for
example, if there is a need for a comma at this place.It doesn't
replace anything. The space after "|" is empty.
<([0-9]@[/.-])([0-9]@[/.-])([0-9]@>)|MM/DD/YY(YY) //Find all numeric
dates from DD/MM/YY(YY) and changes them to format MM/DD/YY(YY)
([0-9]).([0-9])|\1,\2 //Find numbers containing a decimal point and
change them to numbers with a decimal comma

You can see that the list of search data may contain exact strings and
wildcard expressions. Is it possible for this macro or should it be
implemented in 2 macros - one for the normal search and another for
the wildcard (regex) search?

 

[Fumei2 via OfficeKB.com]

Just to reiterate:

"At this point, I'll do the editing if needed and hit a shortkey to
continue searching. "

This is not possible. You can not "continue searching". Procedures (macros)
have no Pause feature.

With careful programming it is possible to set markers that a NEW execution
of the same procedure could look for.

" then no replacement is needed and the macro stops for manual editing of the
text."

There is no way to (at least with a straight forward macro) to stop execution,
so you are stuck right there. The macro can NOT be stopped for manual
editing. Once a macro starts it continues on to the end. Yes, it can be
stopped, but once stopped, it does not start again from that point.

That being said, it may be possible to use a userform. In that case, you
build a userform and load your search parameters into it, and action then one
at a time.

Hello,

[quoted text clipped - 61 lines]

implemented in 2 macros - one for the normal search and another for
the wildcard (regex) search?

 

[Fumei2 via OfficeKB.com]

Before anyone gets on my case....

They have no Pause feature in run-time. In design-time, yes you can Pause.

Just to reiterate:

"At this point, I'll do the editing if needed and hit a shortkey to
continue searching. "

This is not possible. You can not "continue searching". Procedures (macros)
have no Pause feature.

With careful programming it is possible to set markers that a NEW execution
of the same procedure could look for.

" then no replacement is needed and the macro stops for manual editing of the
text."

[quoted text clipped - 13 lines]

 

[ivanov.ivaylo]

Hi,

Then, the workaround procedure with markers is an option

Can you help me with this macro?

Before anyone gets on my case....

They have no Pause feature in run-time.  In design-time, yes you can Pause.

Just to reiterate:

"At this point, I'll do the editing if needed and hit a shortkey to
continue searching. "

This is not possible.  You can not "continue searching".  Procedures(macros)
have no Pause feature.

With careful programming it is possible to set markers that a NEW execution
of the same procedure could look for.

" then no replacement is needed and the macro stops for manual editing of the
text."

[quoted text clipped - 13 lines]

implemented in 2 macros - one for the normal search and another for
the wildcard (regex) search?

 

[Greg Maxey]

You are asking for a very polished cannonball that would take some if not
most people many hours if not days to develop and thoroughly test.

I have a macro that comes reasonable close and with several more hours of
refinement in may suit your needs. You can contact me via my website
feedback if you would like to pursue this on a fee basis.

Hi,

Then, the workaround procedure with markers is an option

Can you help me with this macro?

Before anyone gets on my case....

They have no Pause feature in run-time. In design-time, yes you can
Pause.

Just to reiterate:

"At this point, I'll do the editing if needed and hit a shortkey to
continue searching. "

This is not possible. You can not "continue searching". Procedures
(macros) have no Pause feature.

With careful programming it is possible to set markers that a NEW
execution of the same procedure could look for.

" then no replacement is needed and the macro stops for manual
editing of the text."
[quoted text clipped - 13 lines]
implemented in 2 macros - one for the normal search and another
for the wildcard (regex) search?

 

[Fumei2 via OfficeKB.com]

"You are asking for a very polished cannonball that would take some if not
most people many hours if not days to develop and thoroughly test."

Indeed.

I would definitely agree on the days possibility, at least to polish the
cannonball.

Greg is a skilled and accomplished programmer. If this project is something
you seriously need, you should consider taking him up on his offer. Yes, it
would cost you, but that is appropriate as this requires careful and
dedicated work. What you are asking for is not, in the least, trivial.

Can it be done? Yes, I believe it can, but creating it and, more importantly,
TESTING it to ensure it works the way it is supposed to - well, that takes
time.

You could attempt to try it yourself, but unless you are quite skilled, I am
afraid that you may find it very frustrating. That being said, an effort to
do it yourself would certainly improve your skills in the process. So, it
may be worth your effort. However, if you have any time constraints (you
need it in the next month...), consider Greg's offer.

If you DO want to attempt it, my suggestion is:

- make a simpler procedure that reads search criteria from a txt file
- once you have that working fully, create a userform that will read in the
search criteria from the txt file and do the search
- once you have THAT working fully, add a marker procedure so you can get
the functionality you want (i.e. to search, pause for manual editing, start
the search from the new location)

Step-by-step. It is ONLY way to get the functionality you want. Trust me,
unless you are a Greg (or Jay, or....), attempting to build full
functionality from the start will cause you to tear your hair out.

If you do start yourself, we can help with SPECIFIC questions. But we can
not build this for you (at least not for free).

You are asking for a very polished cannonball that would take some if not
most people many hours if not days to develop and thoroughly test.

I have a macro that comes reasonable close and with several more hours of
refinement in may suit your needs. You can contact me via my website
feedback if you would like to pursue this on a fee basis.

Hi,

[quoted text clipped - 27 lines]

 

[Fumei2 via OfficeKB.com]

Just add another comment.

You have a great advantage in that you seem to be able to write out/define
quite explicit requirements. This is GOOD.

I do have one question though: "The routine continues when there is no
unsearched data left in the text file, looping to the end of the document,
then continuing from the beginning, but always starting/resuming from the
cursor position."

What is the importance of starting/resuming from the cursor position? Why do
you need it to "always" be from the cursor position? Programmatically, it is
much better to code things using Range, not Selection (cursor).

"You are asking for a very polished cannonball that would take some if not
most people many hours if not days to develop and thoroughly test."

Indeed.

I would definitely agree on the days possibility, at least to polish the
cannonball.

Greg is a skilled and accomplished programmer. If this project is something
you seriously need, you should consider taking him up on his offer. Yes, it
would cost you, but that is appropriate as this requires careful and
dedicated work. What you are asking for is not, in the least, trivial.

Can it be done? Yes, I believe it can, but creating it and, more importantly,
TESTING it to ensure it works the way it is supposed to - well, that takes
time.

You could attempt to try it yourself, but unless you are quite skilled, I am
afraid that you may find it very frustrating. That being said, an effort to
do it yourself would certainly improve your skills in the process. So, it
may be worth your effort. However, if you have any time constraints (you
need it in the next month...), consider Greg's offer.

If you DO want to attempt it, my suggestion is:

- make a simpler procedure that reads search criteria from a txt file
- once you have that working fully, create a userform that will read in the
search criteria from the txt file and do the search
- once you have THAT working fully, add a marker procedure so you can get
the functionality you want (i.e. to search, pause for manual editing, start
the search from the new location)

Step-by-step. It is ONLY way to get the functionality you want. Trust me,
unless you are a Greg (or Jay, or....), attempting to build full
functionality from the start will cause you to tear your hair out.

If you do start yourself, we can help with SPECIFIC questions. But we can
not build this for you (at least not for free).

You are asking for a very polished cannonball that would take some if not
most people many hours if not days to develop and thoroughly test.

[quoted text clipped - 8 lines]

 

[Greg Maxey]

I don't hardly anything about reading from .txt files. However, the little
I do know seems to fall short with the examples given:

, which|
([0-9]).([0-9])|\1,\2

Trying to read those with:

Sub ReadThem()
Dim s As String
Open "C:\Test.txt" For Input As #1
While Not EOF(1)
Input #1, s
ActiveDocument.Range.InsertAfter s & vbCr
Wend
Close #1
End Sub

Returns:

'first line blank.

which|

([0-9]).([0-9])|\1

\2


It seems the comma "," acts as line break and is not read as part of the
string. Am I missing something (like the rest of the EOF process iceburg
;-)

I would put the various find|replace pairs in a table in a separate Word
document and load them into an array at the start of the process.

"You are asking for a very polished cannonball that would take some
if not most people many hours if not days to develop and thoroughly
test."

Indeed.

I would definitely agree on the days possibility, at least to polish
the cannonball.

Greg is a skilled and accomplished programmer. If this project is
something you seriously need, you should consider taking him up on
his offer. Yes, it would cost you, but that is appropriate as this
requires careful and dedicated work. What you are asking for is not,
in the least, trivial.

Can it be done? Yes, I believe it can, but creating it and, more
importantly, TESTING it to ensure it works the way it is supposed to
- well, that takes time.

You could attempt to try it yourself, but unless you are quite
skilled, I am afraid that you may find it very frustrating. That
being said, an effort to do it yourself would certainly improve your
skills in the process. So, it may be worth your effort. However, if
you have any time constraints (you need it in the next month...),
consider Greg's offer.

If you DO want to attempt it, my suggestion is:

- make a simpler procedure that reads search criteria from a txt file
- once you have that working fully, create a userform that will read
in the search criteria from the txt file and do the search
- once you have THAT working fully, add a marker procedure so you
can get the functionality you want (i.e. to search, pause for manual
editing, start the search from the new location)

Step-by-step. It is ONLY way to get the functionality you want.
Trust me, unless you are a Greg (or Jay, or....), attempting to build
full functionality from the start will cause you to tear your hair
out.

If you do start yourself, we can help with SPECIFIC questions. But
we can not build this for you (at least not for free).

You are asking for a very polished cannonball that would take some
if not most people many hours if not days to develop and thoroughly
test.

I have a macro that comes reasonable close and with several more
hours of refinement in may suit your needs. You can contact me via
my website feedback if you would like to pursue this on a fee basis.

Hi,

[quoted text clipped - 27 lines]

Message posted via
OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/word-programming/201002/1

 

[Peter Jamieson]

That's because there are various ways to access text file data using the
built-in VBA text file commands:
a. "Write" and "Input"
b. "Print" and "Input Line"
c. "Put" and "Get"
and there's also the functions
Input and InputB

a list of values using a comma-delimited format. "Input"
expects to see a number of comma-delimited values, stripping commas and
line ends, and you need to provide as many variables as the number of
values you want to read.

e.g.

changing this:

While Not EOF(1)
Input #1, s
ActiveDocument.Range.InsertAfter s& vbCr
Wend


to this

Input #1, s, t, u, v
ActiveDocument.Range.InsertAfter _
s & vbCr & _
t & vbCr & _
u & vbCr & _
v & vbCr

and adding the appropriate variable declarations will give you what you
had before. All the commas have been stripped, etc. If you try

Input #1, s, t, u, v, w

you will get an error, so you either have to read the values one at a
time as you were doing, or know how many you need to read. To cut a long
story short, in an international context you also have to consider
different decimal, list and separator delimiters, even those probably
won't behave as expected, and you also need to pay attention to some
special values. Further, if you have list such as

a, b,"c","d""e"

VBA does not interpret it as four values
a
b
c
d"e

but as 5 values
a
b
c
d
e

i.e., you're in a world of pain. Probably better not to go there unless
you are sure you can read your input. (Well, OK, using Write to write a
list is one thing, but make sure whatever it is that's going to read the
list knows what it does).

Print outputs without inserting delimiters (so if you want delimited
output, you have to insert your own, and then you need to consider
doubling up text delimiters etc. Line Input inputs a line at a time, but
then you have to be sure that what you think is a line and it thinks is
a line are one and the same thing. For example, AFAICR a single vbLf
doesn't count as a line end, even though it's the standard line
terminator for Unix files. ISTR that in (much!) older versions of Word
you also had to consider a reasonable possibility that a line might
exceed the maximum string length (probably not something to be too
worried about now though)

Which leaves you with either
a. use GET, or perhaps input and have your code do any necessary
interpretation of the file content, or
b. use another object/library to do it. I wouldn't be surprised if
you'd used the Scripting.FileSystemObject yourself, but then you have to
consider whether that facility is present (also not unreasonable for
recent versions of Windows/office). You could also build something using
Win32 functions - there's an old example at
http://support.microsoft.com/kb/189981
for example. If you need Mac Word VBA code, you'd probably have to stick
to the built-in GET etc. or make calls out to Applescript or some such.

Then there's the whole business of dealing with different text file
encodings...

Peter Jamieson

http://tips.pjmsn.me.uk

I don't hardly anything about reading from .txt files. However, the little
I do know seems to fall short with the examples given:

, which|
([0-9]).([0-9])|\1,\2

Trying to read those with:

Sub ReadThem()
Dim s As String
Open "C:\Test.txt" For Input As #1
While Not EOF(1)
Input #1, s
ActiveDocument.Range.InsertAfter s& vbCr
Wend
Close #1
End Sub

Returns:

'first line blank.

which|

([0-9]).([0-9])|\1

\2


It seems the comma "," acts as line break and is not read as part of the
string. Am I missing something (like the rest of the EOF process iceburg
;-)

I would put the various find|replace pairs in a table in a separate Word
document and load them into an array at the start of the process.

"You are asking for a very polished cannonball that would take some
if not most people many hours if not days to develop and thoroughly
test."

Indeed.

I would definitely agree on the days possibility, at least to polish
the cannonball.

Greg is a skilled and accomplished programmer. If this project is
something you seriously need, you should consider taking him up on
his offer. Yes, it would cost you, but that is appropriate as this
requires careful and dedicated work. What you are asking for is not,
in the least, trivial.

Can it be done? Yes, I believe it can, but creating it and, more
importantly, TESTING it to ensure it works the way it is supposed to
- well, that takes time.

You could attempt to try it yourself, but unless you are quite
skilled, I am afraid that you may find it very frustrating. That
being said, an effort to do it yourself would certainly improve your
skills in the process. So, it may be worth your effort. However, if
you have any time constraints (you need it in the next month...),
consider Greg's offer.

If you DO want to attempt it, my suggestion is:

- make a simpler procedure that reads search criteria from a txt file
- once you have that working fully, create a userform that will read
in the search criteria from the txt file and do the search
- once you have THAT working fully, add a marker procedure so you
can get the functionality you want (i.e. to search, pause for manual
editing, start the search from the new location)

Step-by-step. It is ONLY way to get the functionality you want.
Trust me, unless you are a Greg (or Jay, or....), attempting to build
full functionality from the start will cause you to tear your hair
out.

If you do start yourself, we can help with SPECIFIC questions. But
we can not build this for you (at least not for free).

You are asking for a very polished cannonball that would take some
if not most people many hours if not days to develop and thoroughly
test.

I have a macro that comes reasonable close and with several more
hours of refinement in may suit your needs. You can contact me via
my website feedback if you would like to pursue this on a fee basis.

Hi,

[quoted text clipped - 27 lines]
Message posted via
OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/word-programming/201002/1