?
Solved

VB 6.0 Merge Using Word 11 Object Library

Posted on 2009-05-05
11
Medium Priority
?
668 Views
Last Modified: 2013-11-25
I have the following code working EXCEPT it keeps asking me for the sheet name.  I want it to be fully automated.  It is using objects from the Microsoft Word 11.0 Object Library.

What is it doing you might ask...
Opening a word document and word app
Making the word doc a merge docment (it already has merge fields in it)
Opens the Excel spreadsheet datasource
Merges the document into a new doc
saves the new doc
closes the new doc
closes the merge doc
quits word

I would like to accomplish all of this WITHOUT prompts, but it keeps asking for the table name no matter what I have tried.
Dim wordDoc As Word.Document
        Dim wordApp As Word.Application
        Set wordDoc = GetObject("fullpathandmerge.doc", "word.document")
        Set wordApp = wordDoc.Application
        With wordApp
             .Visible = True
             wordDoc.MailMerge.MainDocumentType = wdFormLetters
             wordDoc.MailMerge.OpenDataSource "datasourcepathandfilename.xls", 
             wordDoc.MailMerge.SuppressBlankLines = True
             wordDoc.MailMerge.DataSource.FirstRecord = wdDefaultFirstRecord
             wordDoc.MailMerge.DataSource.LastRecord = wdDefaultLastRecord
             wordDoc.MailMerge.Destination = wdSendToNewDocument
             wordDoc.MailMerge.Execute False
             .ActiveDocument.SaveAs "fullpathandfile.doc", wdFormatDocument, False
             .ActiveDocument.Close False
             wordDoc.Close False
        End With
        wordApp.Quit

Open in new window

0
Comment
Question by:WEhalt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24308276
Have you tried this?
 

wordDoc.DisplayAlerts = wdAlertsNone

Open in new window

0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24308284
Sorry should be.
 

Wordapp.DisplayAlerts = wdAlertsNone

Open in new window

0
 

Author Comment

by:WEhalt
ID: 24308402
Still get the message
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24308458
Which line of code are you recieving the message on?  Can you put some breakpoints in to determine where it's occurring?
0
 

Author Comment

by:WEhalt
ID: 24308510
wordDoc.MailMerge.OpenDataSource ...
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24308613
Give this a try.
 

wordDoc.MailMerge.OpenDataSource Name:=""datasourcepathandfilename.xls", LinkToSource:=True

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24308818
If you have set the Main document up manually, and the datasource does not change, you can omit the OpenDataSource line completely.

Otherwise try something like this:


             wordDoc.MailMerge.OpenDataSource Name:="datasourcepathandfilename.xls", SQLStatement:="SELECT * FROM `Sheet1$`"

Open in new window

0
 

Author Comment

by:WEhalt
ID: 24310654
I just tried both of the above and a combination of them.  I got the same message to choose the table.

I also tried re-adding the datasource link in the Word document.  With that, I get a different message asking whether or not I want to open "SELECT * FROM sheet" even with the display Alerts set to false.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24311888
You don't say what the new message says. Perhaps it its the new security message.
If so, this Microsoft article tells you how to suppress it with a registry edit.

http://support.microsoft.com/kb/825765
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 24312037
I have now tested the code and it does run as predicted.

Without the registry patch, I get the warning message if the input document is a mail merge doc. If I remove the datasource information with the main document set-up button on the mail merge toolbar, the security message does not appear. In both cases the merge takes place.

Here is my exact code.
Private Sub Command1_Click()
    Dim wordDoc As Word.Document
    Dim wordApp As Word.Application
    
    Const strMainDoc = "C:\Documents and Settings\Graham Skan\My Documents\mmtest.doc"
    Const strDataSource = "C:\Documents and Settings\Graham Skan\My Documents\mmtest.xls"
    Set wordDoc = GetObject(strMainDoc, "word.document")
    Set wordApp = wordDoc.Application
    
    
    With wordApp
    
         .Visible = True
         wordDoc.MailMerge.MainDocumentType = wdFormLetters
         wordDoc.MailMerge.OpenDataSource Name:=strDataSource, SQLStatement:="SELECT * FROM `Sheet1$`"
         wordDoc.MailMerge.SuppressBlankLines = True
         wordDoc.MailMerge.DataSource.FirstRecord = wdDefaultFirstRecord
         wordDoc.MailMerge.DataSource.LastRecord = wdDefaultLastRecord
         wordDoc.MailMerge.Destination = wdSendToNewDocument
         wordDoc.MailMerge.Execute False
         .ActiveDocument.SaveAs "fullpathandfile.doc", wdFormatDocument, False
         .ActiveDocument.Close False
         wordDoc.Close False
    End With
    wordApp.Quit
End Sub

Open in new window

0
 

Author Comment

by:WEhalt
ID: 24313840
This was an awesome answer.  Thank you so much for taking the time to help me.

W
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question