How do I refernce a column as I would a field nam in access
 

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(wdUserTemplatesPat h)
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

How do I refernce a column as I would a field nam in access
 

Hi

I'm sure it's possible. But why to bother - you can do exactly all this
using Word's Mail Merge with Excel table as datasource. And you can not only
create or print out documents, but also envelopes or labels - and even send
e-mails.


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)


"Rob H" wrote in message
...
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(wdUserTemplatesPat h)
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