EASE93
11-06-2021, 12:54 PM
Hello
I have got this VBA to open a new word document and populate the document according to the values of named cells in Excel.
My question is: How can I name the new word document that is generated through the macro according to the value of a certain cell in excel?
If the value of cell B1 of sheet 1 in my excel workbook is "TestCase100", then I would like the new word file to be named "TestCase100.docx"
Sub XlNamesToWdBookmarks()
Dim objWord As Object, docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.name
Dim lRw As Long, iX As Integer
Dim Path As String
Set wb = ActiveWorkbook
Path = wb.Path & "\WordTemplate.docx"
On Error GoTo ErrorHandler
Set objWord = CreateObject("Word.Application")
lRw = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
For iX = 6 To lRw
Sheets("PZN").Range("D3").Value = iX - 5
Set docWord = objWord.Documents.Add("C:\WordTemplate.docx")
For Each xlName In wb.Names
If docWord.Bookmarks.Exists(xlName.name) Then
docWord.Bookmarks(xlName.name).Range.Text = Range(xlName.Value)
End If
Next xlName
With objWord
.Visible = True
.ActiveWindow.WindowState = 0
.Activate
End With
Next iX
ErrorExit:
Set objWord = Nothing
Exit Sub
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; There is a problem"
If Not objWord Is Nothing Then
objWord.Quit False
End If
Resume ErrorExit
End If
End Sub
Thank you very much!
I have got this VBA to open a new word document and populate the document according to the values of named cells in Excel.
My question is: How can I name the new word document that is generated through the macro according to the value of a certain cell in excel?
If the value of cell B1 of sheet 1 in my excel workbook is "TestCase100", then I would like the new word file to be named "TestCase100.docx"
Sub XlNamesToWdBookmarks()
Dim objWord As Object, docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.name
Dim lRw As Long, iX As Integer
Dim Path As String
Set wb = ActiveWorkbook
Path = wb.Path & "\WordTemplate.docx"
On Error GoTo ErrorHandler
Set objWord = CreateObject("Word.Application")
lRw = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
For iX = 6 To lRw
Sheets("PZN").Range("D3").Value = iX - 5
Set docWord = objWord.Documents.Add("C:\WordTemplate.docx")
For Each xlName In wb.Names
If docWord.Bookmarks.Exists(xlName.name) Then
docWord.Bookmarks(xlName.name).Range.Text = Range(xlName.Value)
End If
Next xlName
With objWord
.Visible = True
.ActiveWindow.WindowState = 0
.Activate
End With
Next iX
ErrorExit:
Set objWord = Nothing
Exit Sub
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; There is a problem"
If Not objWord Is Nothing Then
objWord.Quit False
End If
Resume ErrorExit
End If
End Sub
Thank you very much!