+ Reply to Thread
Results 1 to 2 of 2

Excel VBA to find & replace in embedded Word files

  1. #1
    Registered User
    Join Date
    02-04-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    7

    Excel VBA to find & replace in embedded Word files

    Hi Guys,

    I'm bothered with an issue which I can't seem to resolve on my own.

    The code below is meant to open 15 different word templates that are included in an excel add-in (.xlam).
    The code will then find & replace some information (customising all the templates) and then save them in a specific directory.

    I have two issues in this regard:
    1) Every 2 times I run it, it crashes once. First time it works, then it crashes with Error 462: The remote server machine does not exist or is unvailable, then works again, crash, work, ..
    Note that there are also issues when another word file is already open - therefore I use specific code to kill winword.exe
    2) It does the work 15 times, and always between 6 and 7 there is a major lag.. not that big of an issue, but as i'm already posting here, any clues?

    I have included the code below; I think I have removed all the irrelevant things in order to make it as clear as possible:

    Thanks in advance,

    Code:

    close_word

    'remove .xlam from the workbook name
    Dim sWbname As String
    sWbname = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)

    Dim wrdDoc As Word.Document
    Dim wrdApp As Word.Application
    Dim WDObj As OLEObject

    Set wrdApp = CreateObject("Word.Application")
    For AantalBrieven = 1 To 15
    Set WDObj = ThisWorkbook.Worksheets(2).OLEObjects(lijstobj(AantalBrieven))
    WDObj.Activate
    WDObj.Object.Application.Visible = False
    WDObj.Object.Application.ScreenUpdating = False
    With Word.Documents("Document in " & sWbname)
    Word.Documents("Document in " & sWbname).SaveAs ("C:\circularisation\" & achternaam & "\" & lijstword(AantalBrieven))
    .Close
    End With
    Set wrdDoc = wrdApp.Documents.Open("C:\circularisation\" & achternaam & "\" & lijstword(AantalBrieven))
    wrdApp.Visible = False
    wrdApp.ScreenUpdating = False
    With wrdDoc.Content.Find
    .Text = "xcircudatex"
    .Replacement.Text = sCircu_gen
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
    End With
    With wrdDoc
    .Close ' close the document
    End With
    Set wrdDoc = Nothing
    Next AantalBrieven

    ''''' I used to work with wrdApp.Quit and Set wrdApp = Nothing, but the killing of winword.exe seemed to cause less crashes
    close_word
    Code of the close_word sub I use:

    Sub close_word()

    CloseWord ("winword.exe")

    End Sub


    Private Function CloseWord(strWord As String) 'note: strWord ="winword.exe"

    Dim objProcList As Object
    Dim objWMI As Object
    Dim objProc As Object
    Set objWMI = GetObject("winmgmts:") 'create WMI object instance
    If Not IsNull(objWMI) Then
    Set objProcList = objWMI.InstancesOf("win32_process") 'create object collection of Win32 processes
    For Each objProc In objProcList 'iterate through the enumerated Collection
    If UCase(objProc.Name) = UCase(strWord) Then objProc.Terminate (0)
    Next
    End If

    End Function

  2. #2
    Registered User
    Join Date
    02-04-2011
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Using VBA code in excel to find & replace stuff in embedded word files - word cra

    anyone? Is my question not clear - do you need more detail?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1