Using macro, mail merge, access

[jasonrbaxter]

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-]

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?

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]

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-]

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]

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-]

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.

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