Merging with Word Main Document from Access Form

[weigoldk]

This conversation begain under another issue. I'm moving it out to a new thread with a subject line that better fits the topic.

weigoldk
I have an Access 97 database that is past due to be converted up to Access 2000. After the conversion however, I'm getting an error. I have a button that should do the following:

Run Word and open my document
My document is a mail merge main document
It's data source is a query back in the Access database
The query prompts for a date (so the user jumps back to Access and puts in the date and clicks OK)
Then the merge executes
I print and am happy

When I try to run it post the conversion I get the following error in Word:
Word experienced an error trying to open the file.
Try these suggestions
*Check the file permissions for the document or drive.
*Make sure there is sufficient free memory and disk space.
*Open the file with the Text Recovery converter.

My macro simply says:
winword.exe c:\temp\ccmerge5.doc

The document will end up being on a shared drive, but for the time being, I have it on my C drive, so it really isn't a permissions issue (I verified this by simply opening the file in Word).

My Access 97 database simply had an On Click event for the button that then did a RunApp command. In searching around for help on this forum, I found one piece of advice that says to use a Macro. I tried a Macro, but I end up with the same results.

If I have to re-think my way of getting to Word, I'll be happy to do that (Word is needed for the advanced formatting features), but please be gentle with me I've very new to scripting.




Remou (TechnicalUser) 2 Nov 05 13:57
I tried this in a macro:
winword.exe "C:\TestDirName\Doc1.doc"
And it worked. Could it be as simple as adding quotes? If not, perhaps try building a sample document, and see if that works.

You might like to look at this:
Automate Word Mail Merge From Access
FAQ705-3237



weigoldk (MIS) 2 Nov 05 14:04
Thanks Remou,

I also was able to get the macro to work if I opened a word document, except when the document was a mail merge main document that looped back to the Access db's query to get the data.

Thanks also for the link. I'll study that as soon as I get a chance.

Remou (TechnicalUser) 2 Nov 05 14:48
I tested with a merge document that looped back to ask for a date. I am using Access 2000.



weigoldk (MIS) 2 Nov 05 15:14
Remou,

How did you get it to connect to the query? When I try to set my datasource, all I see are tables.



Remou (TechnicalUser) 2 Nov 05 16:35
The second tab on the Open Data Source form offered me a choice of queries, however, I seem to recall something about install. I will see if I can remember . I have some Access code that will do this too, if you want.



weigoldk (MIS) 2 Nov 05 16:37
Remou, that would be great.



Remou (TechnicalUser) 2 Nov 05 17:13
Well, this is a long and complicated way, but hopefully you will end up with what you want. First, remove the date bit from your query, then change MMerge below to the name of your query. After you have run the code, you can save your document, add the date bit back (though a form for the date might be better) and then use the Macro above to open. I have tried all this, so if it does not work, it will be back to trying to remember if there was an install thingy.
CODE
Public Function OpenWordDocs()
'References: Microsoft Word 9.0 Object Library
Dim strDBmergeSource
Dim objWord As Word.Application
Dim wrdMerge As Word.MailMerge

Set objWord = CreateObject("Word.Application")
objWord.Documents.Add

Set wrdMerge = objWord.ActiveDocument.MailMerge
strDBmergeSource = CurrentProject.FullName

objWord.Application.Visible = True
objWord.Application.DisplayAlerts = wdAlertsNone

With wrdMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=strDBmergeSource, LinkToSource:=True, _
AddToRecentFiles:=False, Connection:="QUERY MMerge", SQLStatement:= _
"SELECT * FROM [MMerge]", SQLStatement1:=""
End With

objWord.Application.DisplayAlerts = wdAlertsAll
Set objWord = Nothing
Set wrdMerge = Nothing
End Function



weigoldk (MIS) 3 Nov 05 7:55
Remou, you are more than kind. I will try the code ASAP.

Yesterday, while I was out searching the web, I found this, but it did not help:

***check the references to make sure you have referenced the Word Object Library

***Open the module, and select tools, then references. Scroll down to the Word Object library and place a check box beside it.



weigoldk (MIS) 3 Nov 05 15:53
Remou or any "script" writer.

