Running Macro in Excel to mailmerge and print in background

[Guest]

I have a spreadsheet with multiple columns of data that are being
concatenated into one column. I am trying to mailmerge that one column into
Word so that i can print the data onto some avery labels.

I actually have a couple of questions concerning this:

1) As of now, i have the "main" spreadsheet feeding a separate sheet because
i could not figure out how to have word only merge the one column with the
conatenated data. Can someone help me figure out how to merge only the
concatenated data?

2) I have a macro set to auto_open that prompts the user for specified
information. I would like to allow the user to click a button and have Excel
open word and run the macro i have recorded in Word. I have seen a few topics
on this subject, but none that really seemed to help me.

I have the code for both macros (in Word and Excel) if that would help at all.

Thanks in advance.

 

[Guest]

http://support.microsoft.com/?id=177760
VBA: How to Run Macros in Other Office Programs (OFF 97)

The following Sub procedure assumes that the document WordDoc.Doc contains a
macro called "WordMacro."
has this example:
Sub WDTest()
Dim WD as Object
Set WD = CreateObject("Word.Application")
WD.Documents.Open "C:\My Documents\WordDoc.Doc"
' Note that the project name and module name are required to
' path the macro correctly.
WD.Run "Project.Module1.WordMacro"
End Sub

To the best of my knowledge, you would need your concatenated values on a
separate sheet by themselve with a header row. Probably better to have that
as the first sheet in the workbook.


http://support.microsoft.com/default.aspx?scid=kb;en-us;318117
HOW TO: Use Addresses from an Excel 2002 Worksheet to Create Labels in Word
2002

 

[Guest]

OK. I see how that would work. How do i get my current Excel macro to call up
and run that code? (I am VERY new to VB and have just recently started
learning the code)

Thanks for your response.

 

[Guest]

That is sample code that excel would run to run a macro in Word.

 

[Guest]

I understand, but how should i modify it, to have my Excel document run the
code?

 

[Guest]

Sub ExistingMacro()
' existing code
wdtest
' more code
End Sub

 

[Guest]

I am still not getting it. Below is the code for both the Excel file and the
Word file. How do i incorporate the code to run your code as well?

Excel Macro:

Sub Auto_Open()
'
' Barcode Macro
' Macro recorded 1/26/2007 by Heath Davis
'

' Keyboard Shortcut: Ctrl+Shift+C

Range("I1").Select
InputMsg = "Enter the Part ID"
InputTitle = "Part ID"
PartID = InputBox(InputMsg, InputTitle)
ActiveCell.FormulaR1C1 = PartID

Range("I2").Select
InputMsg = "Enter the number of Lots"
InputTitle = "Number of Lots"
Lots = InputBox(InputMsg, InputTitle)
ActiveCell.FormulaR1C1 = Lots

Range("I3").Select
InputMsg = "Enter the number of Parts Per Lot"
InputTitle = "Number of Parts Per Lot"
Parts = InputBox(InputMsg, InputTitle)
ActiveCell.FormulaR1C1 = Parts

Range("I4").Select
InputMsg = "Enter the number at which the Lots begin"
InputTitle = "Number for Lots to begin"
NumLots = InputBox(InputMsg, InputTitle)
ActiveCell.FormulaR1C1 = NumLots

Range("I5").Select

End Sub

Word macro:

Sub label_merge()
'
' label_merge Macro
' Macro recorded 2/1/2007 by Heath Davis
'
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Barcoding\barcoding.xls" _
, 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=C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Barcoding\barcoding.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";" _
, SQLStatement:="SELECT * FROM `Labels$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""Labels"""
WordBasic.MailMergePropagateLabel
Selection.WholeStory
Selection.Font.Name = "IDAutomationHC39M"
Selection.Font.Size = 7
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
ActiveDocument.PrintPreview
End Sub

Thanks again!

 

[Guest]

Hopefully your code isn't in the workbook that Word has to use for the mail
merge. It may work, but suspect it will lock up - Excel calls word which
calls excel (excel is busy because it is calling word). Make copies of your
documents and try it.



Sub Auto_Open()
'
' Barcode Macro
' Macro recorded 1/26/2007 by Heath Davis
'

' Keyboard Shortcut: Ctrl+Shift+C

Range("I1").Select
InputMsg = "Enter the Part ID"
InputTitle = "Part ID"
PartID = InputBox(InputMsg, InputTitle)
ActiveCell.FormulaR1C1 = PartID

Range("I2").Select
InputMsg = "Enter the number of Lots"
InputTitle = "Number of Lots"
Lots = InputBox(InputMsg, InputTitle)
ActiveCell.FormulaR1C1 = Lots

Range("I3").Select
InputMsg = "Enter the number of Parts Per Lot"
InputTitle = "Number of Parts Per Lot"
Parts = InputBox(InputMsg, InputTitle)
ActiveCell.FormulaR1C1 = Parts

Range("I4").Select
InputMsg = "Enter the number at which the Lots begin"
InputTitle = "Number for Lots to begin"
NumLots = InputBox(InputMsg, InputTitle)
ActiveCell.FormulaR1C1 = NumLots

Range("I5").Select
Dim WD as Object
Set WD = CreateObject("Word.Application")
WD.Documents.Open "C:\My Documents\WordDoc.Doc"
' Note that the project name and module name are required to
' path the macro correctly.
WD.Run "Project.Module1.label_merge"


End Sub

Word macro:

Sub label_merge()
'
' label_merge Macro
' Macro recorded 2/1/2007 by Heath Davis
'
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Barcoding\barcoding.xls" _
, 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=C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Barcoding\barcoding.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";" _
, SQLStatement:="SELECT * FROM `Labels$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""Labels"""
WordBasic.MailMergePropagateLabel
Selection.WholeStory
Selection.Font.Name = "IDAutomationHC39M"
Selection.Font.Size = 7
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
ActiveDocument.PrintPreview
ActiveDocument.ClosePrintPreview
ActiveDocument.PrintPreview
End Sub