Excel data to Word Mail Merge via VBA

[tca_VB]

I have an excel file that is filling multiple needs. One of the needs is for a list in excel (column A) to create labels (Avery Business Card #8371 10 per page). I'd like to hook a macro to a button to run and produce from excel. I've searched the forum and haven't found what seems to match. Any suggestions.

Excel Sheet:
Tasks (header row)
draw an object
place on paper
review information
research answers

Label Output
draw an object place on paper
review information research answers

thanks for some hints or insight!

[tca_VB]

More info...

I have tried the following code, but it locks up and says that Excel is waiting to create OLE object. Can someone take a look and offer a suggestion?

I have created BusCard8371.doc to have formatting and data fields set for mail merge.

Thanks,



Sub MailMergeLabels()
Dim appWD As Object
Application.ScreenUpdating = False
Application.StatusBar = "Starting Label Creation"
If appWD Is Nothing Then
Set appWD = CreateObject("Word.Application")
End If
appWD.Documents.Open Filename:=("C:\Folder\BusCard8371.doc")
With appWD.ActiveDocument.MailMerge
.OpenDataSource Name:=("C:\Folder\Master_Sheet.xls")
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute Pause:=False
End With
Application.StatusBar = "Now Creating Document"
ActiveDocument.SaveAs ("C:\Folder\NameLabel.doc")
ActiveDocument.Close
appWD.Documents("C:\Folder\BusCard8371.doc").Close savechanges:=False
appWD.Quit
Set appWD = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

[Kenneth Hobs]

If you still have problems, make a simile xls and doc to post here.

If you record the mailmerge in MSWord, that might give you a clue.

A thread where MSWord object was created is shown in http://vbaexpress.com/forum/showthread.php?p=185718

This MSWord example code might give you some other ideas. Notice that I passed some parameters on open and turned off error messages before that. I later turned them back on. There are several modifications that would need to be added such as the MSWord object. Also notice that if you use late binding (GetObject or CreateObject) MSWord constants will not resolve. You can use the numerical equivalents for late binding. In MSWord's VBA Immediate window, enter the variable and with a prefixed question mark to see the value. e.g. ?wdaltertsnone

Sub MergeRun(frmFile As String, datFile As String, _
Optional bClose As Boolean = True, Optional bPrint As Boolean = True, _
Optional iNoCopies As Integer = 1)
If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
'On Error GoTo endnow
Application.DisplayAlerts = wdAlertsNone
'Open form file and associate data file
Documents.Open frmFile, False, True, False
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource name:=datFile, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
'Merge to a new document
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
If bPrint = True Then
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=iNoCopies, Pages:="", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
End If
If bClose = True Then
ActiveDocument.Close False
ActiveDocument.Close False
End If
endnow:
Application.DisplayAlerts = wdAlertsAll
End Sub