Searching a word doc through macros

[nehachoubey]

Hi,
I have inserted a word document in one of the cell in my worksheet. Now my requirement to to search for some string in that doc and accordingly perform some task.. so i want to know can we connect to word documents through macros?

 

[Kenneth Hobson]

Yes.

 

[nehachoubey]

ok thanks. My object name is Object3. I have to search for the string "err_code :=" and whatever is there on the right side inside qotes ' ' i have to copy that in another column. Plz help!

 

[Kenneth Hobson]

The solution will be in two steps. (1) Set the OLE object as an MSWord file. (2) Do the find as you would any Excel to MSWord project. I don't have time right now to put it all together for you exactly but this should get the ball rolling.

You can adapt this OLE method to fit your needs. To see what you need for finding a string and then getting the adjacent string in single quotes, record the find in MSWord and adapt it to this macro. After the code, I listed some links for search/replace routines to give you a starting point for the find part.

Sub test_PasteOLEObjectIntoSheet()
  PasteOLEObjectContentIntoSheet "oDoc", "Sheet2"
End Sub

'Set Reference to Microsoft Word xx.x Object Library
Sub PasteOLEObjectContentIntoSheet(sOLE As String, sSheet As String)
  Dim wdApp As Word.Application
  Dim myDoc As Word.Document
  Dim wdstory As Word.Range
  Dim oOLE As OLEObject
    
  Set oOLE = ActiveSheet.OLEObjects(sOLE)
  'MsgBox oOLE.OLEType  'Linked or not.
  oOLE.Verb xlPrimary
  Set wdApp = GetObject(, "Word.Application")
  'wdApp.Visible = True
  wdApp.Visible = False 'OLEObect will show briefly even with this setting.
  Set myDoc = wdApp.ActiveDocument
    
  Do
  Loop Until Not myDoc Is Nothing

'  MsgBox myDoc.Name
  Set wdstory = myDoc.Content
  'Same as that above
  'Set wdstory = myDoc.StoryRanges(wdMainTextStory)
  With wdstory.Font
    .Name = "Arial"
    .ColorIndex = wdRed
    .Size = 16
  End With

      
  With Excel.ThisWorkbook.Worksheets(sSheet)
    .UsedRange.Clear
    'Setting the value like this does not include attributes.
    .Range("A1").Value = myDoc.Content
    
    myDoc.Range(myDoc.Content.Start, myDoc.Content.End).Copy
    
    Excel.Worksheets(sSheet).Range("A2").PasteSpecial
    'Another method to paste.
    'ThisWorkbook.Worksheets(sSheet).Activate
    'Excel.ActiveSheet.Paste
    'ActiveSheet.Paste
    Application.CutCopyMode = False
  End With
   
  myDoc.Close
  wdApp.Quit
  Set wdApp = Nothing
  Set myDoc = Nothing
  Set wdstory = Nothing
End Sub

'FindReplace Text
' http://www.excelforum.com/excel-pro...rd-in-ms-word-with-varable-from-ms-excel.html
' Multiple Find/Replace
' http://www.excelforum.com/excel-programming/794297-struggling-with-a-find-replace-macro-to-word.html
' Single Find/Replace
' http://www.vbaexpress.com/forum/showthread.php?t=38958

 

[nehachoubey]

thanks alot for the help but i m new to macros so plz can u explain with a small example? Suppose the doc is error.doc and i have to search for the string "p_err_cod=" in the entire doc and copy the err code after the "=" sign in another column.

 

[nehachoubey]

hey kenneth it is throwing error in this line : Dim wdApp As Word.Application. says not allowed.. ??

 

[Kenneth Hobson]

This comment:

'Set Reference to Microsoft Word xx.x Object Library

means to set it in the VBE, Tools > References..

 

[nehachoubey]

oh ok thanks

 

[nehachoubey]

i am getting the following error now : unable to get the oleobjects property of the worksheet class

 

[Kenneth Hobson]

Without seeing the Excel file with the code, it is hard to give advise. Since this site does not allow attachments, you can post to a free site like 4shared.com or one of the many others.

Most likely, you referred to an OLE object name that did not exist.