Where do I insert the code. I thought it sould go within my "onclic" event for mu button, but I'm getting an error there (End Sub expected).

Here's what I did:

Private Sub Print_CCW_License_Click()


Public Function OpenWordDocs()
'References: Microsoft Word 9.0 Object Library
Dim strDBmergeSource
Dim objWord As Word.Application
Dim wrdMerge As Word.MailMerge

Set objWord = CreateObject("Word.Application")
objWord.Documents.Add

Set wrdMerge = objWord.ActiveDocument.MailMerge
strDBmergeSource = CurrentProject.FullName

objWord.Application.Visible = True
objWord.Application.DisplayAlerts = wdAlertsNone

With wrdMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=strDBmergeSource, LinkToSource:=True, _
AddToRecentFiles:=False, Connection:="QUERY Gun Board Meeting Date Query", SQLStatement:= _
"SELECT * FROM [Gun Board Meeting Date Query]", SQLStatement1:=""
End With

objWord.Application.DisplayAlerts = wdAlertsAll
Set objWord = Nothing
Set wrdMerge = Nothing
End Function
End Function

End Sub



Remou (TechnicalUser) 3 Nov 05 16:06
The way you would do it if it was a finished function is to put the code snippet in a module or else put it underneath your click event. Then call it in the click event:
Private Sub Print_CCW_License_Click()
Call OpenWordDocs()
End Sub

However, it is not a finished piece of code, it is only a (hopeful) way round the fact that your mailmerge is not showing queries as an option. So what you need to do is to paste the snippet of code into a module, follow the steps above, and then choose run (!) from the menu on the code window. When you save, don't give the module the same name as the code (that is, don't call the module OpenWordDocs). Also, it may not work.



weigoldk (MIS) 3 Nov 05 16:11
Remou,

Would you mind if I moved this to a new thread? It really doesn't fit under my original title and if I ever get this figured out, I'll want everyone to join the party!



Remou (TechnicalUser) 3 Nov 05 16:34
Not at all, you are quite right. In the meantime, here is some code that will create a mailmerge from scratch:
CODE
Public Function OpenWordDocs()
'References: Microsoft Word 9.0 Object Library
Dim strDBmergeSource
Dim objWord As Word.Application
Dim wrdMerge As Word.MailMerge
Dim strSQL As String
Dim strDate

'Get the date for this run using an InputBox
strDate = InputBox("Please enter date. ", , Date)

'Build a suitable SQL string
strSQL = "SELECT * FROM [Mail Merge] Where Updated <=#" & Format(strDate, "mm/dd/yyyy") & "#"
'Debug.Print strSQL

'Select a template
strTemplate = "C:\Title.dot"

'Create an instance of Word
Set objWord = CreateObject("Word.Application")
'And show it
objWord.Application.Visible = True
objWord.Application.DisplayAlerts = wdAlertsNone

'Check if the template exists, if it does, use it
'If not, just open a new document
If Len(Dir(strTemplate)) <> 0 Then
objWord.Documents.Add Template:=strTemplate
Else
objWord.Documents.Add
End If

'Mailmerge
Set wrdMerge = objWord.ActiveDocument.MailMerge
'Current database name and path
strDBmergeSource = CurrentProject.FullName

'With mailmerge
With wrdMerge
'If the template is not used, set up form letters
If Len(Dir(strTemplate)) = 0 Then
.MainDocumentType = wdFormLetters
End If
'Add all the linking stuff
.OpenDataSource Name:=strDBmergeSource, LinkToSource:=True, _
AddToRecentFiles:=False, Connection:="QUERY MailMerge", SQLStatement:= _
strSQL, SQLStatement1:=""
End With

'Release everything leaving the Mailmerge to the user.
objWord.Application.DisplayAlerts = wdAlertsAll
Set objWord = Nothing
Set wrdMerge = Nothing
End Function

weigoldk (MIS) 7 Nov 05 9:30
I worked on this just a bit over the weekend. I can't seem to get past this line and error.

Dim objWord As Word.Application

Error: Compile Error
User defined type not defined.

 

[Remou]

weigoldk
Did you notice:

'References: Microsoft Word 9.0 Object Library

