Automate Table information to Word

V

vvariety

I have a table with multiple rows of data that I want to automate to a
word doc. With in the word doc there are specific bookmarks that
based on the subline code for the record should populate with
information or leave it blank. I have tried several different ways to
try to enumerate through the records but no such luck. I have tried
creating an array for each of the codes, and but now it prints all
records in one line. following is the code that I am using --- any
help or ideas would greatly be appreciated.

Function PrtLimoQte()
On Error GoTo TemplateError
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wdObj As Word.Application
Dim strName As String
Dim liab, medpay, um, uim, umuim, pip, phy

liab = Array(611, 631)
medpay = Array(620, 660)
um = Array(621, 661)
uim = Array(622, 662)
umuim = Array(623, 663)
pip = Array(615, 635)
phy = Array(618, 638)

Set db = CurrentDb
Set rs = db.OpenRecordset("MtblQuoteInfo")

DoCmd.Hourglass True
strName = "K:\predator\templates\quoteletters\lnjquote0808.doc"

Set wdObj = GetObject(, "Word.application")
If Err.Number <> 0 Then
Set wdObj = CreateObject("word.application")
End If
wdObj.Visible = True
wdObj.Documents.Open FileName:=strName
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq1"
wdObj.Selection.TypeText rs![Tel]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq2"
wdObj.Selection.TypeText Format((rs![VFname] & " " & rs![VLname]),
vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq3"
wdObj.Selection.TypeText Format(rs![producer], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq4"
wdObj.Selection.TypeText Format(rs![produceradd], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq5"
wdObj.Selection.TypeText Format(rs![producercsz], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq6"
wdObj.Selection.TypeText rs![progdesc]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq7"
wdObj.Selection.TypeText rs![Edate]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq8"
Dim i As Long
For i = 1 To rs.RecordCount
If rs![Subline] = liab Then
wdObj.Selection.TypeText Format(rs![OccurLimit],
"currency")
ElseIf rs![Subline] <> liab Then
wdObj.Selection.TypeText ""
End If
rs.MoveNext
Next i
DoEvents
wdObj.Application.Options.PrintBackground = False
wdObj.Application.ActiveDocument.Activate
DoCmd.Hourglass False
rs.close
db.close
TemplateError:
Set wdObj = Nothing
wdObj.Quit
Exit Function
End Function
 
R

Roger Carlson

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutomatingWordFromAccess.mdb" which illustrates how I do
this. You can see it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=396

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

vvariety said:
I have a table with multiple rows of data that I want to automate to a
word doc. With in the word doc there are specific bookmarks that
based on the subline code for the record should populate with
information or leave it blank. I have tried several different ways to
try to enumerate through the records but no such luck. I have tried
creating an array for each of the codes, and but now it prints all
records in one line. following is the code that I am using --- any
help or ideas would greatly be appreciated.

Function PrtLimoQte()
On Error GoTo TemplateError
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wdObj As Word.Application
Dim strName As String
Dim liab, medpay, um, uim, umuim, pip, phy

liab = Array(611, 631)
medpay = Array(620, 660)
um = Array(621, 661)
uim = Array(622, 662)
umuim = Array(623, 663)
pip = Array(615, 635)
phy = Array(618, 638)

Set db = CurrentDb
Set rs = db.OpenRecordset("MtblQuoteInfo")

DoCmd.Hourglass True
strName = "K:\predator\templates\quoteletters\lnjquote0808.doc"

Set wdObj = GetObject(, "Word.application")
If Err.Number <> 0 Then
Set wdObj = CreateObject("word.application")
End If
wdObj.Visible = True
wdObj.Documents.Open FileName:=strName
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq1"
wdObj.Selection.TypeText rs![Tel]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq2"
wdObj.Selection.TypeText Format((rs![VFname] & " " & rs![VLname]),
vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq3"
wdObj.Selection.TypeText Format(rs![producer], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq4"
wdObj.Selection.TypeText Format(rs![produceradd], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq5"
wdObj.Selection.TypeText Format(rs![producercsz], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq6"
wdObj.Selection.TypeText rs![progdesc]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq7"
wdObj.Selection.TypeText rs![Edate]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq8"
Dim i As Long
For i = 1 To rs.RecordCount
If rs![Subline] = liab Then
wdObj.Selection.TypeText Format(rs![OccurLimit],
"currency")
ElseIf rs![Subline] <> liab Then
wdObj.Selection.TypeText ""
End If
rs.MoveNext
Next i
DoEvents
wdObj.Application.Options.PrintBackground = False
wdObj.Application.ActiveDocument.Activate
DoCmd.Hourglass False
rs.close
db.close
TemplateError:
Set wdObj = Nothing
wdObj.Quit
Exit Function
End Function
Click to expand...
 
V

vvariety

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutomatingWordFromAccess.mdb" which illustrates how I do
this.  You can see it here:http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=396

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




I have a table with multiple rows of data that I want to automate to a
word doc.  With in the word doc there are specific bookmarks that
based on the subline code for the record should populate with
information or leave it blank.  I have tried several different ways to
try to enumerate through the records but no such luck.  I have tried
creating an array for each of the codes, and but now it prints all
records in one line.  following is the code that I am using --- any
help or ideas would greatly be appreciated.
Click to expand...
Function PrtLimoQte()
On Error GoTo TemplateError
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim wdObj As Word.Application
Dim strName As String
Dim liab, medpay, um, uim, umuim, pip, phy
Click to expand...
liab = Array(611, 631)
medpay = Array(620, 660)
um = Array(621, 661)
uim = Array(622, 662)
umuim = Array(623, 663)
pip = Array(615, 635)
phy = Array(618, 638)
Click to expand...
Set db = CurrentDb
Set rs = db.OpenRecordset("MtblQuoteInfo")
Click to expand...
DoCmd.Hourglass True
strName = "K:\predator\templates\quoteletters\lnjquote0808.doc"
Click to expand...
Set wdObj = GetObject(, "Word.application")
   If Err.Number <> 0 Then
       Set wdObj = CreateObject("word.application")
   End If
wdObj.Visible = True
wdObj.Documents.Open FileName:=strName
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq1"
   wdObj.Selection.TypeText rs![Tel]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq2"
   wdObj.Selection.TypeText Format((rs![VFname] & " " & rs![VLname]),
vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq3"
   wdObj.Selection.TypeText Format(rs![producer], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq4"
   wdObj.Selection.TypeText Format(rs![produceradd], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq5"
   wdObj.Selection.TypeText Format(rs![producercsz], vbProperCase)
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq6"
   wdObj.Selection.TypeText rs![progdesc]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq7"
   wdObj.Selection.TypeText rs![Edate]
wdObj.Selection.Goto what:=wdGoToBookmark, Name:="lq8"
   Dim i As Long
   For i = 1 To rs.RecordCount
       If rs![Subline] = liab Then
           wdObj.Selection.TypeText Format(rs![OccurLimit],
"currency")
       ElseIf rs![Subline] <> liab Then
           wdObj.Selection.TypeText ""
       End If
       rs.MoveNext
   Next i
DoEvents
wdObj.Application.Options.PrintBackground = False
wdObj.Application.ActiveDocument.Activate
DoCmd.Hourglass False
rs.close
db.close
TemplateError:
   Set wdObj = Nothing
   wdObj.Quit
   Exit Function
End Function- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Roger, with some minor tweeking to fit my need it worked
great. You've saved me a ton of time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy from Excel to bookmarks in Word 4
Pasting Charts into Word then resizing them 1
Copy And Paste From Excel To Word 5
Want to merge access with word; what is wrong with this code? 2
Using Excel VBA to control Word (open document) 1
Reading Word TextBoxes and Transferring Contents to Excel 2
Access checkbox yes/no sends or sends send batch of text to word.. 5
Insert a table into word 1

Top