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