espenskeie
Well-known Member
- Joined
- Mar 30, 2009
- Messages
- 636
- Office Version
- 2016
- Platform
- Windows
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:
Regards
Espen
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:
Code:
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 SubRegards
Espen