You had mentioned this refrence in your post, so I thought you had already included it.

 

[weigoldk]

I noticed it, but I don't have that available as an option.

I have Microsoft Office 11.0 Object Library choosen--and what hoping it was backwards compatible.

Do I need to download/install 9.0 Object Library?

 

[Remou]

No, I don't think so. How abou if you change the line to:
Dim objWord As Object

 

[weigoldk]

With

Dim objWord as Object

I get


"The expression On Click you entered as the event property setting produced the folloiwng error: Invalid ouside procedure.

 

[Remou]

Can you post the code you are using? Invalid outside procedure means (according to the MSDN library) that you have a statement outside the (Public / Private) Function or Sub statement. However, a Dim statement would not normally produce this error, so maybe it is something that can be easily spotted in your code, either by me or someone else who reads this post.

 

[weigoldk]

I stripped it down to:

Private Sub cmdPrtCCWWord_Click()
Call OpenWordDoc
End Sub


Public Function OpenWordDoc()
Dim objWord As Word.Application
End Function

 

[Remou]

I notice that you have changed back to
[tt]Dim objWord As Word.Application[/tt]
So what error message are you getting now?
[tt]Error: Compile Error
User defined type not defined.[/tt]
Or
[tt]"The expression On Click you entered as the event property setting produced the folloiwng error: Invalid ouside procedure."[/tt]

This post has a very similar problem:
File opening
thread707-869266

 

[weigoldk]

sorry,
I pasted the wrong text

Private Sub cmdPrtCCWWord_Click()
Call OpenWordDoc
End Sub

Public Function OpenWordDoc()
Dim objWord As Object
End Function

As of right now, I'm not getting an error....unfortunately I didn't save the script from earlier to compare.

I'll proceed using the Dim objWord As Object and let you know how it goes.

 

[weigoldk]

I don't know if this is related, but I followed the steps in

http://support.microsoft.com/kb/885832

(eventhough I'm at service pack 2)

(article title: You are prompted to locate the data source when you open the main document of a mail merge in Word 2003)

I did this because I was running into that when I tried alternate methods for running a word mail merge from access.

 

[weigoldk]

I'm adding back in lines of code bit by bit. I didn't make it too far

At
Dim wrdMerge As Word.MailMerge
I get Error: Compile Error
User defined type not defined.

 

[Remou]

Um, can you double check that you have a reference to the Word Library, just to be sure? Meanwhile, I'll try something else.

 

[Remou]

You can also try changing
Dim wrdMerge As Word.MailMerge
To
Dim wrdMerge As Object
and see how far that goes.

 

[weigoldk]

Remou,

I changed all of the word.objects to just dim xxx as object, but then it errors at:

Set objWord = CreateObject("Word.Application")

I worked on it at home last night and don't recall the exact error, but it was something about not recognizing the word.application type. For some reason my Access is just not understanding the commands to let me work with Word.

I went back through my notes, etc. and ended up finding a solution to my original problem of using the run command.

I had earlier printed microsoft article 888697...You may receive the "Word was unable to open the data source" error message when you try to mail merge to Word by using an Access parameter query in Microsoft Access

I've paraphrased the Microsoft article here.
The key is in Word
1. Open the main merge document, but don't try to connect to data--just yet
2. On the Tools menu, click Options.
3. On the General tab, click to select the Confirm conversion at Open check box, and then click OK.
Note This lets you select the appropriate mail merge method.
4. Start the Mail Merge Helper (Tools > Mail Merge)
5. Use current document as the form
6. In the Mail Merge pane, click Next Select recipients.
7. In the Mail Merge pane, click Browse under Use an existing list.
10. In the Select Data Source dialog box, click your Access database in the Look in box, and then click Open.
11. In the Confirm Data Source dialog box, click MS Access Databases via DDE (*.mdb, *.mde), and then click OK.
12. Finish the Merge--don't save results (unless you want to)
13. DO save main document changes.
14. Go back to Access and try again from the form.

I received a stubborn gene from my mother's side and a persitent gene from my father's side. I hope to come back to this someday soon and get the script to work.

Thanks tons and tons for your help.

 

[Remou]

Good genes, good genes.