Access to Output into Word

alastair69

alastair69

Registered User.
Local time
Yesterday, 22:54
Joined
Dec 21, 2004
Messages
562
I have a database the i fillin for a property, want i need to do is output this into word but i am not how to do this i have looked at using document properties, but i feel there must be an easier way of out putting the information to word, does anybody have any ideas.

The version of office i am using is 2003, and have got a word template setup just got sure of the next steps.

Any ideas would be good.

Alastair
P.S. I have tryed the Cv writer in the examples section but this just returns a whole load of errors, any examples would be good.
 
Have you looked at examples about mail merge? I know there's couple on the web, maybe they can do what you want to do?
 
we have a look and see, not sure if mail merge is the right way to go but thanks for your help
 
Explain in detail what you need to do, and why you don't think mail merge is what you want to do and we can see and try to give you more insights.
 
I second Banana's opinion. It is highly possible to create a full-blown, natural Word document from Access by opening Word as an Application Object and thus "exposing" Word's internal collections, properties, and methods. But the word interface via collections is SO bloody bizarre that I have never tried that. It is all I care to do to extract data from a document, much less create one using VBA.

Using mail merge is easier because you at least set up the templates via Word and only supply data. In this case, you are supplying the formatting meta-data with Word as opposed to trying to supply both data and meta-data through Access & VBA.
 
I have found one way after further reaseach on this site using bookmarks in the word document, it seems to work, but i know i will come into trouble when i need a table that is in word to be filled in.

Is there any problems anybody can see with my approaching it using bookmarks in the word template.
 
