R
Rob H
The code written at the bottom of the page is by Helen Feddema. I was
wondering if it is possible to adapt this code to write to a word document
using the same technique opening word and using some values from a selected
row in excel (with corresponding column headers) to populate a custom word
document?
I know I would only have to change the references to the fields
.Item("JobTitle").Value = Nz(Me![txtTitle])
would have to say ...
.Item("JobTitle").Value = (ref to column instead of field)
How would I adapt this to say current selected row? (keeping the nz option
to eliminate errors occuring if there is some missing information)
Many Thanks
Rob
*** Code Starts ****
Private Sub cmdWordLetter_Click()
On Error GoTo ErrorHandler
Dim appWord As Word.Application
Dim docs As Word.Documents
Dim strLetter As String
Dim strTemplateDir As String
Dim prps As Object
Dim strDate As String
Set appWord = GetObject(, "Word.Application")
strDate = CStr(Date)
strTemplateDir = appWord.Options.DefaultFilePath(wdUserTemplatesPath)
strTemplateDir = strTemplateDir & "\Personal Documents\"
Debug.Print "Office templates directory: " & strTemplateDir
strLetter = strTemplateDir & "DocProps.dot"
Debug.Print "Letter: " & strLetter
Set docs = appWord.Documents
docs.Add strLetter
Set prps = appWord.ActiveDocument.CustomDocumentProperties
With prps
.Item("TodayDate").Value = strDate
.Item("Name").Value = Nz(Me![txtFirstName] & " " & Me![txtLastName])
.Item("Address").Value = Nz(Me![txtAddress])
.Item("Salutation").Value = Nz(Me![txtSalutation])
.Item("CompanyName").Value = Nz(Me![txtCompanyName])
.Item("City").Value = Nz(Me![txtCity])
.Item("StateProv").Value = Nz(Me![txtStateOrProvince])
.Item("PostalCode").Value = Nz(Me![txtPostalCode])
.Item("JobTitle").Value = Nz(Me![txtTitle])
End With
With appWord
.Visible = True
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.MoveDown Unit:=wdLine, Count:=1
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub
wondering if it is possible to adapt this code to write to a word document
using the same technique opening word and using some values from a selected
row in excel (with corresponding column headers) to populate a custom word
document?
I know I would only have to change the references to the fields
.Item("JobTitle").Value = Nz(Me![txtTitle])
would have to say ...
.Item("JobTitle").Value = (ref to column instead of field)
How would I adapt this to say current selected row? (keeping the nz option
to eliminate errors occuring if there is some missing information)
Many Thanks
Rob
*** Code Starts ****
Private Sub cmdWordLetter_Click()
On Error GoTo ErrorHandler
Dim appWord As Word.Application
Dim docs As Word.Documents
Dim strLetter As String
Dim strTemplateDir As String
Dim prps As Object
Dim strDate As String
Set appWord = GetObject(, "Word.Application")
strDate = CStr(Date)
strTemplateDir = appWord.Options.DefaultFilePath(wdUserTemplatesPath)
strTemplateDir = strTemplateDir & "\Personal Documents\"
Debug.Print "Office templates directory: " & strTemplateDir
strLetter = strTemplateDir & "DocProps.dot"
Debug.Print "Letter: " & strLetter
Set docs = appWord.Documents
docs.Add strLetter
Set prps = appWord.ActiveDocument.CustomDocumentProperties
With prps
.Item("TodayDate").Value = strDate
.Item("Name").Value = Nz(Me![txtFirstName] & " " & Me![txtLastName])
.Item("Address").Value = Nz(Me![txtAddress])
.Item("Salutation").Value = Nz(Me![txtSalutation])
.Item("CompanyName").Value = Nz(Me![txtCompanyName])
.Item("City").Value = Nz(Me![txtCity])
.Item("StateProv").Value = Nz(Me![txtStateOrProvince])
.Item("PostalCode").Value = Nz(Me![txtPostalCode])
.Item("JobTitle").Value = Nz(Me![txtTitle])
End With
With appWord
.Visible = True
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.MoveDown Unit:=wdLine, Count:=1
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub