Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / December 2005

Tip: Looking for answers? Try searching our database.

Using macro, mail merge, access

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jasonrbaxter@gmail.com - 07 Dec 2005 21:02 GMT
I am trying to use a macro to automatically merge data and print it
out.

My data source is a Access database.  I can't bypass the screen to
select the Access database using the macros.  Is there something I need
to add to my Macro to bypass that screen.  Is there a manual way VB can
do this?

Thank you in advance.
Cindy M  -WordMVP- - 09 Dec 2005 13:25 GMT
> I am trying to use a macro to automatically merge data and print it
> out.
[quoted text clipped - 3 lines]
> to add to my Macro to bypass that screen.  Is there a manual way VB can
> do this?

Please specify the version of Word, and show us the relevant VBA code.
Also describe how you came to use this code.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)
jasonrbaxter@gmail.com - 09 Dec 2005 16:44 GMT
I am using Word 2003 and Access 2003

Here is my VBA code in Access.  This opens the file I want.

Me.Visible = False
   DoCmd.OpenQuery "Selected Record"

   Dim objWord As Object
   Set objWord = CreateObject("Word.Application")
   objWord.Visible = True
   objWord.Documents.Open
"x:\technicians\training\rca\RCA_Form_v.1.doc/mMacro1

I still have to run the Macro manually.  Once the macro runs there is a
screen that you have to select the data source.  I click "Machine Data
Source" then click MS Access Database then OK.  Then it prints out as
the macro says.

Here is the Macro VB

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/7/2005 by jasbax
'
   ActiveDocument.MailMerge.OpenDataSource Name:= _
       "C:\Documents and Settings\jasbax\My Documents\My Data
Sources\Root Cause Analysis Updated Record Selection.odc" _
       , ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
       AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
       WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
       Format:=wdOpenFormatAuto, Connection:= _
       "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS
Access Database;Extended Properties=""DSN=MS Access
Database;DBQ=X:\Technicians\Training\RCA\Root Cause
Analysis.mdb;DefaultDir=X:\Technicians\Training\RCA;DriverId=25;FIL=MS
Access;MaxBufferSiz" _
       , SQLStatement:="SELECT * FROM `Updated Record Selection`",
SQLStatement1 _
       :="", SubType:=wdMergeSubTypeOther
   ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
   ActiveDocument.PrintOut
End Sub
Cindy M  -WordMVP- - 12 Dec 2005 09:23 GMT
> I am using Word 2003 and Access 2003
>
> I still have to run the Macro manually.  Once the macro runs there is a
> screen that you have to select the data source.  I click "Machine Data
> Source" then click MS Access Database then OK.  Then it prints out as
> the macro says.

OK, first thing I'd do is to link this main merge document properly to
the data source. It looks like it currently has an incomplete link via
OLE DB. I always prefer to link using ODBC if at all feasible.

Go into Word and open this document. You can answer "No" to the SQL
question (assuming that pops up).

Activate "Confirm conversions on open" in Tools/Options/General. Use the
command you usually would to link up to the data source. Once you've
selected the *.mdb file you should get a dialog box with connection
methods. Choose ODBC.

In the next dialog box, in order to list the queries, click "Options",
then activate "Views". When you come back to the list, make sure the
correct *.mdb file is still selected, then choose your query.

You shouldn't need the macro in the word file at all. Instead, you can
start mail merge execution from the VB code.

   Dim objWord As Object
   Dim objDoc as Object
   Set objWord = CreateObject("Word.Application")
   objWord.Visible = True
   Set objDoc = objWord.Documents.Open( _
"x:\technicians\training\rca\RCA_Form_v.1.doc)

   objDoc.MailMerge.Execute
   'objDoc.Close
   ActiveDocument.PrintOut

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)
jasonrbaxter@gmail.com - 13 Dec 2005 15:53 GMT
When I try to run it, it says

Run-time error '4605':

This method or property is not available because the document is not a
mail merge main document.

When I try to debug it, it shows the line:
objDoc.MailMerge.Execute

When I open the document I always have to open the data source.  I
believe this is the reason why this is happening.  When it tries to run
the Mail Merge, it doesn't have any data source to use.

Is there a way for the Data Source to Open when I open the document
automatically?

Thank you for all your help,

Jason Baxter
Cindy M  -WordMVP- - 14 Dec 2005 13:20 GMT
> Run-time error '4605':
>  
[quoted text clipped - 8 lines]
> the Mail Merge, it doesn't have any data source to use.
>  
Yes, it would have to have a data source attached...

By default, for Word 2002 with SP and Word 2003, if a document is opened
via automation the SQL security check will remove the data source. If you
link the mail merge document, save, close and re-open to you get a
message about SQL executing, and whether Word should continue?

If this is the case, first see this KB article:
"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document - 825765
http://support.microsoft.com?kbid=825765

> Is there a way for the Data Source to Open when I open the document
> automatically?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.