Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF w/ changing column refernce ... use an array? | Excel Discussion (Misc queries) | |||
in vba for access how do i get the day of the week for a date field? | Excel Worksheet Functions | |||
Field in Access | Excel Programming | |||
pivot field size (column field) | Excel Programming | |||
Access field order using ADO | Excel Programming |