MERGE ACCESS TO WORD 2

[DIRENE]

I have see this Public Function CreateWordLetter a number of time and it seem to be really simple to use. But I can not get it to work maybe someone can help me I have the function name createWordletter I have a form with a command button it that on click I have:

Private Sub Command45_Click()
CreateWordLetter "C:\Documents and Settings\Irene\Desktop\dear"

End Sub

The problem is that when I click the button I get a compile error message: expected variable or procedure, not module.

Can someone tell me want I am doing wrong. Thank you Irene

 

[cghoga]

CreateWordLetter is not an actual function in Access.
You need to create a function called CreateWordLetter
and within that function enter the code that will merge access to word. Then call that function from your code.

Something like this.

Public Function CreateWordLetter()

DoCmd.TransferText.....etc.

End Function

Then, in the On Click event or any other event you want to use this code you would 'call' the function by simply entering the name of the function CreateWordLetter like you did in your post.

Hope this helsp you.

 

[DIRENE]

Thank you for ansrew me, I have not be working with my database latley. Now the cope for CreateWordLetter() is stopping at:

.ActiveDocument.Bookmarks.Add Name:="bookmark name",

any ideal how to fix this.

Thank You,

Irene

 

[MakeItSo]

You must replace "bookmark name" with the name you want to assign to the bookmark.
Background: You have to tell Word where on the page to put which data from Access.
So you have bookmarks e.g named
"FirstName", "LastName", "ZipCode" and so on.
It is very similar to a MailMerge.
(Actually it is a MailMerge?)

Anyway, I guess this code line is whithin a loop, isn't it?
I also guess, there's a recordset opened and looped through in that loop. If so, you need to fill in field names.

Familiar with this?

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[DIRENE]

Here is the code behind the function and when I change the "bookmark Name" to "firstname" I'm getting a complie error: syntax error


Public Function CreateWordLetter(strDocPath As String)

'if no path is passed to function, exit
If IsNull(strDocPath) Or strDocPath = "" Then
Exit Function
End If

Dim dbs As Databases
Dim objWord As Object
Dim PrintResponse
Set dbs = CurrentDb

'create reference to Word Object

