Using Code from Excel to Open and merge data with WORD

[Bob Reynolds]

Hello, I have a database in Excel 2003 that holds data for numerous merge
letters that I have in WORD 2003.
My scenario, that will repeat itself with about 20 different WORD letters in
mail merge format.
I have entered data into EXCEL and am now ready for the merge. I run the
following code: (Found on the web in a usergroup)

Sub OpenWordDoc()
Dim wdApp As Word.Application, wdDoc As Word.Document
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdDoc =
wdApp.Documents.Open("C:\LettersFormsCOJMaster\Letters01232007\L06Closurewithpayment01232007.dot")
'This path is fixed and will not vary.
wdApp.Visible = True
End Sub


My need is to find the template, open a new WORD document, go to the
ExcelDatabase.xls file and open the "Database" tab as the merge data source
for
the completed document. Once this is done, I need it to conduct the merge
and merge it to another document for saving or printing (selected by a
msgbox).

Any help would be appreciated. I don't want to have to direct any input
into the above process since it should be automatic. I have the same exact
scenario on the other letters,
but once I get one figured out the others will be fine. If there is better
code that would be fine also.

Thanks
Bob Reynolds.

 

[Doug Robbins - Word MVP]

Set wdDoc =
wdApp.Documents.Open("C:\LettersFormsCOJMaster\Letters01232007\L06Closurewithpayment01232007.dot")

should be:

Set wdDoc =
wdApp.Documents.Add("C:\LettersFormsCOJMaster\Letters01232007\L06Closurewithpayment01232007.dot")

If the data source is already attached to the template, then all you should
need to add is:

With wdDoc.MailMerge
.Destination = wdSendToPrinter
.Execute
End With

wdDoc.Close wdDoNotSaveChanges


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Bob Reynolds]

Thank you Doug, I have my datasource located in an excel spreadsheet that is
located at "C:\LettersFormsCOJMaster\DatabaseInformation.xls (with the tab
inside that contains the data "database".

I'm looking as to how I may name the database andthe path and then go
directly to it without having to pick anything out of message boxes...ONce
the code runs I would like for it to open the word document that I selected,
determine it's a mail merge document and go to the excel workbook that has
all the data and then select the specific "tab" that contains the data
without me having to intervene. What do you think?

BOB

 

[Doug Robbins - Word MVP]

If the template is linked to the data source, a document created from the
template should also be linked to the datasource.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[rjr]

Thank you Doug,

I've got limited knowledge of programming but I have this issue now and
possibly you can give me some code for it.
Once I open the mail merge document I need it to ask me for the datasource.
The data source happense to be "bobsform data.xls" and the data is held in
the tab labeled "database"

Basically I want the code to go to a specific mailmerge document in WORD,
open it up, Select the datasource and select the data tab automatically.
Can it be done
Many Thanks
BOB


Do you know of code that once the mail merge document is locaded tht would
permit me to tell it to go to

 

[Doug Robbins - Word MVP]

Code recorded while attaching a specific sheet of an Excel workbook as the
datasource of a mailmerge main document

ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\Doug Robbins\My Documents\My Data
Sources\Book1.xls" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
_
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
_
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data
Source=C:\Documents and Settings\Doug Robbins\My Documents\My Data
Sources\Book1.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Eng" _
, SQLStatement:="SELECT * FROM `Sheet2$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP