excel/word vba help! 1

[tektipuser88]

hi there,

have code below which used to work fine but suddenly not working... application errors and closes itself when it gets to the .find .text bit.

I've made sure i have a ref to word ojb lib.

i'm pulling my hair out as to why it won't work anymore.. any help, hints tips is most welcome!

thanks in advance!

Sub findAndPaste()


Dim aimaApp As Word.Application, targApp As Word.Application
Dim aimaDoc As Word.Document, targDoc As Word.Document
Dim searchString As String
Dim personApp As Word.Application

Set aimaApp = New Word.Application

With aimaApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

'open up current AIMA doc
'Set aimaDoc = aimaApp.Documents.Open("W:\Investor Services\AIMA Due Diligence\Auto AIMA\AIMA Questionnaire.doc")
Set aimaDoc = aimaApp.Documents.Open("C:\Hell.doc")


Set personApp = New Word.Application

With personApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

Set targDoc = personApp.Documents.Add

searchString = "hello"

aimaDoc.Activate

With aimaApp.Selection.Find
'app errors out here, if i put mouse over .text it says 'invalid procedure call or argument'!!
.Text = searchString
.Wrap = wdFindContinue
End With

aimaApp.Selection.Find.Execute
aimaApp.Selection.SelectRow
aimaApp.Selection.Copy

targDoc.Activate
personApp.Selection.PasteSpecial
personApp.Quit (False)
aimaApp.Quit (False)

End Sub

 

[fumei]

Seems you have multiple instances.

You Dim three Word application objects:

Dim aimaApp As Word.Application, targApp As Word.Application
Dim personApp As Word.Application

You set two of them as New:

Set aimaApp = New Word.Application

' other stuff
Set personApp = New Word.Application

And one of them as an document object:

Set targDoc = personApp.Documents.Add

In NO cases, do you properly destroy the instances.

In any case, the problem , I think, is here:

Set targDoc = personApp.Documents.Add
      searchString = "hello"
   aimaDoc.Activate
        With aimaApp.Selection.Find

Well, I know it is, as you are erroring out with the Selection.Find. IMO, properly.

WHAT instance of Word - not what document - is "live"?

It is NOT aimaApp. You just Set targDoc.

Frankly, Word is a good dog, but using three instances of it at the same time is like beating it with a stick.

IMO, there are very few legitmate cases where multiples instances are required. Word can handle a lot, and usually with ONE instance. Creating three seems way way way out of line. In terms of memory usage, it is like hanging a "Kick me" sign on Word.

Why are you doing this?




faq219-2884

Gerry
My paintings and sculpture

 

[MakeItSo]

...like hanging a "Kick me" sign on Word"

As good a way to put it as any, Gerry!

P.S. ttuser has three apps, and two docs. The one he set to document WAS dimmed as document ==> targetAPP, targetDOC
;-)

However you are totally right of course, one instance is fully sufficient.
Additionally, targetDOC is not created within the targetAPP instance. A bit inconsequential.


1. Reduce your application instances to one
2.

personApp.Selection.PasteSpecial

PasteSpecial - and how special? specify the speciality.
;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[tektipuser88]

Hey thanks for looking at my problem.

hehe.. very funny remarks as well!

All i want to do is just copy and paste certain bits in a large document (all of which are contained in tables) into individual separate docs according to a lookup.

 

[MakeItSo]

Here's your code, boiled down to something that should work:

Sub findAndPaste()

Dim [b]wrd As Word.Application[/b]
Dim aimaDoc As Word.Document, targDoc As Word.Document
Dim searchString As String

Set wrd = New Word.Application
Set aimaDoc = wrd.Documents.Open("C:\Hell.doc")
Set targDoc = wrd.Documents.Add
          
searchString = "hello"
aimaDoc.Activate
[b]
With wrd.Selection.Find
    .ClearFormatting
    .Format = False
    .MatchCase = False
    .MatchWildcards = False
    .Text = searchString
    .Wrap = wdFindContinue
    .Execute
End With

If wrd.Selection.Find.Found Then
    aimaApp.Selection.SelectRow
    aimaApp.Selection.Copy
    targDoc.Range.PasteSpecial
    targDoc.SaveAs whatever, wdFormatDocument
Else
    MsgBox "Search string not found in aima-DOC!"
End If

targDoc.SaveAs whatever, wdFormatDocument
[/b][green]
''to activate the document:
'wrd.Visible
'wrd.WindowState = wdWindowStateMaximize

