Replace some of the text in word (.docx)

[espenskeie]

Hi

I have a VBA that opens a word document where I have a standard text, and some of the words are ment to change depending on who customer we have in mind.

It seems a bit unstable, sometimes it takes half of the words, if I add a sign like a comma after the word it seems to catch them better and manage to replace them.

Right now it doesn't change any of the words....

If anyone has any experience with this I would appreciate ideas and suggestions to the code. I think it might be a slow code that I have written.

This is is:

Option Explicit
 'the document
Dim Inv_doc As Object
 'the application
Dim WD As Object, which_document As Object
Dim FName As String
Dim DesktopB As String
    
Sub AutoNameEdit()
     'where is the template located
     
    FName = ActiveWorkbook.Sheets("Ark3").Range("A2").Value
    DesktopB = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
    
    Dim which_document As String
    which_document = DesktopB & "\SalesTools\Intro.docx"
    
    'Set which_document = DesktopB & "\Intro.docx" ' I cannot get the DTAddress to work here
    
         'need an instance of word
    Set WD = CreateObject("Word.Application")
    WD.Visible = True
    Set Inv_doc = WD.Documents.Open(which_document)
     '*** code to manipulate your document
     'replace the text in the document with text in cells
    Call Change_Bookmark("txtCompName", Cells(2, 1).Value)
    Call Change_Bookmark("txtCompNo", Cells(2, 2).Value)
    Call Change_Bookmark("txtStreet", Cells(2, 3).Value)
    Call Change_Bookmark("txtStreetNo", Cells(2, 4).Value)
    Call Change_Bookmark("txtPostNo", Cells(2, 5).Value)
    Call Change_Bookmark("txtStorage", Cells(2, 6).Value)
    Call Change_Bookmark("txtCompRef", Cells(2, 7).Value)
    Call Change_Bookmark("txtCity", Cells(2, 12).Value)
    Call Change_Bookmark("chkCamera", Cells(2, 13).Value)
    Call Change_Bookmark("chkGate", Cells(2, 14).Value)
    Call Change_Bookmark("chkFence", Cells(2, 15).Value)
    WD.Activate
    
    
    Inv_doc.SaveAs DesktopB & "\SalesTools\" & FName & "-" & Date & ".docx"
    Inv_doc.Close
    WD.Quit
    Set Inv_doc = Nothing
    Set WD = Nothing
End Sub
Sub Change_Bookmark(Template_value As String, New_Value As String)
    Dim oword As Object
    For Each oword In Inv_doc.Words
        If oword.Text = Template_value Then
            oword.Text = New_Value
        End If
    Next oword
    Set oword = Nothing
End Sub

Regards
Espen

 

[NdNoviceHlp]

Trial this format (adjust sheet name) ...

Call Change_Bookmark("txtCompName", _
             CStr(Sheets("Sheet1").Cells(2, 1).Value)) [\code] 
There's something punky with your sub  Change_Bookmark (which would really make a better function). You should loop your Word bookmarks using ranges and evaluate/replace their content. HTH. Dave
ps. also to speed things up... in your sub/function once you replace the text you should exit the loop and exit the sub/function
pps skip the wd.activate line of code

 

[Kenneth Hobson]

See if any of these help:

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

 

[espenskeie]

Hi Ken

Thank you for the links, I read them all and they were very useful. I have now solved the problem, I discovered that the replacement function is quite sensitive, but works fine when its tuned right.

Yet, I discovered another problem, one of the textboxes where I type my text into - it gives me an error when I run the replacement code. It says that the string is too long. I counted the words, 61 letters. And some lineshifts.

Do you know if I can solve this issu? I read on forums that the limit usually is 255 characters, but maybe my lineshifts are counted too?

Regards
Espen

 

[Kenneth Hobson]

What kind of Excel textbox: ActiveX, Form, userform, etc.?

 

[espenskeie]

it is a userform, and it stores the data in a cell in excel. Then I run a macro that replace the text in a word document, thats when I get the error message.