Creating mailing label file in VBA takes forever

[BillHamilton]

Hi,

I have an Excel macro that opens Word and using Word macro code creates a mailing label document from one of Word's built-in templates then writes data held in an array to it (each label is different). Here's the code (excluding writing out the data which is quite complicated and irrelevant here):


Sub WriteToWordFile()
Dim objWord As Object, Wrd As Word.Application, Doc As Word.Document
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Not objWord Is Nothing Then
objWord.Quit
Set objWord = Nothing
End If
On Error GoTo 0

Set Wrd = New Word.Application
Set Doc = Wrd.Documents.Add

' Why does the following instruction take about a minute to execute? <<<<<<<<<<<<<<<<<<
Wrd.MailingLabel.CreateNewDocument Name:="J8651", Address:="", _
AutoText:="", LaserTray:=wdPrinterManualFeed, ExtractAddress:=False, _
PrintEPostageLabel:=False, Vertical:=False

' Code to write data to each label in the Document.

Wrd.ActiveDocument.SaveAs Filename:="LabelFile"
End Sub

Pretty standard stuff.

I only run this once a year to create the labels for an annual occasion, and this year I've discovered from stepping though the code that it takes a whole minute just to execute the instruction
"Wrd.MailingLabel.CreateNewDocument Name:="J8651", Address:="", _
AutoText:="", LaserTray:=wdPrinterManualFeed, ExtractAddress:=False, _
PrintEPostageLabel:=False, Vertical:=False"
whereas previously it just floated through with hardly a pause. This code was originally created by the macro generator..

I have recently upgraded from Office 2007 to Office 2013 and this is the first time it has run under that version. Using Windows 7.

Has anyone any clues as to why this is happening and what to do about it?

Bill

[gmayor]

When corrected to remove the faffing about with opening Word application objects, the document production in Office 2013 is virtually instantaneous here.

This is the modified code (with late binding to Word) I used:



Sub WriteToWordFile()
Dim objWord As Object
Dim oDoc As Object
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err Then
Set objWord = CreateObject("Word.Application")
End If
On Error GoTo 0
Set oDoc = objWord.MailingLabel.CreateNewDocumentByID _
(LabelID:="805957077", _
Address:="", _
AutoText:="ToolsCreateLabels1", _
LaserTray:=4, _
ExtractAddress:=False, _
PrintEPostageLabel:=False, _
Vertical:=False)

' Code to write data to each label in the Document.
oDoc.SaveAs Filename:="C:\Path\LabelFile.docx"
End Sub

[BillHamilton]

Hi,

Thanks for the speedy response. I gave your code a go, but unfortunately it took even longer (70 seconds) to execute the MailingLabel instruction.

Your method is presumably doing the same thing as mine but in a different way. I hadn't heard of CreateNewDocumentByID. I'm beginning to think that there's something wrong with my Word/Office installation.

However, I ran the process manually and it worked instantaneously. I repeated it with the macro recorder on; it worked fast and it created code pretty-much as yours is (but with AutoText set to ""). I ran that code in my macro and it took 65 seconds to execute that one instruction. Baffling.

Bill