As matter of fact, this is what I use. The thing is that you will need to make sure you have all bookmarks named uniquely so Access knows which bookmark you are talking about. Word defaults the bookmarks to a bunch of same names ("Text1", CheckBox1", whatever) which means that Access could potentially be putting different data in same bookmark. No fun.
 
Banana,

Can you use bookmark for the tables in word?
 
Yeah.

The thing you need to realize is that Word scatter more bookmarks than Zeus has scattered his wild oats to virgin maidens.

There's bookmark for mail merge. There's bookmark for form field. There's a bookmark for table. There's bookmark for God know what.

So you need to be very, very precise about what bookmark you want to use because you can't reference a form field bookmark when you want to insert text in a cell's bookmark.

With my Word document, which is given to me from my state's government, I used form field bookmarks because they designed the document as a form. Therefore, I coded something like this:

Code: 
Dim objWord As Word.Application
Dim objDocument As Word.Document
Dim frmflds As Word.FormFields
Dim frmfld as Word.FormField

Set objWord = New Word.Application
Set objDocument = objWord.Documents(Open "whatever.doc")
Set frmflds = objDocument.Formfields

For Each frmfld In frmflds
    Debug.Print frmfld.Bookmark.Name
Next frmfld

If you look at Word's help, especially the object model (shudder), you'll get an idea of how table's properties (including bookmark) is structured and code accordingly.

HTH.
 
Code: 
Dim objWord As Word.Application
Dim objDocument As Word.Document
Dim frmflds As Word.FormFields
Dim frmfld as Word.FormField

Set objWord = New Word.Application
Set objDocument = objWord.Documents(Open "whatever.doc")
Set frmflds = objDocument.Formfields

For Each frmfld In frmflds
    Debug.Print frmfld.[COLOR="Red"]Bookmark[/COLOR].Name
Next frmfld

I keep getting on error at the point marked in read can anybody help me out on why this happens please.
 
Quick update.

I have the data that is not in a table flowing though it to my word template using the bookmark in word via access, i am still having problems with the table data flow. Anybody got any ideas on how this can be done.
 
Exactly what is going wrong? What is expected?

A bit hard to help if I don't know what you want, and what isn't working.
 
The output form is attached, which will make the process a bit easier to understand. do you need a copy of the database to see it working or would screen shots be ok, if you could let be know i will sort it out.
 

Attachments

Just the code where you're automating Word; I looked at your template, and it seems like you have bookmarks where you need them... As I'm not familiar with those type of bookmarks, I'm not quite sure how you have them named.
 
As it turns out, doing TABLES in Word is easier than inserting text in paragraphs in an arbitrary position. Tables are part of the document's Tables collection, numbered from 1 to n, and in the order that tables appear in the document.

Within each Table item there are collections of Rows, again from 1 to n. Look in WORD's VBA help to see the collections. When you drill down to individual cells, they are reminiscent of Excel cells, with a section.text portion that leads to the contents of the individual Word table cell. So random access is eminently possible. Your starting point is Word --> Help --> Programming (or Collections) and then look at table collects to drill down to the detailed level. There are examples along the way. Once you get your head around the "collection" paradigm, Word tables are reasonable. It's the raw text part that makes me want to blow chow.
 
Doc Man,

I am using bookmarks within the word document, this works fine when only one line of data is required in am now at the stage of add muilt lines, just wounder how you would tackle this.
 
I wouldn't do it through Access VBA unless there were no better way to do it. But I don't know how I WOULD do it until I looked at in detail. There are others on this forum who have worked with Word and VBA more than I have.

OK, now that the disclaimer has been entered, here is a thought or two.

If you are adding text, select an insert point and start adding. But remember that sometimes you have to add a SOFT line break. If you add a HARD line break (vbCR, for instance), you create a Word PARAGRAPH marker. Check around in Word help for adding a soft line break.
 
Access Data to Word

This is code which I wrote to fill out Word template letters from an Access database.
Unfortunately, I cannot give you the rest of the data as it is confidential.

The code locates bookmarks in the Word template(s), adds text at the site of the bookmark adjusting nouns and verbs for single/multiple instances, and writes one, two or three letters as required.

Private Sub Command82_Click()
Dim objWord As Word.Application
Dim strFilePath As String
Dim strSupplier, strSupAddress1, strSupAddress2, strSupAddress3 As String
Dim strAttention, strSupFirstName As String
Dim strSpAddress1, strSpAddress2, strSpAddress3 As String
Dim strSpFirstName As String
Dim lngSupplierID, lngSponsorID As Long
Dim strCommunication, strProduct, strSponsor, strSponsor2 As String
Dim dtDated As Date, strDated As String
Dim lngARTG1, lngARTG2, lngARTG3, lngARTG4, lngARTG5, lngARTG6, lngARTG7, lngARTG8, lngARTG9 As Long
Dim strPIName1, strPIName2, strPIName3, strPIName4, strPIName5, strPIName6, strPIName7, strPIName8, strPIName9 As String
Dim strPlural, strPlural2, strPlural3, strPlural4, strPlural5, strProductSponsorID As String
Dim strSaveName As String
Dim strPINumbers As String
Dim strPICount1 As String, strPICount2 As String

If IsNull(Me.Communication) Then
fred = MsgBox("The type of communication must be entered.", vbOKOnly, "Missing Data")
Exit Sub
End If

If IsNull(Me.Dated) Then
fred = MsgBox("The Date of the communication must be entered.", vbOKOnly, "Missing Data")
Exit Sub
End If

If IsNull(Me.Product) Then
fred = MsgBox("The Product Name or a general description must be entered.", vbOKOnly, "Missing Data")
Exit Sub
End If

'place values from current record into Supplier Letter
strSupplier = Me.Supplier
strSupAddress1 = Nz(Me.Address1)
strSupAddress2 = Nz(Me.Address2)
strSupAddress3 = Nz(Me.Address3)
strAttention = Nz(Me.Attention)
strSupFirstName = Nz(Me.supFirstName)

strSpAddress1 = Nz(Me.spAddress1)
strSpAddress2 = Nz(Me.spAddress2)
strSpAddress3 = Nz(Me.spAddress3)
strSpFirstName = Nz(Me.spFirstName)

lngSupplierID = Nz(Me.SupplierID)
lngSponsorID = Nz(Me.SponsorID)
strCommunication = Nz(Me.Communication)
dtDated = Nz(Me.Dated)
strDated = CStr(Format(dtDated, "d mmmm yyyy"))
strProduct = Nz(Me.Product)
If Me.Sponsor = Me.Supplier Then
strSponsor = "your company"
Else
strSponsor = Nz(Me.Sponsor)
End If
strSponsorID = Nz(Me.SponsorID)
If strSponsorID = "" Then
strProductSponsorID = strProduct ' changed 24 July 2006
Else
strProductSponsorID = strProduct & " sponsored by " & strSponsor & " (Client ID: " & strSponsorID & ")"
End If
lngARTG1 = Nz(Me.ARTG1)
lngARTG2 = Nz(Me.ARTG2)
lngARTG3 = Nz(Me.ARTG3)
lngARTG4 = Nz(Me.ARTG4)
lngARTG5 = Nz(Me.ARTG5)
lngARTG6 = Nz(Me.ARTG6)
lngARTG7 = Nz(Me.ARTG7)
lngARTG8 = Nz(Me.ARTG8)
lngARTG9 = Nz(Me.ARTG9)
strPIName1 = Nz(Me.PIName1)
strPIName2 = Nz(Me.PIName2)
strPIName3 = Nz(Me.PIName3)
strPIName4 = Nz(Me.PIName4)
strPIName5 = Nz(Me.PIName5)
strPIName6 = Nz(Me.PIName6)
strPIName7 = Nz(Me.PIName7)
strPIName8 = Nz(Me.PIName8)
strPIName9 = Nz(Me.PIName9)
If lngARTG2 = 0 Then
strPlural = "a New Proprietary Ingredient"
strPlural2 = "ingredient is"
strPlural3 = "has"
strPlural4 = "This Proprietary Ingredient has"
strPlural4a = "This Proprietary Ingredient is"
strPlural5 = "ingredient is"
strPICount1 = "This Proprietary Ingredient has"
strPICount2 = "this Proprietary Ingredient"
Else
strPlural = "New Proprietary Ingredients"
strPlural2 = "ingredients are"
strPlural3 = "have"
strPlural4 = "These Proprietary Ingredients have"
strPlural4a = "These Proprietary Ingredients are"
strPlural5 = "ingredients are"
strPICount1 = "These Proprietary Ingredients have"
strPICount2 = "these Proprietary Ingredients"
End If

' build string of PI Numbers
strPINumbers = Me.ARTG1
If Not IsNull(Me.ARTG2) Then
strPINumbers = strPINumbers & " " & Me.ARTG2
End If
If Not IsNull(Me.ARTG3) Then
strPINumbers = strPINumbers & " " & Me.ARTG3
End If
If Not IsNull(Me.ARTG4) Then
strPINumbers = strPINumbers & " " & Me.ARTG4
End If
If Not IsNull(Me.ARTG5) Then
strPINumbers = strPINumbers & " " & Me.ARTG5
End If
If Not IsNull(Me.ARTG6) Then
strPINumbers = strPINumbers & " " & Me.ARTG6
End If
If Not IsNull(Me.ARTG7) Then
strPINumbers = strPINumbers & " " & Me.ARTG7
End If
If Not IsNull(Me.ARTG8) Then
strPINumbers = strPINumbers & " " & Me.ARTG8
End If
If Not IsNull(Me.ARTG9) Then
strPINumbers = strPINumbers & " " & Me.ARTG9
End If

'Start Microsoft Word 2003
On Error GoTo ErrorHandler

' Supplier Letter
'production path and filename
strSaveName = "S:/CO/TGA/COMP/ELFLIST/listing/Prop Ingredient Letters/PI " & strPINumbers & " Supplier " & Me.Combo74
'testing and development path and filename
'strSaveName = "D:/Projects/PILetters/PI " & strPINumbers & " Supplier " & Me.Combo74

Set objWord = GetObject(, "Word.Application")
'Set objWord = CreateObject("Word.Application") ' original
With objWord
' Make the application visible.
.Visible = True
' Open the document.
'production path and filename
'.Documents.Open ("D:\Projects\Prop Ingredient Letters\PILetters\Supplier PI Letter.doc")
'testing and development path and filename
.Documents.Open ("S:\CO\TGA\COMP\ELFLIST\listing\Prop Ingredient Letters\PILetters\Supplier PI Letter.doc")
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
End With

With objWord.Selection
.GoTo What:=wdGoToBookmark, Name:="Supplier"
.TypeText Text:=strSupplier
.GoTo What:=wdGoToBookmark, Name:="Address1"
.TypeText Text:=strSupAddress1
.GoTo What:=wdGoToBookmark, Name:="Address2"
.TypeText Text:=strSupAddress2
.GoTo What:=wdGoToBookmark, Name:="Address3"
.TypeText Text:=strSupAddress3
.GoTo What:=wdGoToBookmark, Name:="Attention"
.TypeText Text:=strAttention
.GoTo What:=wdGoToBookmark, Name:="SupplierID"
.TypeText Text:=lngSupplierID
.GoTo What:=wdGoToBookmark, Name:="Product"
.TypeText Text:=strProduct
.GoTo What:=wdGoToBookmark, Name:="Sponsor"
If Len(strSponsorID) < 2 Then
Else
strSponsor2 = "sponsored by " & strSponsor
End If

.TypeText Text:=strSponsor2
.GoTo What:=wdGoToBookmark, Name:="SponsorID"
.TypeText Text:=strSponsorID
.GoTo What:=wdGoToBookmark, Name:="SupFirstName"
.TypeText Text:=strSupFirstName
.GoTo What:=wdGoToBookmark, Name:="Communication"
.TypeText Text:=strCommunication
.GoTo What:=wdGoToBookmark, Name:="Dated"
.TypeText Text:=strDated
.GoTo What:=wdGoToBookmark, Name:="ifPlural1"
.TypeText Text:=strPlural
.GoTo What:=wdGoToBookmark, Name:="ifPlural4"
.TypeText Text:=strPlural4a
.GoTo What:=wdGoToBookmark, Name:="ProductSponsorID"
.TypeText Text:=strProductSponsorID
.GoTo What:=wdGoToBookmark, Name:="ifPlural2"
.TypeText Text:=strPlural2
.GoTo What:=wdGoToBookmark, Name:="ifPlural3"
.TypeText Text:=strPlural3
.GoTo What:=wdGoToBookmark, Name:="ARTG1"
.TypeText Text:=lngARTG1
.GoTo What:=wdGoToBookmark, Name:="PIName1"
.TypeText Text:=strPIName1

If lngARTG2 <> 0 Then ' 24 July 06 ARTGn changed to lngARTGn
.GoTo What:=wdGoToBookmark, Name:="ARTG2"
.TypeText Text:=lngARTG2
.GoTo What:=wdGoToBookmark, Name:="PIName2"
.TypeText Text:=strPIName2
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG2"
Selection.Rows.Delete
End If

If lngARTG3 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG3"
.TypeText Text:=lngARTG3
.GoTo What:=wdGoToBookmark, Name:="PIName3"
.TypeText Text:=strPIName3
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG3"
Selection.Rows.Delete
End If

If lngARTG4 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG4"
.TypeText Text:=lngARTG4
.GoTo What:=wdGoToBookmark, Name:="PIName4"
.TypeText Text:=strPIName4
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG4"
Selection.Rows.Delete
End If

If lngARTG5 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG5"
.TypeText Text:=lngARTG5
.GoTo What:=wdGoToBookmark, Name:="PIName5"
.TypeText Text:=strPIName5
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG5"
Selection.Rows.Delete
End If

If lngARTG6 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG6"
.TypeText Text:=lngARTG6
.GoTo What:=wdGoToBookmark, Name:="PIName6"
.TypeText Text:=strPIName6
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG6"
Selection.Rows.Delete
End If

If lngARTG7 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG7"
.TypeText Text:=lngARTG7
.GoTo What:=wdGoToBookmark, Name:="PIName7"
.TypeText Text:=strPIName7
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG7"
Selection.Rows.Delete
End If

If lngARTG8 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG8"
.TypeText Text:=lngARTG8
.GoTo What:=wdGoToBookmark, Name:="PIName8"
.TypeText Text:=strPIName8
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG8"
Selection.Rows.Delete
End If

If lngARTG9 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG9"
.TypeText Text:=lngARTG9
.GoTo What:=wdGoToBookmark, Name:="PIName9"
.TypeText Text:=strPIName9
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG9"
Selection.Rows.Delete
End If

.GoTo What:=wdGoToBookmark, Name:="Supplier2"
.TypeText Text:=strSupplier
.GoTo What:=wdGoToBookmark, Name:="SupplierID2"
.TypeText Text:=lngSupplierID
.GoTo What:=wdGoToBookmark, Name:="PICount1"
.TypeText Text:=strPICount1
.GoTo What:=wdGoToBookmark, Name:="PICount2"
.TypeText Text:=strPICount2
.GoTo What:=wdGoToBookmark, Name:="letterDate"
.TypeText Text:=CStr(Format(Now, "d mmmm yyyy"))

End With
ActiveDocument.SaveAs FileName:=strSaveName, FileFormat:=wdFormatDocument

'Sponsor Letter
If IsNull(Me.Sponsor) Then
If IsNull(Me.Agent) Then
Exit Sub
End If
End If
If Me.Supplier <> "" Then
'production path and filename
strSaveName = "S:/CO/TGA/COMP/ELFLIST/listing/Prop Ingredient Letters/PI " & strPINumbers & " Sponsor " & Me.Combo76
'testing and development path and filename
'strSaveName = "D:/Projects/PILetters/PI " & strPINumbers & " Sponsor " & Me.Combo76

If Me.Supplier = Me.Sponsor Then
Exit Sub
End If
If Not IsNull(Me.Sponsor) Then
With objWord
' Make the application visible.
.Visible = True
' Open the document.
'production path and filename
.Documents.Open ("S:\CO\TGA\COMP\ELFLIST\listing\Prop Ingredient Letters\PILetters\Sponsor PI Letter.doc")
'testing and development path and filename
'.Documents.Open ("D:\Projects\PILetters\Sponsor PI Letter.doc")
End With
strAttention = Nz(Me.spAttention)

With objWord.Selection
.GoTo What:=wdGoToBookmark, Name:="Sponsor"
.TypeText Text:=strSponsor
.GoTo What:=wdGoToBookmark, Name:="spAddress1"
.TypeText Text:=strSpAddress1
.GoTo What:=wdGoToBookmark, Name:="spAddress2"
.TypeText Text:=strSpAddress2
.GoTo What:=wdGoToBookmark, Name:="spAddress3"
.TypeText Text:=strSpAddress3
.GoTo What:=wdGoToBookmark, Name:="Attention"
.TypeText Text:=strAttention
.GoTo What:=wdGoToBookmark, Name:="SupplierID"
.TypeText Text:=lngSponsorID
.GoTo What:=wdGoToBookmark, Name:="Supplier"
.TypeText Text:=strSupplier
.GoTo What:=wdGoToBookmark, Name:="Product"
.TypeText Text:=strProduct
.GoTo What:=wdGoToBookmark, Name:="spFirstName"
.TypeText Text:=strSpFirstName

.GoTo What:=wdGoToBookmark, Name:="Supplier2"
.TypeText Text:=strSupplier
.GoTo What:=wdGoToBookmark, Name:="SupplierID2"
.TypeText Text:=lngSupplierID
.GoTo What:=wdGoToBookmark, Name:="ifPlural2"
.TypeText Text:=strPlural5
.GoTo What:=wdGoToBookmark, Name:="Product2"
.TypeText Text:=strProduct
.GoTo What:=wdGoToBookmark, Name:="Entered"
.TypeText Text:=strPlural4
.GoTo What:=wdGoToBookmark, Name:="ARTG1"
.TypeText Text:=lngARTG1
.GoTo What:=wdGoToBookmark, Name:="PIName1"
.TypeText Text:=strPIName1

If ARTG2 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG2"
.TypeText Text:=lngARTG2
.GoTo What:=wdGoToBookmark, Name:="PIName2"
.TypeText Text:=strPIName2
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG2"
Selection.Rows.Delete
End If

If ARTG3 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG3"
.TypeText Text:=lngARTG3
.GoTo What:=wdGoToBookmark, Name:="PIName3"
.TypeText Text:=strPIName3
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG3"
Selection.Rows.Delete
End If

If ARTG4 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG4"
.TypeText Text:=lngARTG4
.GoTo What:=wdGoToBookmark, Name:="PIName4"
.TypeText Text:=strPIName4
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG4"
Selection.Rows.Delete
End If

If ARTG5 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG5"
.TypeText Text:=lngARTG5
.GoTo What:=wdGoToBookmark, Name:="PIName5"
.TypeText Text:=strPIName5
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG5"
Selection.Rows.Delete
End If

If ARTG6 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG6"
.TypeText Text:=lngARTG6
.GoTo What:=wdGoToBookmark, Name:="PIName6"
.TypeText Text:=strPIName6
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG6"
Selection.Rows.Delete
End If

If ARTG7 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG7"
.TypeText Text:=lngARTG7
.GoTo What:=wdGoToBookmark, Name:="PIName7"
.TypeText Text:=strPIName7
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG7"
Selection.Rows.Delete
End If

If ARTG8 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG8"
.TypeText Text:=lngARTG8
.GoTo What:=wdGoToBookmark, Name:="PIName8"
.TypeText Text:=strPIName8
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG8"
Selection.Rows.Delete
End If

If ARTG9 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG9"
.TypeText Text:=lngARTG9
.GoTo What:=wdGoToBookmark, Name:="PIName9"
.TypeText Text:=strPIName9
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG9"
Selection.Rows.Delete
End If

.GoTo What:=wdGoToBookmark, Name:="Supplier3"
.TypeText Text:=strSupplier
.GoTo What:=wdGoToBookmark, Name:="SupplierID3"
.TypeText Text:=lngSupplierID
.GoTo What:=wdGoToBookmark, Name:="PICount1"
.TypeText Text:=strPICount1
.GoTo What:=wdGoToBookmark, Name:="PICount2"
.TypeText Text:=strPICount2
.GoTo What:=wdGoToBookmark, Name:="letterDate"
.TypeText Text:=CStr(Format(Now, "d mmmm yyyy"))

End With
End If
ActiveDocument.SaveAs FileName:=strSaveName, FileFormat:=wdFormatDocument

End If

'Agent Letter
If Me.txtAgent <> "" Then
strAgent = Me.txtAgent
strAgAddress1 = Me.txtAgAddress1
strAgAddress2 = Me.txtAgAddress2
strAgAddress3 = Me.txtAgAddress3
strAgAttention = Me.txtAgAttention
strAgFirstName = Me.txtAgFirstName
strAgentID = Me.Combo92
strSupplier = Me.Supplier
strSupplier2 = Me.Supplier
strSupplierID = Me.Combo74
strSponsor = Me.Sponsor
If strSponsorID = "" Then
strProductSponsorID = ""
Else
strProductSponsorID = strProduct & " sponsored by " & strSponsor & " (Client ID: " & strSponsorID & ")"
End If

'production path and filename
strSaveName = "S:/CO/TGA/COMP/ELFLIST/listing/Prop Ingredient Letters/PI " & strPINumbers & " Agent " & Me.Combo92
'testing and development path and filename
'strSaveName = "D:/Projects/PILetters/PI " & strPINumbers & " Agent " & Me.Combo92
With objWord
' Make the application visible.
.Visible = True
' Open the document.
'production path and filename
.Documents.Open ("S:\CO\TGA\COMP\ELFLIST\listing\Prop Ingredient Letters\PILetters\Agent PI Letter.doc")
'testing and development path and filename
'.Documents.Open ("D:\Projects\PILetters\Agent PI Letter.doc")
End With

With objWord.Selection
.GoTo What:=wdGoToBookmark, Name:="Agent"
.TypeText Text:=strAgent
.GoTo What:=wdGoToBookmark, Name:="agAddress1"
.TypeText Text:=strAgAddress1
.GoTo What:=wdGoToBookmark, Name:="agAddress2"
.TypeText Text:=strAgAddress2
.GoTo What:=wdGoToBookmark, Name:="agAddress3"
.TypeText Text:=strAgAddress3
.GoTo What:=wdGoToBookmark, Name:="agAttention"
.TypeText Text:=strAttention
.GoTo What:=wdGoToBookmark, Name:="AgentID"
.TypeText Text:=strAgentID
.GoTo What:=wdGoToBookmark, Name:="Supplier"
.TypeText Text:=strSupplier

.GoTo What:=wdGoToBookmark, Name:="agFirstName"
.TypeText Text:=strAgFirstName
.GoTo What:=wdGoToBookmark, Name:="Supplier2"
.TypeText Text:=strSupplier
.GoTo What:=wdGoToBookmark, Name:="SupplierID2"
.TypeText Text:=lngSupplierID
.GoTo What:=wdGoToBookmark, Name:="ifPlural2"
.TypeText Text:=strPlural2

.GoTo What:=wdGoToBookmark, Name:="ProductSponsorID"
If Len(strSponsorID) < 2 Then
Else
strSponsor2 = "sponsored by " & strSponsor
End If
.TypeText Text:=strProductSponsorID

.GoTo What:=wdGoToBookmark, Name:="ifPlural"
.TypeText Text:=strPlural4

.GoTo What:=wdGoToBookmark, Name:="ARTG1"
.TypeText Text:=lngARTG1
.GoTo What:=wdGoToBookmark, Name:="PIName1"
.TypeText Text:=strPIName1

If ARTG2 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG2"
.TypeText Text:=lngARTG2
.GoTo What:=wdGoToBookmark, Name:="PIName2"
.TypeText Text:=strPIName2
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG2"
Selection.Rows.Delete
End If

If ARTG3 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG3"
.TypeText Text:=lngARTG3
.GoTo What:=wdGoToBookmark, Name:="PIName3"
.TypeText Text:=strPIName3
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG3"
Selection.Rows.Delete
End If

If ARTG4 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG4"
.TypeText Text:=lngARTG4
.GoTo What:=wdGoToBookmark, Name:="PIName4"
.TypeText Text:=strPIName4
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG4"
Selection.Rows.Delete
End If

If ARTG5 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG5"
.TypeText Text:=lngARTG5
.GoTo What:=wdGoToBookmark, Name:="PIName5"
.TypeText Text:=strPIName5
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG5"
Selection.Rows.Delete
End If

If ARTG6 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG6"
.TypeText Text:=lngARTG6
.GoTo What:=wdGoToBookmark, Name:="PIName6"
.TypeText Text:=strPIName6
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG6"
Selection.Rows.Delete
End If

If ARTG7 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG7"
.TypeText Text:=lngARTG7
.GoTo What:=wdGoToBookmark, Name:="PIName7"
.TypeText Text:=strPIName7
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG7"
Selection.Rows.Delete
End If

If ARTG8 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG8"
.TypeText Text:=lngARTG8
.GoTo What:=wdGoToBookmark, Name:="PIName8"
.TypeText Text:=strPIName8
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG8"
Selection.Rows.Delete
End If

If ARTG9 <> 0 Then
.GoTo What:=wdGoToBookmark, Name:="ARTG9"
.TypeText Text:=lngARTG9
.GoTo What:=wdGoToBookmark, Name:="PIName9"
.TypeText Text:=strPIName9
Else
Selection.GoTo What:=wdGoToBookmark, Name:="ARTG9"
Selection.Rows.Delete
End If

.GoTo What:=wdGoToBookmark, Name:="Supplier3"
.TypeText Text:=strSupplier
.GoTo What:=wdGoToBookmark, Name:="SupplierID3"
.TypeText Text:=lngSupplierID
.GoTo What:=wdGoToBookmark, Name:="PICount1"
.TypeText Text:=strPICount1
.GoTo What:=wdGoToBookmark, Name:="PICount2"
.TypeText Text:=strPICount2
.GoTo What:=wdGoToBookmark, Name:="letterDate"
.TypeText Text:=CStr(Format(Now, "d mmmm yyyy"))

End With

ActiveDocument.SaveAs FileName:=strSaveName, FileFormat:=wdFormatDocument

End If

ErrorHandlerExit:
Set objWord = Nothing
Exit Sub
ErrorHandler:
If Err = 429 Then
'Word not running
Set objWord = CreateObject("Word.Application")
Resume Next
ElseIf Err = 462 Then
Set objWord = GetObject(, "Word.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & " Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub
Click to expand...

If you wish to combine multiple records into a single word document see http://www.access-programmers.co.uk/forums/search.php?searchid=1432523
 
bluetongue,
Following you code you supplied i have changed some of the items so i can see the data been passed to and from the data lines.

The code i am using is below, i have an erorr 13 at the point of lngPhoto_Number (Marked in red) were i know that there is more then then one record to pass to word template, i am woundering that is the problem is, if anybody has and help it would be helpful.

** UPDATE the value in the photo number field is P6080028 **
-------------------------------------------------------------------------------
Private Sub cmd_Word_Doc_Click()
'On Error GoTo Err_cmd_Word_Doc_Click

'Declare the follwing
Dim objWord As Word.Application
Dim lngSiteText, lngtxt_Area_Number, lngCommon_Area, lngEstimated_Age, lngAFull_Building As String
Dim lngNumber_Floors, lngcmb_Property_Type, lngSurvey_Details As String
Dim lngTotal_Samples, lngLab_Cert_Date, lngLab_cert_ID, lngPhoto_Number As String
Dim lngRain_Water_Goods, lngChimney_Stacks, lngGarden_Sheds, lngPorches_Canopies, lngExternal_Roof As String
Dim lngNo_Access, lngSofitts, lngCowls, lngBoundary_Fences, lngExternal_Doors, lngExternal_Path As String
Dim lngCurtilage_Garden, lngGarages_Outbuildings, lngBin_Store, lngUnder_Cloaking, lngExternal_Meter_Services As String
Dim lngSurveyors_Names, lngOther_Observations, lngNotes As String
Dim lngSurveyed_Date As Date

lngSiteText = Nz(Me.SiteText)
lngtxt_Area_Number = Nz(Me.txt_Area_Number)
lngCommon_Area = Nz(Me.Common_Area)
lngEstimated_Age = Nz(Me.Estimated_Age)
lngFull_Building = Nz(Me.Full_Building)
lngNumber_Floors = Nz(Me.Number_Floors)
lngcmb_Property_Type = Nz(Me.cmb_Property_Type)
lngSurvey_Details = Nz(Me.Survey_Details)
lngSurveyed_Date = Nz(Me.Surveyed_Date)
lngSurveyors_Names = Nz(Me.Surveyors_Names)
lngTotal_Samples = Nz(Me.Total_Samples)
lngLab_Cert_Date = Nz(Me.Lab_Cert_Date)
lngLab_cert_ID = Nz(Me.Lab_cert_ID)
lngPhoto_Number = Nz(Forms![frm_Property_Sheet_Holder]![Frm_Property_Sheet]![Form1]![Photo_Number])
lngNotes = Nz(Forms![frm_Property_Sheet_Holder]![Frm_Property_Sheet]![Form1]!Notes)
lngOther_Observations = Nz(Me.Other_Observations)
lngRain_Water_Goods = Nz(Me.Rain_Water_Goods)
lngChimney_Stacks = Nz("Me.Chimney/Stacks")
lngGarden_Sheds = Nz(Me.Garden_Sheds)
lngPorches_Canopies = Nz(Me.Porches_Canopies)
lngExternal_Roof = Nz(Me.External_Roof)
lngNo_Access = Nz(Me.No_Access)
lngSofitts = Nz(Me.Sofitts)
lngCowls = Nz(Me.Cowls)
lngBoundary_Fences = Nz(Me.Boundary_Fences)
lngExternal_Doors = Nz(Me.External_Doors)
lngExternal_Path = Nz(Me.External_Path)
lngCurtilage_Garden = Nz(Me.Curtilage_Garden)
lngGarages_Outbuildings = Nz(Me.Garages_Outbuildings)
lngBin_Store = Nz(Me.Bin_Store)
lngUnder_Cloaking = Nz("Me.Under-Cloaking")
lngExternal_Meter_Services = Nz("Me.External_Meter/Services")

'Set word as an application and make it invisible
Set objWord = CreateObject("Word.Application")
objWord.Visible = False 'True is visible

'path and name of the template your are using.
objWord.Documents.Add ("C:\Temp\Property Type External Features.dot")

'This is for the bookmark that you created in the template

'Str_Address
If lngSiteText <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Address").Select
objWord.Selection.Text = lngSiteText
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Address"
Selection.Rows.Delete
End If

'Str_Area_Number
If lngtxt_Area_Number <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Area_Number").Select
objWord.Selection.Text = lngtxt_Area_Number
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Area_Number"
Selection.Rows.Delete
End If
'Str_Common_Area
If lngCommon_Area <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Common_Area").Select
objWord.Selection.Text = lngCommon_Area
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Common_Area"
Selection.Rows.Delete
End If

'Str_Estimated_Age_of_Building
If lngEstimated_Age <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Estimated_Age_of_Building").Select
objWord.Selection.Text = lngEstimated_Age
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Estimated_Age_of_Building"
Selection.Rows.Delete
End If

'Str_Full_Building
If lngFull_Building <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Full_Building").Select
objWord.Selection.Text = lngFull_Building
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Full_Building"
Selection.Rows.Delete
End If

'Str_Number_of_Floors
If lngNumber_Floors <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Number_of_Floors").Select
objWord.Selection.Text = lngNumber_Floors
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Number_of_Floors"
Selection.Rows.Delete
End If

'Str_Property_Type
If lngcmb_Property_Type <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Property_Type").Select
objWord.Selection.Text = lngcmb_Property_Type
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Property_Type"
Selection.Rows.Delete
End If

'Str_Survey_Details
objWord.ActiveDocument.Bookmarks("Str_Survey_Details").Select
objWord.Selection.Text = lngSurvey_Details
' Else
'Selection.GoTo What:=wdGoToBookmark, Name:="Str_Survey_Details"
'Selection.Rows.Delete
'End If

'Str_Surveyed_Date
If lngSurveyed_Date <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Surveyed_Date").Select
objWord.Selection.Text = lngSurveyed_Date
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Surveyed_Date"
Selection.Rows.Delete
End If

'Str_Surveyors_Name
If lngSurveyors_Names <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Surveyors_Name").Select
objWord.Selection.Text = lngSurveyors_Names
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Surveyors_Name"
Selection.Rows.Delete
End If

'Str_Total_Number_of_Samples
If lngTotal_Samples <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Total_Number_of_Samples").Select
objWord.Selection.Text = lngTotal_Samples
' Else
'Selection.GoTo What:=wdGoToBookmark, Name:="Str_Total_Number_of_Samples"
'Selection.Rows.Delete
End If

'Str_Lab_Cert_Date
If lngLab_Cert_Date <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Lab_Cert_Date").Select
objWord.Selection.Text = lngLab_Cert_Date
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Lab_Cert_Date"
Selection.Rows.Delete
End If

'Str_Lab_Cert_ID
If lngLab_cert_ID <> 0 Then
objWord.ActiveDocument.Bookmarks("Str_Lab_Cert_ID").Select
objWord.Selection.Text = lngLab_cert_ID
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Str_Lab_Cert_ID"
Selection.Rows.Delete
End If

'tbl_Photo_No
If lngPhoto_Number <> 0 Then
objWord.ActiveDocument.Bookmarks("tbl_Photo_No").Select
objWord.Selection.Text = lngPhoto_Number
Else
Selection.GoTo What:=wdGoToBookmark, Name:="tbl_Photo_No"
Selection.Rows.Delete
End If
 
Last edited:
You must log in or register to reply here.

Similar threads

Juice
Printing html file to a specific printer
Replies
4
Views
381
arnelgp
arnelgp
murray83
Twordle: The Ultimate Word Challenge for Access Users! ( again databases, not boring )
Replies
8
Views
860
murray83
murray83
J
vba to print Word document double side
Replies
1
Views
302
Gasman
Gasman
murray83
Get a Word definition from online into Access ?
Replies
5
Views
331
GPGeorge
GPGeorge
R
Using Outlook, Word and Access to send bulk emails.
2
Replies
22
Views
1,235
Silversee
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom