Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 6th, 2005, 04:52 AM
Authorized User
 
Join Date: Feb 2005
Location: \'s Heer Abtskerke, , Netherlands.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access to Word Mail Merge Automation

hello,

I have a db whit a query, now when a user is clicking on a button in a form there most be print a number of letters whit some data of the query.

I have now the following code:

Code:
Public Sub MergeToWord(strDocName As String, MyQuery As String)
Dim objApp As Object

'DoCmd.Close

'Change cursor to hourglass
DoCmd.Hourglass True

'Open Mailmerge Document
'Start Word


Set objApp = CreateObject("Word.Application")
With objApp
    .Visible = True 'Make it visible
    .Documents.Open strDocName 'Open the Mailmerge Document

    'Use the Query defined in the arguments as the datasource
     .ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:="QUERY " & MyQuery

End With

'print and close Document
With objApp
   .ActiveDocument.MailMerge.Execute Pause:=True
   .ActiveDocument.PrintOut Background:=False
   .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Avoid Saving over your template
   .Quit SaveChanges:=wdDoNotSaveChanges 'close all documents
End With


Set objApp = Nothing

ErrorHandler:
Select Case Err.Number
Case 4157
End Select

DoCmd.Hourglass False 'Cursor back to normal
the word file has all the correct merg reference and no connection.

only when i am running the code it will stop in word and ask me to select the correct table/query. How ever i give the query in the line: Connection:="QUERY " & MyQuery

I have try moste options under ActiveDocument.MailMerge.OpenDataSource but don't get it to work. Does someone now how to get round this message form word?

Richard Karelse


  #2 (permalink)  
Old June 6th, 2005, 09:52 PM
Authorized User
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to alantodd
Default

Hi Richard,

To find out what is hapening, you may like to include the following code to help debug the problem:

'Use the Query defined in the arguments as the datasource
xx = Connection:= & "QUERY " & MyQuery
msgbox xx
.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:= & xx

This should atleast allow you to confirm that the name of the query is being correctly passed to this subroutine.

Hope it helps.

Alan
  #3 (permalink)  
Old June 7th, 2005, 01:41 AM
Authorized User
 
Join Date: Feb 2005
Location: \'s Heer Abtskerke, , Netherlands.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Alan i all ready now that the query name is correct. That is not the problem. when i run the procedure i give the query In hard code. and there i have typed it right.

  #4 (permalink)  
Old June 7th, 2005, 01:42 PM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to Kevinsharrison
Default

I had that same problem when I tried to do the same thing as you but I could never found out the problem either. I still have the problem, but mine is with an excel file. Asks what sheet I want to merge with. Very annoying.

Well hope someone will answer this question. I am in the same boat as you Karel.

Kevin

  #5 (permalink)  
Old June 7th, 2005, 07:58 PM
Authorized User
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to alantodd
Default

Richard,

Could you just confirm that the code you originally typed is correct?

What I'm referring to is the line ...

.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:="QUERY " & MyQuery

Should it read ...

.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:= & "QUERY " & MyQuery

Notice the extra '&'.

Regards,

Alan
  #6 (permalink)  
Old June 8th, 2005, 01:46 AM
Authorized User
 
Join Date: Feb 2005
Location: \'s Heer Abtskerke, , Netherlands.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by alantodd
 Richard,

Could you just confirm that the code you originally typed is correct?

What I'm referring to is the line ...

.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:="QUERY " & MyQuery

Should it read ...

.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:= & "QUERY " & MyQuery

Notice the extra '&'.

Regards,

Alan
Alan when i insert the extra & i get a syntax error.
Why should you use a & if there is no other string in front of it?
The & just puts them together not more than that.
  #7 (permalink)  
Old June 9th, 2005, 04:54 AM
Authorized User
 
Join Date: Feb 2005
Location: \'s Heer Abtskerke, , Netherlands.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

All right then,
i have found the solution for my problem.
Kevinsharrison here special for you the correct code how it works:

Public Sub MergeToWord(strDocName As String, MyQuery As String)
Dim objApp As Object

'DoCmd.Close

'Change cursor to hourglass
DoCmd.Hourglass True

'Open Mailmerge Document
'Start Word


Set objApp = CreateObject("Word.Application")
With objApp
    .Visible = True 'Make it visible
    .Documents.Open strDocName 'Open the Mailmerge Document

    'Use the Query defined in the arguments as the datasource

   strConnect = "DSN=MS Access Database;DBQ=" & CurrentDb.Name & "FIL=MS Access;"
.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, _
ReadOnly:=True, LinkToSource:=True, _
          Connection:=strConnect, _
          SQLStatement:="SELECT * FROM [" & MyQuery & "]"




End With

'print and close Document
With objApp
   .ActiveDocument.MailMerge.Execute Pause:=True
   .ActiveDocument.PrintOut Background:=False
   .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Avoid Saving over your template
   .Quit SaveChanges:=wdDoNotSaveChanges 'close all documents
End With


Set objApp = Nothing

ErrorHandler:
Select Case Err.Number
Case 4157
End Select

DoCmd.Hourglass False 'Cursor back to normal

 ' stDocName = "FRMbrfwaarschuwingNL"
  ' DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub







Similar Threads
Thread Thread Starter Forum Replies Last Post
Word Mail Merge Automation using ASP.Net sbhatia ASP.NET 1.0 and 1.1 Professional 3 November 29th, 2013 07:18 AM
"Pushing" from Access to Word via Mail Merge Loralee BOOK: Access 2003 VBA Programmer's Reference 2 March 8th, 2006 10:20 PM
Access launching Word for mail merge Loralee Access 8 November 14th, 2005 04:24 PM
Access/Vba mail merge to word help alfonse Access VBA 0 July 28th, 2005 05:11 AM
Word E-Mail Merge using Access Tables Richard Lally Access 2 February 7th, 2005 04:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.