Hi.
2 years ago we had a spreadsheet written by a former employee to process some template documents based on the content of an Excel sheet.
The word document templates have the text "Column A", "Column B" etc. in them and there is some VBA code in a master spreadsheet that populates information from the spreadsheet onto the word documents.
To use this, we select a row and click a button which gets it started. It then processes all the Word files in a given folder, searches the document and replaces the "Column A" text with whatever is in Column A for the selected row.
This was all created by a former employee who is no longer able to edit the VBA code for us and we're very much in the "novice" category!
I have a basic understanding of what it's doing and how it should work but not enough to edit it with confidence or success!
The problem is this.....
We have added several Columns in Excel, right up to Column Z. However when we try putting data into Column AA it does not populate correctly.
I can replicate this manually by opening the word template and using the Replace tool to replace "Column AA" with "hello".
If I dont check the box for "find whole words only" it changes the text to "helloA" which, is exactly what the VBA code is doing.
With "Find whole words only" checked, it works perfectly and replaces "Column AA" with "hello"
How do we modify our code to make this happen? Here is the section that appears to handle the find and replace process:
Private Sub ProcessWordFiles(ByVal ServerPathStr As String, ByVal Temp As String, ByVal SelRow As String, ByVal SaveAsFileName As String)
Dim i, j As Integer
Dim str1, str2, str3, str4 As String
Dim fileName As String
Set objWord = CreateObject("Word.Application")
objWord.Visible = False
fileName = Dir(ServerPathStr)
While (fileName <> "")
If UCase(Right(fileName, 5)) = ".DOCX" Then
Label1.Caption = "Processing " & fileName
Repaint
Set objDoc = objWord.Documents.Open(ServerPathStr & fileName)
Set objSelection = objWord.Selection
For i = Temp To 1 Step -1
str1 = "Column " & ColumnLetter(CLng(i))
str2 = CStr(Trim(Worksheets("Sheet1").Cells(SelRow, i).Value))
str3 = CStr(Trim(Worksheets("Sheet1").Cells(SelRow, 3).Value))
'If str1 <> "" And str2 <> "" Then
objSelection.Find.Text = str1
objSelection.Find.Forward = True
objSelection.Find.MatchWholeWord = True
objSelection.Find.Replacement.Text = str2
objSelection.Find.Execute , , , , , , , , , , wdReplaceAll
'End If
Next i
str4 = SaveAsFileName & str3 & " " & Replace(fileName, ".docx", ".pdf")
objDoc.ExportAsFixedFormat OutputFileName:=str4, ExportFormat:=17
objDoc.SaveAs SaveAsFileName & str3 & " " & fileName
End If
fileName = Dir
Wend
objWord.Quit
End Sub
2 years ago we had a spreadsheet written by a former employee to process some template documents based on the content of an Excel sheet.
The word document templates have the text "Column A", "Column B" etc. in them and there is some VBA code in a master spreadsheet that populates information from the spreadsheet onto the word documents.
To use this, we select a row and click a button which gets it started. It then processes all the Word files in a given folder, searches the document and replaces the "Column A" text with whatever is in Column A for the selected row.
This was all created by a former employee who is no longer able to edit the VBA code for us and we're very much in the "novice" category!
I have a basic understanding of what it's doing and how it should work but not enough to edit it with confidence or success!
The problem is this.....
We have added several Columns in Excel, right up to Column Z. However when we try putting data into Column AA it does not populate correctly.
I can replicate this manually by opening the word template and using the Replace tool to replace "Column AA" with "hello".
If I dont check the box for "find whole words only" it changes the text to "helloA" which, is exactly what the VBA code is doing.
With "Find whole words only" checked, it works perfectly and replaces "Column AA" with "hello"
How do we modify our code to make this happen? Here is the section that appears to handle the find and replace process:
Private Sub ProcessWordFiles(ByVal ServerPathStr As String, ByVal Temp As String, ByVal SelRow As String, ByVal SaveAsFileName As String)
Dim i, j As Integer
Dim str1, str2, str3, str4 As String
Dim fileName As String
Set objWord = CreateObject("Word.Application")
objWord.Visible = False
fileName = Dir(ServerPathStr)
While (fileName <> "")
If UCase(Right(fileName, 5)) = ".DOCX" Then
Label1.Caption = "Processing " & fileName
Repaint
Set objDoc = objWord.Documents.Open(ServerPathStr & fileName)
Set objSelection = objWord.Selection
For i = Temp To 1 Step -1
str1 = "Column " & ColumnLetter(CLng(i))
str2 = CStr(Trim(Worksheets("Sheet1").Cells(SelRow, i).Value))
str3 = CStr(Trim(Worksheets("Sheet1").Cells(SelRow, 3).Value))
'If str1 <> "" And str2 <> "" Then
objSelection.Find.Text = str1
objSelection.Find.Forward = True
objSelection.Find.MatchWholeWord = True
objSelection.Find.Replacement.Text = str2
objSelection.Find.Execute , , , , , , , , , , wdReplaceAll
'End If
Next i
str4 = SaveAsFileName & str3 & " " & Replace(fileName, ".docx", ".pdf")
objDoc.ExportAsFixedFormat OutputFileName:=str4, ExportFormat:=17
objDoc.SaveAs SaveAsFileName & str3 & " " & fileName
End If
fileName = Dir
Wend
objWord.Quit
End Sub
