Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
VBA code - automation of creating labels
Could someone help me out with the code that would be necessary to
automate the following request: Thanks Underwriter business developer will generate Excel file........ In the Address TAB a button is being added that will contain VBA code that will execute the following: Count number of records (Range A7.......En) Open Word Create a 30 cell table much like the one attached Insert Agency field Name Insert Agency Address Insert Agency City Insert Agency State Insert Agency Zip Font Size 8 Format:Centered Propagate fields to all Cells |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
VBA code - automation of creating labels
On Nov 20, 7:11 am, wrote:
Could someone help me out with the code that would be necessary to automate the following request: Thanks Underwriter business developer will generate Excel file........ In the Address TAB a button is being added that will contain VBA code that will execute the following: Count number of records (Range A7.......En) Open Word Create a 30 cell table much like the one attached Insert Agency field Name Insert Agency Address Insert Agency City Insert Agency State Insert Agency Zip Font Size 8 Format:Centered Propagate fields to all Cells ************************************************** ********** Just some notes and the code that I think is breaking. It works fine up to this point: When executing the code that creates the mailing labels from the VBA dev environment it works without any problems. However, when I launch the code using the button on the "Address" worksheet it fails. I believe the issue is that launching the code using the button results in Excel having the focus at a key point when Word needs it. It refuses to propagate the mailing labels when it is not the "active" application. I have tried a couple of things to work around this, including the snippet that is currently in the module (wrdApp.Activate). The error occurs at the WordBasic.MailMergePropagateLabel command. I'm hoping that you can provide the last line I need to get this out the door. Sub Merge_Click() ' Dim wrdSelection As Word.Selection ' Dim wrdMailMerge As Word.MailMerge ' Dim wrdMergeFields As Word.MailMergeFields ' Create name for temp data document tmpName = "P:\Cincinnati\Downtown\Specialty\Inland Marine\P&IM Management Report\MPG\" & _ Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_data.doc" ' Create an instance of Word, and make it visible. Set wrdApp = CreateObject("Word.Application") wrdApp.Visible = True ' Add a new document. Set wrdDoc = wrdApp.Documents.Open("P:\Cincinnati\Downtown\Spec ialty \Inland Marine\P&IM Management Report\MPG\Label_Template.doc") ' Set wrdDoc = wrdApp.Documents.Add wrdDoc.Select Set wrdSelection = wrdApp.Selection Set wrdMailMerge = wrdDoc.MailMerge ' Create the MailMerge Data file. CreateMailMergeDataFile ' Continue on with MailMerge wrdApp.Activate wrdDoc.MailMerge.MainDocumentType = wdMailingLabels wrdDoc.MailMerge.OpenDataSource Name:=tmpName, _ ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _ :="", SubType:=wdMergeSubTypeOther wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_Name""" wrdSelection.TypeParagraph wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_Address""" wrdSelection.TypeParagraph wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_City""" wrdSelection.TypeText Text:=", " wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_State""" wrdSelection.TypeText Text:=" " wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_ZIP""" WordBasic.MailMergePropagateLabel With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With ' Close the original form document. wrdDoc.Close False ' Hide Mail Merge toolbar wrdApp.CommandBars("Mail Merge").Visible = False ' Release references. Set wrdSelection = Nothing Set wrdMailMerge = Nothing Set wrdDoc = Nothing Set wrdApp = Nothing ' Clean up the temp file. Kill tmpName ' Notify user we are done. MsgBox "Mail Merge Complete.", vbMsgBoxSetForeground End Sub |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
VBA code - automation of creating labels
You have posted in the ultimate end user NG. You're much more likely to get
VBA help if you post in one of the microsoft.public.word.vba hierarachy of NGs. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA Word MVP FAQ site: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. wrote in message ... On Nov 20, 7:11 am, wrote: Could someone help me out with the code that would be necessary to automate the following request: Thanks Underwriter business developer will generate Excel file........ In the Address TAB a button is being added that will contain VBA code that will execute the following: Count number of records (Range A7.......En) Open Word Create a 30 cell table much like the one attached Insert Agency field Name Insert Agency Address Insert Agency City Insert Agency State Insert Agency Zip Font Size 8 Format:Centered Propagate fields to all Cells ************************************************** ********** Just some notes and the code that I think is breaking. It works fine up to this point: When executing the code that creates the mailing labels from the VBA dev environment it works without any problems. However, when I launch the code using the button on the "Address" worksheet it fails. I believe the issue is that launching the code using the button results in Excel having the focus at a key point when Word needs it. It refuses to propagate the mailing labels when it is not the "active" application. I have tried a couple of things to work around this, including the snippet that is currently in the module (wrdApp.Activate). The error occurs at the WordBasic.MailMergePropagateLabel command. I'm hoping that you can provide the last line I need to get this out the door. Sub Merge_Click() ' Dim wrdSelection As Word.Selection ' Dim wrdMailMerge As Word.MailMerge ' Dim wrdMergeFields As Word.MailMergeFields ' Create name for temp data document tmpName = "P:\Cincinnati\Downtown\Specialty\Inland Marine\P&IM Management Report\MPG\" & _ Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_data.doc" ' Create an instance of Word, and make it visible. Set wrdApp = CreateObject("Word.Application") wrdApp.Visible = True ' Add a new document. Set wrdDoc = wrdApp.Documents.Open("P:\Cincinnati\Downtown\Spec ialty \Inland Marine\P&IM Management Report\MPG\Label_Template.doc") ' Set wrdDoc = wrdApp.Documents.Add wrdDoc.Select Set wrdSelection = wrdApp.Selection Set wrdMailMerge = wrdDoc.MailMerge ' Create the MailMerge Data file. CreateMailMergeDataFile ' Continue on with MailMerge wrdApp.Activate wrdDoc.MailMerge.MainDocumentType = wdMailingLabels wrdDoc.MailMerge.OpenDataSource Name:=tmpName, _ ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _ :="", SubType:=wdMergeSubTypeOther wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_Name""" wrdSelection.TypeParagraph wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_Address""" wrdSelection.TypeParagraph wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_City""" wrdSelection.TypeText Text:=", " wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_State""" wrdSelection.TypeText Text:=" " wrdDoc.Fields.Add Range:=wrdSelection.Range, Type:=wdFieldMergeField _ , Text:="""Producer_ZIP""" WordBasic.MailMergePropagateLabel With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With ' Close the original form document. wrdDoc.Close False ' Hide Mail Merge toolbar wrdApp.CommandBars("Mail Merge").Visible = False ' Release references. Set wrdSelection = Nothing Set wrdMailMerge = Nothing Set wrdDoc = Nothing Set wrdApp = Nothing ' Clean up the temp file. Kill tmpName ' Notify user we are done. MsgBox "Mail Merge Complete.", vbMsgBoxSetForeground End Sub |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Supressing the ctrl-c and other keys during word automation in automation | Microsoft Word Help | |||
Word should let me out a delivery point bar code on labels! | Microsoft Word Help | |||
postal bar code on labels | Microsoft Word Help | |||
creating data source through C # code in Visual studio? | Mailmerge | |||
code for creating a new folder in current user's My Documents? | Microsoft Word Help |