Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macro in Excel to mailmerge and print in background
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macro in Excel to mailmerge and print in background
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...b;en-us;318117 HOW TO: Use Addresses from an Excel 2002 Worksheet to Create Labels in Word 2002 -- Regards, Tom Ogilvy "learningaccess" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macro in Excel to mailmerge and print in background
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. "Tom Ogilvy" wrote: 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...b;en-us;318117 HOW TO: Use Addresses from an Excel 2002 Worksheet to Create Labels in Word 2002 -- Regards, Tom Ogilvy "learningaccess" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macro in Excel to mailmerge and print in background
That is sample code that excel would run to run a macro in Word.
-- Regards, Tom Ogilvy "learningaccess" wrote: 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. "Tom Ogilvy" wrote: 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...b;en-us;318117 HOW TO: Use Addresses from an Excel 2002 Worksheet to Create Labels in Word 2002 -- Regards, Tom Ogilvy "learningaccess" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macro in Excel to mailmerge and print in background
I understand, but how should i modify it, to have my Excel document run the
code? "Tom Ogilvy" wrote: That is sample code that excel would run to run a macro in Word. -- Regards, Tom Ogilvy "learningaccess" wrote: 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. "Tom Ogilvy" wrote: 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...b;en-us;318117 HOW TO: Use Addresses from an Excel 2002 Worksheet to Create Labels in Word 2002 -- Regards, Tom Ogilvy "learningaccess" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macro in Excel to mailmerge and print in background
Sub ExistingMacro()
' existing code wdtest ' more code End Sub -- Regards, Tom Ogilvy "learningaccess" wrote: I understand, but how should i modify it, to have my Excel document run the code? "Tom Ogilvy" wrote: That is sample code that excel would run to run a macro in Word. -- Regards, Tom Ogilvy "learningaccess" wrote: 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. "Tom Ogilvy" wrote: 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...b;en-us;318117 HOW TO: Use Addresses from an Excel 2002 Worksheet to Create Labels in Word 2002 -- Regards, Tom Ogilvy "learningaccess" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macro in Excel to mailmerge and print in background
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\b arcoding.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="""";Us er ID=Admin;Data Source=C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop\Barcoding\b arcoding.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! "Tom Ogilvy" wrote: Sub ExistingMacro() ' existing code wdtest ' more code End Sub -- Regards, Tom Ogilvy "learningaccess" wrote: I understand, but how should i modify it, to have my Excel document run the code? "Tom Ogilvy" wrote: That is sample code that excel would run to run a macro in Word. -- Regards, Tom Ogilvy "learningaccess" wrote: 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. "Tom Ogilvy" wrote: 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...b;en-us;318117 HOW TO: Use Addresses from an Excel 2002 Worksheet to Create Labels in Word 2002 -- Regards, Tom Ogilvy "learningaccess" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Macro in Excel to mailmerge and print in background
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\b arcoding.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="""";Us er ID=Admin;Data Source=C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop\Barcoding\b arcoding.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 -- Regards, Tom Ogilvy "learningaccess" wrote: 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\b arcoding.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="""";Us er ID=Admin;Data Source=C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop\Barcoding\b arcoding.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! "Tom Ogilvy" wrote: Sub ExistingMacro() ' existing code wdtest ' more code End Sub -- Regards, Tom Ogilvy "learningaccess" wrote: I understand, but how should i modify it, to have my Excel document run the code? "Tom Ogilvy" wrote: That is sample code that excel would run to run a macro in Word. -- Regards, Tom Ogilvy "learningaccess" wrote: 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. "Tom Ogilvy" wrote: 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...b;en-us;318117 HOW TO: Use Addresses from an Excel 2002 Worksheet to Create Labels in Word 2002 -- Regards, Tom Ogilvy "learningaccess" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any way to print labels from Excel doc w/out using Word mailmerge | Excel Discussion (Misc queries) | |||
How do I print labels after I have mailmerge on excel | Excel Discussion (Misc queries) | |||
Clean Up Background While Running Macro | Excel Programming | |||
running macro at the background | Excel Programming | |||
Running Excel as a batch job AND in the background | Excel Programming |