error in word VBA to automate creation of mailing labels

[dgr7]

hello,
I'm trying to automate the creation of mailing labels where the source data in an .xls worksheet.
I have the below VBA code that I created first with the macro recorder in word then edited some to put in the Directory and Filename code that I've used successfully in some Excel VBA and VB 6.0 code.
Now I'm having trouble getting the code to work. I run it and I get the error:

Run-time error '509':

This command is not available
and the code execution stops on the line:

WordBasic.MailMergePropagateLabel

Can anyone help me get past this error so the code will run successfully.

thanks in advance,
david
Dim Directory, Filename As String

Documents.Add DocumentType:=wdNewBlankDocument

Directory = "C:\My Documents\MonthEndMailingLabels\"
Filename = Dir(Directory & "*.xls")
'MsgBox Directory & Filename
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:= _
Directory & Filename, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=Directory & Filename;" & _
"Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:" & _
"Registry Path="""";Jet OLEDB:Database Password" _
, SQLStatement:="SELECT * FROM `Untitled$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
' ActiveDocument.MailMerge.OpenDataSource Name:= _
" & Directory & Filename & ", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & Directory & Filename & _
";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:" & _
"Registry Path="""";Jet OLEDB:Database Password" _
, SQLStatement:="SELECT * FROM `Untitled$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
' ActiveDocument.MailMerge.OpenDataSource Name:= _
Directory & Filename, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & Directory & Filename & _
";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:" & _
"Registry Path="""";Jet OLEDB:Database Password" _
, SQLStatement:="SELECT * FROM `Untitled$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
wdFieldAddressBlock, Text:= _
"\f ""<<_COMPANY_" & Chr(13) & ">><<_STREET1_" & Chr(13) & ">><<_STREET2_" & Chr(13) & ">><<_CITY_>>" & _
"<<, _STATE_>><< _POSTAL_>>"" \l 1033 \c 0 \e """""
WordBasic.MailMergePropagateLabel
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Selection.WholeStory
'change the font size to 9
'can I take the left 5 characters from the .xls to use in place of Jan07? left(filename,5)
'ChangeFileOpenDirectory "C:\My Documents\MonthEndMailingLabels\"
'ActiveDocument.SaveAs Filename:="TRUSTJan07Labels.doc", FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
End Sub


Edited 11-Apr-07 by geekgirlau. Reason: insert line breaks

[dgr7]

can anyone help me with this?
thanks,
david

[geekgirlau]

Cross-posted here (http://www.tek-tips.com/viewthread.cfm?qid=1331460&page=1)

[geekgirlau]

Have a look at this post (http://help.lockergnome.com/office/Macro-execution-ftopict851880.html) and see if it helps.

[fumei]

Actually, if you do a google search on this, it is cross posted in a couple of places, and the answer, as far as I can see is likely that there is no table layout. It can not propogate a layout that does not exist.

Have you tried having a table, as has been suggested?

[dgr7]

geekgirlau & fumei,
thank you for your assistance. I've read through the LockerGnome link you provided and see where nelson j did "Peter....your suggestion worked. I created the mailing lable format ahead of
time and called the saved format into the macro recording and it works as I
need it. Thanks.
J. Nelson "

however I'm not sure how you would go about creating the label format ahead of time then calling it in a macro without using the specific data file that I use to populate the label.
Could someone walk me through the steps on how you'd do this?

thanks in advance,
david

[fumei]

Huh? First of all, after all this time??? Hmmmmm.

Next:
how you would go about creating the label format ahead of time then calling it in a macro without using the specific data file that I use to populate the label
Hmmmm, format is.....format. Data is....data.

Do you think you need to know that you are going to use the word "hello" (data) BEFORE you format the paragraph it is in as bold (format)?

Do you think you need to know you are going to use the word "world" (data) BEFORE you make the table cell it is in shaded and 14 pts TimesRoman (format)?

[dgr7]

Could someone walk me through the steps on how you'd do this?

thanks in advance,
david