'or to quit:
'aimaDoc.Close False
'targDoc.Close True
'wrd.Quit
'
'Set aimaDoc = Nothing
'Set targDoc = Nothing
'Set wrd = Nothing
[/green]
End Sub

As you can see, one word application object suffices.
I have not changed the rest and left it to "Selection" instead of working with "Range" or "Content", which is probably the next best thing to do, once the code works.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[tektipuser88]

Fantastic, many thanks!

In getting this problem, it's made me realise just how little i know about objects etc.

Are there any good links/books that will educate me on this?

Any help would be greatly appreciated!

Thanks again all!!!

e

 

[MakeItSo]

Ooooooops!

Just seen that I've accidentally left the "aimaApp" object in one "with" block.

Sub findAndPaste()

Dim wrd As Word.Application
Dim aimaDoc As Word.Document, targDoc As Word.Document
Dim searchString As String

Set wrd = New Word.Application
Set aimaDoc = wrd.Documents.Open("C:\Hell.doc")
Set targDoc = wrd.Documents.Add
          
searchString = "hello"
aimaDoc.Activate

With wrd.Selection.Find
    .ClearFormatting
    .Format = False
    .MatchCase = False
    .MatchWildcards = False
    .Text = searchString
    .Wrap = wdFindContinue
    .Execute
End With

If wrd.Selection.Find.Found Then
    [red][b]wrd[/b].Selection.SelectRow
    [b]wrd[/b].Selection.Copy[/red]
    targDoc.Range.PasteSpecial
    targDoc.SaveAs whatever, wdFormatDocument
Else
    MsgBox "Search string not found in aima-DOC!"
End If

targDoc.SaveAs whatever, wdFormatDocument
...

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[fumei]

All i want to do is just copy and paste certain bits in a large document (all of which are contained in tables) into individual separate docs according to a lookup"

You would have to spec it out, but the following does:

1. sets a document object for the current document
2. sets a search string. This could be set by other
methods: an inputbox, a string from some other
procedure...whatever
3. using a table object, loops through all tables, setting
a Range object for the table range
4. checks the text of the Range (each table) for the
search string
5. if the range (each table) HAS the search string, create
a new document, as a document object
6. the new document range is "", therefore making it equal
to the text of Range object (each table) makes its
text equal to the table. A copy. NO copy and paste
is required.
7. saves the new document. In this example each new
document ends with "Extract"#.doc", as in extract1.doc,
extract2.doc, extract3.doc....
8. closes the new file, focus is back to the starting doc.
9. goes on to the next table

Voila. You search through all the tables, looking for a text string. If you find a table with that string, extract the text out and save it as a new document.

Done. Nothing is ever selected, or copied, or pasted.

Note 1: the extracted text is the text from the table it was found in. It is NOT the table. The documents saved contain the text, NOT the table. It is possible to get the table.

Note 2: it was not specified if all the table text was required, or just part of it. The code takes all of it, but it could certainly be adjusted (with proper logic) to extract any part you like.

Sub ExtractTableCrap()
Dim searchString As String
Dim r As Range
Dim j As Long
Dim aTable As Table
Dim ThisDoc As Document
Dim ThatDoc As Document
j = 1
Set ThisDoc = ActiveDocument
searchString = "THIS TABLE"
  For Each aTable In ActiveDocument.Tables
    Set r = aTable.Range
    If InStr(aTable.Range.Text, searchString) > 0 Then
      r.Copy
      Set ThatDoc = Documents.Add
      ThatDoc.Range = r
      ThatDoc.SaveAs _
            FileName:="c:\test\Extract" & j & ".doc"
      ThatDoc.Close
      j = j + 1
      Set r = Nothing
      Set ThatDoc = Nothing
    End If
  Next
End Sub

The above searches through all the tables in the current document. If the table has - anywhere in it - the string "THIS TABLE", the text of that table is dumped into a new document. The created new documents are saved and named incrementally.

faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

Obviously the code I posted above is not using a created instance of Word. It is using Word itself. However, it could be used by ONE instance of Word. There is no need - as far as I can see - for these multiple instances. If you need to create ONE instance, and load your source file...no problem. Do that. It does not matter. You can use that one instance to do whatever other processing you need.

So use ONE Word.Application object and go for it.

faq219-2884

Gerry
My paintings and sculpture

 

[tektipuser88]

Many thanks for that!