Set objWord = CreateObject("Word.Application&quot

'Word Object is created

With objWord
.Visible = True
.Documents.Open (strDocPath)
'move to each bookmark, and insert correct text.
.ActiveDocument.Bookmarks("bookmark name&quot.Select
.Selection.Text = (CStr(Forms![frmTestWord]![FIELD NAME]))
.ActiveDocument.Bookmarks.Add Name:="FIRSTNAME",
Range = Selection.Range

** continue the ActiveDocument and Selection statements for each bookmark that you have on the Word Document **

End With

'release all objects

Set dbs = Nothing

End Function


Thanks for the help
IRene

 

[MakeItSo]

That's what I meant. ;-)
Take a look at the bold text in this code:

With objWord
.Visible = True
.Documents.Open (strDocPath)
'move to each bookmark, and insert correct text.
.ActiveDocument.Bookmarks("bookmark name&quot.Select
.Selection.Text = (CStr(Forms![frmTestWord]![FIELD NAME]))
.ActiveDocument.Bookmarks.Add Name:="FIRSTNAME",

Range = Selection.Range

These are the crucial lines. First line is comment for the second which moves to the already existing bookmark in the Word doc which shall obtain the text.
Next line assigns the contents of the control named "Field Name" in Form "frmTestWord" to the bookmark on the Word-Doc

** continue the ActiveDocument and Selection statements for each bookmark that you have on the Word Document **

This means that you should put the entire block in a loop which cycles through all bookmarks you have on your Word doc.

So what you need to do is: Create a (incomplete) Word doc that could look like this:
[blue]
Dear [],
Thank you for ordering our product [].
Price each: []
Amount ordered: []
Price total: []
[/blue]

Instead of the [], insert a bookmark (Word: Insert - bookmark).
Give it a meaningful name (e.g. "Customer" for the first one, then "Product", "Each",...)

These are the bookmark names you have to go through in the Access module.

Hope I've been understandable.


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[ckennerdale]

I keep on getting errors with this code as well.

With this section

With objWord
.Visible = True
.Documents.Open (strDocPath)
'move to each bookmark, and insert correct text.
.ActiveDocument.Bookmarks("ID&quot.Select
.Selection.Text = (CStr(Forms![Single Job View]![ID]))
.ActiveDocument.Bookmarks.Add Name:="ID",

Range = Selection.Range


I have a field called ID with I would like to map to a bookmark called ID which I have creayed.

I get Syntax errors on the last line

.ActiveDocument.Bookmarks.Add Name:="ID",


Any ideas?

Thanks in advance

Caspar Kennerdale

 

[MakeItSo]

Sorry, should have seen this beforehand:
Remove that line:
.ActiveDocument.Bookmarks.Add Name:="ID"
- I have no idea why the original poster wanted to add a bookmark that already exists...

 

[ckennerdale]

Perfect

I had to comment out

Range = Selection.Range

as well.

Out of interest do you know if there is a way to automatically save the word document on a specified folder.

Potentially I will be creating a word report for each record and emailing it over to the client. I would like to save these files in a foldername that has been created in relation to the clients ID. I can automatically generate emails to the client adn send an attachment. If I could automatically save this word document to the folder (and avoid user error) then when I send the email it will attach the word document just created.

I have everything sussed (I think) except the saving of this document.

Cheers

Caspar Kennerdale

 

[MakeItSo]

That's easy:

Dim stdPath
e.g.
stdPath="C:\Customers\"

At the line when the Doc is finished, put
[blue]
objWord.ActiveDocument.SaveAs stdPath & _
Forms![Single Job View]![ID] & "\report.rtf", _
FileFormat:=wdFormatRTF
[/blue]



Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[ckennerdale]

Perfect

Caspar Kennerdale

 

[DIRENE]

I'm still not getting it sorry, this is what I got now

With objWord
.Visible = True
.Documents.Open (strDocPath)
'move to each bookmark, and insert correct text.
.ActiveDocument.Bookmarks("FIRSTNAME&quot.Select
.Selection.Text = (CStr(Forms![frmTestWord]![FirstName]))
.ActiveDocument.Bookmarks("LASTNAME&quot.Select
.Selection.Text = (CStr(Forms![frmTestWord]![LastName]))

Range = Selection.Range
** continue the ActiveDocument and Selection statements for each bookmark that you have on the Word Document **

if I' leave in the stuff between the ** ** I get a syntax error. If I take it out I get a type mismatch error.

On click of my button I have

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click


CreateWordLetter "C:\Documents and Settings\Irene\Desktop\DEAR"



Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

and in my word doc I have

dear {"firstname"} {"lastname"}

Thanks for your help

Irene

 

[MakeItSo]

Looks good so far.
The stuff between ** ** is a comment and should be marked as such:
Just put a single quote at the beginning of the two lines, so it looks like this:
'** continue the ActiveDocument and Selection statements for each bookmark that you have on the Word
'Document **

This will make those lines appear green - i.e. as a comment.

You ought to get yourself a book on VB to get familiar with these things. ;-)

 

[DIRENE]

Do you know a name of a VB book that is easy to read, I'm now get a type mismatch when clicking on the button

 

[MakeItSo]

Depends on it. Which language do you need? English or different?

 

[MakeItSo]

Are sure, you commented out both lines with a single quote?

There are loads of VBA books, some for Excel, some for Access... You gotta be comfortable with its style, so it's best if you just take a quick look at some.
And if you get the feeling you feel good with the language of the author - just go ahead.
It'll be enough to get a feeling for vba - the fine things you only learn by trial and error - or Tek-Tips anyway-

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[ckennerdale]

Sorry if thgis is a basic question

I have everything working fine with the word export.

However Several Fields in my access form 'look up' another database - which contains two fieled - ID (Primary Field) and Officer.

It is bound by the ID field so that if the officer changes, or there is a spelling mistake or whatever the the right option is still attached. In my form I have set the column width to 0cm so that the ID isnt visible - however the ID value is the value that translates over to my word doucment, not the Officer Value.

How can I worlk around this?

Many Thanks

Caspar Kennerdale

 

[DIRENE]

Yes, the line is in green now. Can the type mismatch error be with wants behind my button:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click


CreateWordLetter "C:\Documents and settings\Irene\Desktop\DEAR"



Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

and in my word doc I have

dear {"firstname"} {"lastname"}

Thanks for your help

Irene

 

[ckennerdale]

DIRENE

This worked for me - I have a field called ID and a bookmark in my word document called ID

Public Function CreateWordLetterFunction(strDocPath As String)

'function returns nothing, but I created this as a
'function so all those macro users out there could
'use it also.
'if no path is passed to function, exit - no further
'need to do anything

If IsNull(strDocPath) Or strDocPath = "" Then
Exit Function
End If

Dim dbs As Database
Dim objWord As Object
Dim PrintResponse
Set dbs = CurrentDb

'create reference to Word Object

Set objWord = CreateObject("Word.Application&quot

'Word Object is created - now let's fill it with data.

With objWord
.Visible = True
.Documents.Open (strDocPath)

.ActiveDocument.Bookmarks("ID&quot.Select
.Selection.Text = (CStr(Forms![Single Job View]![ID]))



End With


End Function

Caspar Kennerdale

 

[MakeItSo]

Irene
Your header is fine. But I have just seen something:
[blue]
Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub
[/blue]
You have two jump marks called Err_Command6_Click.
This is not good. Merge the two to one.

Besides that, I don't have the foggiest idea why you get that error. I can only assume a typo somewhere. Perhaps if you post the entire code, we'll find it - it must be something really simple...