Solved

VB 6.0 Merge Using Word 11 Object Library

Posted on 2009-05-05
11
659 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
  • 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now