looping a copy paste macro or do it differently 1

[glarior]

Its been a long time since I have touched any of this but I am trying to create a simple program for me. I am using VB 6.5 that comes with MS word. I want to open the file, search for a paticular word and when it finds the word to copy the entire line and paste it into a new document.
I figured out how to find, copy, paste the entire line but I need it to repeat the process so it will search the entire document... how would I do that?

I had a EOF loop and I crashed MS word which lost my coding thus far lol. No biggie because I can write that part again.


Would you suggest another way of doing this? For all I know I am going in the wrong direction.

Thanks...

 

[Bong]

I think you should look at the Find object (in Word VBA help). There are some examples that will guide you (I recommend using the Range.Find form rather than the Selection.Find form. Your iteration key will be If myRange.Find.Found = True.

_________________
Bob Rashkin

 

[SkipVought]

Hi,

...find, copy, paste the entire line ...

There is no such entity in MS Word. There are words, sentences & paragraphs, but no lines, except for shapes that are lines. But I do not think that line shapes is what you mean.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[glarior]

I think I will have to do this differently.
I have a .docx file with a format of..
Name Address DOB Contact#"
John Smith 555 Main Rd 5555 MO 01 Jan 1999 555-555-555

The goal is to be able to enter a search term into a text box and which ever information matches that search term should be written to another file for printing. For example I want to look for anyone living in MO so I will type "MO" into the text box. The program will open the file, search for the term and if found write the name, address, DOB, and contact number to another file for printing.
I am trying to figure out how to do that. Any help is appreciated.

 

[mintjulep]

Why are you using a word processor to try to manage data?

Copy to Excel, Text to Columns, filter, done.

 

[glarior]

Actually, I don't know why. I have always used word for my macros. I think your idea will work and I will see what I can do in excel

 

[white605]

There is no such entity in MS Word

Skip, you are absolutely correct about this.
Is there a reason we should not "simulate" a line like

Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

in some cases to get the text we want. IOW will what I use sometimes eventually come back and bite me later?
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[SkipVought]

Gerry or one of our other MS Word gurus, will need to verify this, but I believe that a line MAY be an entity that is printer-driver specific. What could be a line on your PC, may not be a line on some other PC, all other things being equal. That's my guess.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[glarior]

I did tried the selection.end key... etc code and it worked for what I wanted. The only problem was it would only get the one line when I wanted it to loop and get other lines. Also, I tried doing the filter thing with excel and I can do it but the people who is going to use this program can't figure it out and don't want to learn. Sad. So back to my word based idea....

 

[glarior]

Ok, trying it a easier way (hopefully). Here is the code which was created using the macro record. That does exactly what I want the program to do but I need to take it one step further. I need the program to go ahead and repeat the process for every time it finds the textSearch word. Any ideas on how I can do that?

Private Sub CommandButton1_Click()

Dim textSearch As String
textSearch = TextBox1

Selection.Find.ClearFormatting
With Selection.Find
.Text = textSearch
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.HomeKey Unit:=wdLine
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Copy

ChangeFileOpenDirectory "C:\Users\Tick\Desktop\"
Documents.Open FileName:="FileOutputPatientSearch.docx", _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto, XMLTransform:=""

Selection.Find.Execute
Selection.Find.Execute
Selection.PasteAndFormat (wdPasteDefault)

End Sub

 

[glarior]

bump,

Anyone?

 

[fumei]

1. as has been mentioned, whenever possible, avoid using Selection. Use Range.

2. if you want to keep performing action on a search string, while you CAN use Bong's

If myRange.Find.Found = True

it is not really required. If you make the .Execute (and its parameters) instruction go through a Boolean (True/False) test, this is the equivalent of .Find.Found = True/False. An .Execute can not be True unless its .Found is also True.

So, to recap what I understand you want to do. You have a userform and when you click a commandbutton

1. open "the" file (WHAT file??? How are you determining which file to open????)
2. make a string variable = the text of a Textbox
3. search the open file for the search string
4. for EVERY found instance of that search string, create a new document that will have the text of the "line" that contains the found string.

OK. Can be done. Some questions though. You make no mention of what you are doing with the new documents. Saving them? Keeping them all open?

As Skip mentions, the term "line" is a bit tricky in Word. Word primarily deals with paragraphs.

IF - repeat IF - these "lines" are indeed paragraphs, then it makes things much easier. Let me demonstrate. When describing things, <p> is the standard for a paragraph mark.

This is the first
line of four "lines"
but it is really just
one single paragraph.<p>

The above has four "lines", but is ONE paragraph.

This is the first<p>
line of four "lines"<p>
but it is really<p>
four separate paragraphs.<p>

The above has four "lines", but is also FOUR paragraphs.

Can you see how similar they look? However, from Word's perspective (as if a piece of software can actually have a pespective...) they are VERY VERY different.

Again, IF your "lines" are actually paragraphs, then it makes it much easier. Here is some code.

Private Sub CommandButton1_Click()
Dim textSearch As String
Dim ThisDoc As Document
Dim WhateverDoc As Document
Dim r As Range
[COLOR=red]'  where is the code to open "the" file??!!!
'  This file MUST be open for the following to work
'  as it uses ActiveDocument[/color red]
textSearch = TextBox1.Text
[COLOR=red]' this is the CURRENT open document![/color red]
Set ThisDoc = ActiveDocument

Set r = ThisDoc.Range

With r.Find
  [COLOR=red]'  for EVERY .Found[/color red]
  Do While .Execute(FindText:=textSearch, _
                Forward:=True)=True
       With r  [COLOR=red]' the range of the .Found[/color red]
         .Expand Unit:=wdParagraph
         .Copy
     [COLOR=red]' make a [b]new[/b] blank doc[/color red]
         Set WhateverDoc = Documents.Add
     [COLOR=red]' put in the grabbed text and SaveAs[/color red]
         With WhateverDoc
            .Range.Paste
            .SaveAs FileName:="c:\" & "Whatever" & _
               j & ".doc"
             .Close
         End With
[COLOR=red]' Collapse the range [b]so loop can continue[/b][/color red]
         .Collapse 0
      End With
      j = j + 1
   Loop
End With
End Sub

The above will search for the text, expand the range to the paragraph it is in, make a new document, put the text of the paragraph in the new document, do a SaveAs, and then close the new file. It then Collapses the range in order to continue on until the search string is not found. The Collapse is very important!

I would strongly recommend using Document objects, as in the code above. It makes working with specific documents much more clear.

The above may not work quite correctly, as I do not know what you are doing regarding documents. The VBA is running from a document (of course), and you are opening a file? This is my assumption so the ThisDoc object is that file, and the Whatever document is another, new, document to which the text is inserted.

Gerry

 

[glarior]

I see what everyone means by line vs paragraph now. I am using paragraphs. Also, after seeing that code I have realized I was going about this the wrong way. Thank you for the that.

I have been playing around with the code and trying different things and have it working for the application. The only problem I have left is when I .range.paste it overwrites the previous paragraph entry on the Whatever document. I tried to add a paragraph before and after the paste but it still overwrites the previous lines. How would I made the .paste not overwrite the previous paragraph?

Do While .Execute(FindText:=textSearch, _
                Forward:=True) = True
        With r  ' the range of the .Found
            .Expand Unit:=wdParagraph
            .Copy
     
        ' put in the grabbed text
            With WhateverDoc


 'This needs to not overwrite the previous text and just add the .range.paste to the current document. 
       
                .Range.InsertParagraphBefore
                .Range.Paste

                               
            End With
            
            
' Collapse the range so loop can continue
            .Collapse 0
        End With
      j = j + 1
   Loop

 

[fumei]

The only problem I have left is when I .range.paste it overwrites the previous paragraph entry on the Whatever document. I tried to add a paragraph before and after the paste but it still overwrites the previous lines. How would I made the .paste not overwrite the previous paragraph?

Huh??????

As it stands, the Whatever document can NOT have a "previous" paragraph! It is a new blank document.

Set WhateverDoc = Documents.Add

Please describe EXACTLY what you are trying to do.

'This needs to not overwrite the previous text and just add the .range.paste to the current document.

.Range.InsertParagraphBefore
.Range.Paste[/code]I do not understand. Your original request was:

"I want to open the file, search for a paticular word and when it finds the word to copy the entire line and paste it into a new document."

My bolding.

Again, please try and describe EXACTLY what you are trying to do.

Gerry

 

[glarior]

Ok, I will do my best to describe this...thank you for being patient with me and apologize for the confusion.

The program has a search term for "COPD" which searches the currently opened word document

Set ThisDoc = ActiveDocument

(this part works perfectly)

When the term "COPD" is found it will copy that paragraph, open the FileOutput.doc (instead of creating a new document) and paste the paragraph after the last entry in the FileOutput.doc.

I was thinking of a new document as the FileOutput but in programming new document means what you were thinking... sorry about that. From what I said make more sense? If not let me know and I will elaborate more.

Thanks again!! I really do appreciate this help!

 

[stevexff]

Why are you using a word processor to try to manage data?

While the discussion on this thread is of academic interest, mintjulep is right on here. Your requirements are better served by Excel or even a database.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[glarior]

I agree with steve & mintjulep. However, I created a basic database which is "to complicated" for the two people who are going to use it. I also created a excel sheet which can filter but they kept messing it up. This is why I decided to do it using word. That way they only see one text box and hit one button and it is very simple, no way to mess it up unless they delete the code lol. I am in the process of finding a company who creates software for our purposes but until then this simple program will save lots of work.

 

[fumei]

OK, sure that makes sense. But you did state a new document - with no mention of appending the found text to an existing document.

I want to open the file, search for a paticular word and when it finds the word to copy the entire line and paste it into a new document.

So, in a way, this makes it easier. Some questions though...

Originally, you stated the search string comes from a textbox. Your last post sounds like an explicit string "COPD". Which is it? If it is ONE string you are loooking for, then what is the textbox for?

Further - and again, I ask you to please state EXACTLY what it is you want to do - are all found strings going to be appended to the same document FileOutput.doc?

In other words, if "COPD" is found four times, do you want each expanded paragraph to be appended one after the other into the same FileOutput.doc?

This can be done, and fairly easily, but again, you have to state EXACTLY what it is you want to do. In fact, as you can see in the following code, to do what you say - so far - you want to do, is actually quite simple and straightforward. There is not even a need to do ANY Copy and Paste.

Option Explicit

Private Sub CommandButton1_Click()
Dim textSearch As String
Dim ThisDoc As Document
Dim DumpThere As Document
Dim r As Range

textSearch = TextBox1.Text

Set ThisDoc = ActiveDocument
Set DumpThere = Documents.Open _
   (FileName:="C:\Users\Tick\Desktop\FileOutputPatientSearch.docx")
     
Set r = ThisDoc.Range

With r.Find
  Do While .Execute(FindText:=textSearch, _
                Forward:=True) = True
      r.Expand Unit:=wdParagraph
      DumpThere.Range.InsertAfter r.Text & vbCrLf
      r.Collapse 0
  Loop
End With
End Sub

What it does.

1. declare variables/objects

2. set your search string variable to the textbox.

3. set ThisDoc as the active document

4. open the file FileOutputPatientSearch.docx and set it as DumpThere.

5. set the range object (r) as the ThisDoc range. Note that FileOutputPatientSearch is now the ActiveDocument...but it does not matter; the code is actioning document OBJECTS.

6. uses Find of the range (r, or ThisDoc) to find the search string.

7. when found, expand the range to the paragraph.

      r.Expand Unit:=wdParagraph

Thus the text of the range (r.Text) is now the whole paragraph. It is a string.

8. insert THAT string to the end of DumpThere (FileOutputPatientSearch).

      [b]DumpThere.Range.InsertAfter[/b] r.Text & vbCrLf

Note that it is inserted as a string. It is NOT copied and pasted. I added a paragraph mark (vbCrLf), as (to me at least) it is needed. Otherwise all your found strings will be appended as one paragraph. Adjust for your needs.

9. collapse the current range to its End, so you can continue the loop.


Done. All instances of the whole paragraph containing the search string are appended - one after the other - to the end of the other document.

Gerry

 

[glarior]

Works perfectly. What I could not figure out was the syntax of

DumpThere.Range.InsertAfter r.Text & vbCrLf

I was trying to use the syntax of

.Range.InsertParagraphAfter

which obviously did not work. Once again, sorry for not being clear and making something simple become complicated and confusing. Thank you very much for your help! Thanks!

 

[fumei]

You are most welcome. Ranges are a very important part of understanding and using Word VBA. Hopefully this will help you improve your skills.

Part of your original problem was that you were using Copy and Paste, which - in this case - is actually not needed. Plus, again, where you can (and that is 99% of the time) do NOT use Selection.

Gerry