1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA Search and Replace (Word and Excel) version 2003

Discussion in 'Business Applications' started by Keebellah, Jan 16, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Well I'm puzzled and intrigued by the following:

    I have two macros.
    I started with one triggered in Word to search and replace for a string and replace with with another one.
    The mcro opens an Excel file and stores the necessary data in an array and then the array's values are used to search and replace the corresponding strings in a word file:

    Below is the code that works if you use it in Word
    where fString is the search to look for and rString the string to replace it with, works perfect no problem here

    Code:
    Sub FindColALL(fString As String, rString As String)
    '   Selection.Find.ClearFormatting
        Selection.Find.Replacement.ClearFormatting
        With Selection.Find
            .Text = fString
            .Replacement.Text = rString
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = True
            .MatchWholeWord = True
            .MatchByte = False
            .CorrectHangulEndings = True
            .MatchAllWordForms = False
            .MatchSoundsLike = False
            .MatchWildcards = False
            .MatchFuzzy = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
    End Sub
    
    Now I decided to do it the other way. Open Excel, read the contents into the same array, then Open work (VBA references set and everything)
    and do the same but calling it from Excel but the above code will not work,

    Seems it doesn't recognize the Word commands even if the references are set.

    I now use the following code:

    Code:
    For Each oWord In wdoc.Words
        If UCase(Trim(oWord.Text)) = UCase(fString) Then
             oWord.Text = rString & Space(1)
        End If
    Next oWord
    
    This also does it more or less but the the variables are very tricky it can only search for the word so if by accident a space is omitted the word to search for might be overlooked.

    Maybe there is another way to insert variables in the Word file (which is like a template) to search for and replace. I'm not that acquainted with Word VBA

    I prefer the Excel -> Word approach instead of the first one Word -> Excel.

    Any tips and hel would be appreciated.

    I imagine it's the VBA syntax incompatibility between Word and Excel, but I'm stuck.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I don't see where you have defined wdoc. Please post your fulll code or at least the portion where you are creating your instance of Word.

    Rollin
     
  3. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Sorry, my mistake, the dimensioning line of code

    Dim fString As String, rString As String, oWord As Object

    maybe it's the .Object, but I couldn't find anything else

    The search string is simple COLUMNA? were the ? will be a character representing the column header A, B, C, IV, AA,

    In the wordfile the search string was <COLUMNA?> but thsi is not recognized as one word so I changed it to COLUMNA? without the < and the >
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Hans, I recommend that you Google the terms "late binding" and "early binding" + "VBA" to learn about the two different ways of creating and working with objects in VBA. When something is declared as an "object" type this is known as late binding and there is no need to set any external references in the VBA editor. One of the advantages to using this method is that you can create your VBA code and it is not dependent on any specific library version (e.g - Word Object Library 8.0, Word Object Library 9.0, etc) This means the code can run on any machine regardless of the version of Office that is installed. The downside is that the VBA Intellisense will not be available. Early binding (setting reference beforehand) is the easier and preferred method since VBA Intellisense will be available but as I said before it may cause problems if there are different versions of the application installed. Here is an example of both methods and they should both work for you.

    Example of early binding -- requires setting reference to Microsoft Word object library in VBA editor

    Code:
    Sub EarlyBinding()
    
    Dim objWord As New Word.Application
    Dim wDoc As Word.Document
    
    objWord.Visible = True
    
    Set wDoc = objWord.Documents.Open("C:\Test.doc")
    
    For Each oWord In wDoc.Words
        If UCase(Trim(oWord.Text)) = UCase(fString) Then
             oWord.Text = rString & Space(1)
        End If
    Next oWord
    
    End Sub
    Example of late binding -- No references need to be set using this method.

    Code:
    Sub Late_Binding()
    
    Dim objWord As Object
    Dim wDoc As Object
    
    Set objWord = CreateObject("Word.Application")
    
    objWord.Visible = True
    
    Set wDoc = objWord.Documents.Open("C:\Test.doc")
    
    For Each oWord In wDoc.Words
        If UCase(Trim(oWord.Text)) = UCase(fString) Then
             oWord.Text = rString & Space(1)
        End If
    Next oWord
     
  5. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Thanks, the explanation is clear but what I hoped to get is the way that like the first code I used in Word, I could search for a string value and the oword looks for what the results 'think' is a work but do not always relay to the search string.
    What do I need to do to achieve this?

    All I really need is a code that will permit me to go through the array and searcg for fstring whis would be "<COLUMNAA>" notice the < and the > both included to define the search string and replace that with rString.

    The Oword variant won't accept that and won't find it because it is not a 'word'

    Any suggestions or am I asking something impossible?
     
  6. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Rollin,
    I'm still struggling so I've put together two sample files
    One Word file and an Excel file

    Put both of them in the same folder
    To show what I mean there are two steps:
    1. Open the Word File and run the macro MergeData
    This will create a file named 'WRD-test-001.doc'
    Clos the word file
    2. Open the Excel file and press the rectangel to execute the macro.
    The "Word Merget test.doc" will be opend and saved as "Test-doc-23-01-2012.doc"

    If you compare both results you'll see that the once created with the word macro is nice an clean and the one from Excel still contains the < because it cannot 'recognize' strings but looks for words.

    I hope this illustrates what I've tried to put in words. Maybe it's a simple thing but it has been keeping me busy without any success.

    Thanks for looking. :)
     

    Attached Files:

  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I just tested the code from both applications and it works fine. In order to make it work from Excel you need to declare your Word object with Public scope at the module level and then make the changes highlighted in red below.

    First remove your declaration for wdApp (Dim wdApp As Word.Application) from the "ProcessData" subroutine and move it to the very top of the module. As I stated before this must be declared in Public scope so that it is available to all subroutines in the module. After making the change the top of your module should appear as I've shown below.

    Code:
    Option Explicit
    Option Base 1
    [COLOR="Red"]Public wdApp As Word.Application[/COLOR]
    Then change the code in your "FindColALL" sub to reference your "Public" Word object

    Code:
    Sub FindColALL(fString As String, rString As String)
    
        [COLOR="Red"]wdApp.[/COLOR]Selection.Find.ClearFormatting
       [COLOR="Red"] wdApp.[/COLOR]Selection.Find.Replacement.ClearFormatting
        With [COLOR="Red"]wdApp.[/COLOR]Selection.Find
            .Text = fString
            .Replacement.Text = rString
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = True
            .MatchWholeWord = True
            .MatchByte = False
            .CorrectHangulEndings = True
            .MatchAllWordForms = False
            .MatchSoundsLike = False
            .MatchWildcards = False
            .MatchFuzzy = False
        End With
        [COLOR="Red"]wdApp.[/COLOR]Selection.Find.Execute Replace:=wdReplaceAll
    End Sub


    Here is what the ProcessData subroutine should now look like.

    Code:
    Sub ProcessData()
    '
    ' Macro recorded and Editted 04-01-2012 by Hans Hallebeek
    Dim xlApp As Excel.Application
    Dim wdoc As Word.Document, xlPath As String, xlFile As String, wdFile As String
    Dim xlWB As Workbook, xlWS As Worksheet, xlWSA As Worksheet
    Dim outputDoc As String, Inscricao As String, Artigo As String
    
    Dim tString As String, sCol As String
    Dim r As Long, c As Long, lc As Long, myArr(), i As Long, adm As Long
    
    wdFile = "Word Merge Test.doc"
    xlPath = ActiveWorkbook.Path
    xlFile = ActiveWorkbook.Name
    
        Sheets("Data").Activate
        
        r = Range("A" & Rows.Count).End(xlUp).Row
        If r < 2 Then
            MsgBox "Sheet contains no data!", vbExclamation, ""
            Exit Sub
        End If
        
        If InPath(xlPath, wdFile) = False Then
            MsgBox "File  '" & wdFile & "' not present in " & xlApp.Path, vbCritical, "Aborted !!!"
            Exit Sub
        End If
    
        Set xlWB = Workbooks(ActiveWorkbook.Name)
    
        On Error GoTo incorrectSheet
        Set xlWS = xlWB.Sheets("Data")
        On Error GoTo 0
        
        ' Open word file for data merge
        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = True
        Set wdoc = wdApp.Documents.Open(xlPath & "\" & wdFile, ReadOnly:=True)
        
        ' open an existing workbook
        ' example excel operations
        r = 2
        With xlWB.Sheets(xlWS.Name)
            lc = xlWB.Sheets(xlWS.Name).Cells(1, Columns.Count).End(xlToLeft).Column
            ReDim myArr(1 To lc, 2)
            While xlWB.Sheets(xlWS.Name).Cells(r, 1).Formula <> ""
                i = 0
                For c = 1 To lc
                    i = i + 1
                    myArr(i, 1) = cAddr2Char(xlWB.Sheets(xlWS.Name).Cells(1, c).Address)
                    myArr(i, 2) = xlWB.Sheets(xlWS.Name).Cells(r, c).Value 'Formula
                Next c
                r = r + 1
            Wend
        End With
        
        'GoSub CloseWorkbook
        
        wdoc.Activate
        outputDoc = vbNullString
        
        Dim fString As String, rString As String, oWord As Object
        
        outputDoc = "Test-doc-"
    
    
    [COLOR="Red"]       If i > 0 Then
    
           For i = LBound(myArr) To UBound(myArr)
                sCol = myArr(i, 1)
                tString = myArr(i, 2)
                FindColALL fString:="<COLUMNA" & Trim(sCol), rString:=tString
            Next i
            ActiveDocument.SaveAs ActiveDocument.Path & "\" & "WRD-test-001.doc"
    
           End If[/COLOR]
    
    
    
        If Right(Trim(outputDoc), 1) = "-" Then
            outputDoc = Trim(outputDoc) & Right("00" & Day(Date), 2) & "-" & Right("00" & Month(Date), 2) & "-" & Year(Date)
            Application.DisplayAlerts = wdAlertsNone
            wdoc.SaveAs wdoc.Path & "\" & outputDoc
            Application.DisplayAlerts = wdAlertsAll
            MsgBox "File created '" & outputDoc & "'" & vbCrLf & vbCrLf & _
                "and saved in " & wdoc.Path
            wdoc.Close True
        Else
            wdoc.Close False
        End If
        wdApp.Quit
        ' xlWB.Sheets(xlWS.Name).Cells(2, 1).EntireRow.Delete Shift:=xlUp
        xlWB.Save
        GoTo ExitMacro
    
    ' -------------------
    CloseWorkbook:
        xlWB.Close False ' close the workbook without saving
        xlApp.Quit ' close the Excel application
        Set xlWB = Nothing
        Set xlApp = Nothing
    Return
    
    ' -------------------
    incorrectSheet:
        MsgBox "File '" & xlWB.Name & "' does not contain the sheet named 'Data'!" & vbCrLf & vbCrLf & _
            "Please verify and try again.", vbCritical, "A T T E N T I O N"
        GoSub CloseWorkbook
    
    ' -------------------
    ExitMacro:
    On Error GoTo 0
    End Sub
     
  8. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Rollin, you're great (y)

    I just couldn't get it why it wasn't recognize, the Public did it.
    I just tested it in the full version (5 page document) and it works great!!!
    Thanks a million.
    Like I say, you're never too old to learn.
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Glad you got it sorted! Don't forget to release your objects when you are done processing.

    Code:
    Set wdApp = Nothing
    Rollin
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Search Replace
  1. atrue502
    Replies:
    3
    Views:
    686
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1036615

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice