If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I automate a mail merge from Excel data & have it updateevery time?
I know I must be just a step or two away from resolving this thing -
let's hope this one's easy for someone out there. My friend has an Excel 2007 spreadsheet for a mailing list she keeps. She updates this spreadsheet once or twice a month and wants to print labels from it using mail merge, but would like it as automated as possible. I'm assuming this must be done with a macro. I can get the mail merge to work correctly if I do it manually; however if I record a macro of the merge process, it seems to freeze the spreadsheet in time to that moment and any subsequent updates to the list are not reflected in the next mail merge. After perusing this newsgroup a bit and Googling for various results, I've tried a few things but cannot get it to work. I've tried creating an ODBC data source from the spreadsheet (which was missing a key field when I tried to link to it) and I've tried importing the data into Access or Outlook instead (not going to work either). Once I solve this issue of the data not being updated in the merge, then I need to make the merge creation process as streamlined as possible for my friend (a computer novice). The advice I found on the Web was to create a Word shortcut using the /t and /m switches to load a new file and start the macro. I'm envisioning this to be the simplest way for her to access her labels. Does that sound about right? Many thanks for any feedback you can offer. I'm banging my head against the wall on this one. |
#2
|
|||
|
|||
How can I automate a mail merge from Excel data & have it update every time?
You can use an Excel macro such as the following. But please see the notes
afterwords. Sub mergeme() Dim bCreatedWordInstance As Boolean Dim objWord As Word.Application Dim objMMMD As Word.Document On Error Resume Next bCreatedWordInstance = False Set objWord = GetObject(, "Word.Application") If objWord Is Nothing Then Err.Clear Set objWord = CreateObject("Word.Application") bCreatedWordInstance = True End If If objWord Is Nothing Then MsgBox "Could not start Word" Err.Clear On Error GoTo 0 Exit Sub End If ' Let Word trap the errors On Error GoTo 0 ' During testing. make sure we can see what we are doing objWord.Visible = True Set objMMMD = objWord.Documents.Open("C:\Users\pjj.DOMAINX\Docum ents\mergefromxl.docx") objMMMD.Activate With objMMMD .MailMerge.OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement:="SELECT * FROM [" & ActiveSheet.Name & "$]" ' Set this as required .MailMerge.Destination = wdSendToNewDocument .MailMerge.Execute End With ' If you need to work with the output document, ' it is now the ActiveDocument, unless there were errors objWord.ActiveDocument.SaveAs "my output document 2.docx" objWord.ActiveDocument.Close savechanges:=wdDoNotSaveChanges ' Close the Mail Merge Main Document objMMMD.Close savechanges:=wdDoNotSaveChanges Set objMMMD = Nothing ' If you have dealt with the new document and want ' to close Word, use something like this... If bCreatedWordInstance Then objWord.Quit End If Set objWord = Nothing End Sub Notes. 1. In the Excel VB Editor, with the relevant Excel document open, you need to use Tools-Reference make a reference to the Microsoft Word 12.0 Object Library 2. You should create the Mail Merge Main Document and go through the usual steps of connecting to the data source then laying out the labels. Then you should se the document back to being a "Normal Word Document", then select the Labels option and (probably) cancel the dialog. Do not reconnect the data source, because... 3. ...you need to save the Mail Merge Main Document with no data source attached. 4. You probably also have to make the registry change described in http://support.microsoft.com/kb/825765/en-us 5. The OpenDataSource code above assumes you want to open the currently selected sheet. If you want to open a specific named range or some other sheet, you will need to modify this line: sqlstatement:="SELECT * FROM [" & ActiveSheet.Name & "$]" You may also have to deal with other security issues, the business of how your friend initiates the macro, etc. etc. Not much, eh? :-) -- Peter Jamieson http://tips.pjmsn.me.uk "MagGyver" wrote in message ... I know I must be just a step or two away from resolving this thing - let's hope this one's easy for someone out there. My friend has an Excel 2007 spreadsheet for a mailing list she keeps. She updates this spreadsheet once or twice a month and wants to print labels from it using mail merge, but would like it as automated as possible. I'm assuming this must be done with a macro. I can get the mail merge to work correctly if I do it manually; however if I record a macro of the merge process, it seems to freeze the spreadsheet in time to that moment and any subsequent updates to the list are not reflected in the next mail merge. After perusing this newsgroup a bit and Googling for various results, I've tried a few things but cannot get it to work. I've tried creating an ODBC data source from the spreadsheet (which was missing a key field when I tried to link to it) and I've tried importing the data into Access or Outlook instead (not going to work either). Once I solve this issue of the data not being updated in the merge, then I need to make the merge creation process as streamlined as possible for my friend (a computer novice). The advice I found on the Web was to create a Word shortcut using the /t and /m switches to load a new file and start the macro. I'm envisioning this to be the simplest way for her to access her labels. Does that sound about right? Many thanks for any feedback you can offer. I'm banging my head against the wall on this one. |
#3
|
|||
|
|||
How can I automate a mail merge from Excel data & have it updateevery time?
Wow, Peter, that's quite a mouthful. Thanks ever so much for the quick
response. To be honest I've never seen the VB editor in action before and quite frankly am having a hard time figuring out how to customize the code you provided, where I need to insert my own paths etc. When I talked about recording a macro, I was doing so in MS Word, visually using the macro recorder, rather than on the backend of the code through VB editor. I'm not entirely sure I have the knowledge or skills to operate on this level. I will show this to a programmer friend of mine, but in the meantime, is there any way you can see that someone could use the macro recorder in Word to accomplish what I'm after? Thanks On Jul 23, 2:19*am, "Peter Jamieson" wrote: You can use an Excel macro such as the following. But please see the notes afterwords. Sub mergeme() Dim bCreatedWordInstance As Boolean Dim objWord As Word.Application Dim objMMMD As Word.Document On Error Resume Next bCreatedWordInstance = False Set objWord = GetObject(, "Word.Application") If objWord Is Nothing Then * Err.Clear * Set objWord = CreateObject("Word.Application") * bCreatedWordInstance = True End If If objWord Is Nothing Then * MsgBox "Could not start Word" * Err.Clear * On Error GoTo 0 * Exit Sub End If ' Let Word trap the errors On Error GoTo 0 ' During testing. make sure we can see what we are doing objWord.Visible = True Set objMMMD = objWord.Documents.Open("C:\Users\pjj.DOMAINX\Docum ents\mergefromxl.docx") objMMMD.Activate With objMMMD * .MailMerge.OpenDataSource _ * * Name:=ActiveWorkbook.FullName, _ * * sqlstatement:="SELECT * FROM [" & ActiveSheet.Name & "$]" * ' Set this as required * .MailMerge.Destination = wdSendToNewDocument * .MailMerge.Execute End With ' If you need to work with the output document, ' it is now the ActiveDocument, unless there were errors objWord.ActiveDocument.SaveAs "my output document 2.docx" objWord.ActiveDocument.Close savechanges:=wdDoNotSaveChanges ' Close the Mail Merge Main Document objMMMD.Close savechanges:=wdDoNotSaveChanges Set objMMMD = Nothing ' If you have dealt with the new document and want ' to close Word, use something like this... If bCreatedWordInstance Then * objWord.Quit End If Set objWord = Nothing End Sub Notes. 1. In the Excel VB Editor, with the relevant Excel document open, you need to use Tools-Reference make a reference to the Microsoft Word 12.0 Object Library 2. You should create the Mail Merge Main Document and go through the usual steps of connecting to the data source then laying out the labels. Then you should se the document back to being a "Normal Word Document", then select the Labels option and (probably) cancel the dialog. Do not reconnect the data source, because... 3. ...you need to save the Mail Merge Main Document with no data source attached. 4. You probably also have to make the registry change described in http://support.microsoft.com/kb/825765/en-us 5. The OpenDataSource code above assumes you want to open the currently selected sheet. If you want to open a specific named range or some other sheet, you will need to modify this line: * * sqlstatement:="SELECT * FROM [" & ActiveSheet.Name & "$]" You may also have to deal with other security issues, the business of how your friend initiates the macro, etc. etc. Not much, eh? :-) -- Peter Jamiesonhttp://tips.pjmsn.me.uk "MagGyver" wrote in message ... I know I must be just a step or two away from resolving this thing - let's hope this one's easy for someone out there. My friend has an Excel 2007 spreadsheet for a mailing list she keeps. She updates this spreadsheet once or twice a month and wants to print labels from it using mail merge, but would like it as automated as possible. I'm assuming this must be done with a macro. I can get the mail merge to work correctly if I do it manually; however if I record a macro of the merge process, it seems to freeze the spreadsheet in time to that moment and any subsequent updates to the list are not reflected in the next mail merge. After perusing this newsgroup a bit and Googling for various results, I've tried a few things but cannot get it to work. I've tried creating an ODBC data source from the spreadsheet (which was missing a key field when I tried to link to it) and I've tried importing the data into Access or Outlook instead (not going to work either). Once I solve this issue of the data not being updated in the merge, then I need to make the merge creation process as streamlined as possible for my friend (a computer novice). The advice I found on the Web was to create a Word shortcut using the /t and /m switches to load a new file and start the macro. I'm envisioning this to be the simplest way for her to access her labels. Does that sound about right? Many thanks for any feedback you can offer. I'm banging my head against the wall on this one. |
#4
|
|||
|
|||
How can I automate a mail merge from Excel data & have it update every time?
Sorry, I had assumed you meant "start from Excel, because if you're starting
from Word, all you should have to do is open the Word document, ensure it reconnects to the data source, then perform the merge. You might need to save the Excel workbook first. To be honest, I try not to automate stuff with as few steps as that except for myself, because for most people it's easier to repeat the steps than struggle with all the additional stuff that having a macro forces on you. As a minimum, I'd say that your friend should a. save and close the Excel document b. open Word c. open the relevant Mail Merge Main document, which should already be connected to the data source. d. answer the security questions If you want to automate the rest of it, you can put the following in a module in that document: Sub AutoOpen() With ActiveDocument .MailMerge.Destination = wdSendToPrinter .MailMerge.Execute End With End Sub and it should run after all those security questions have been answered. Just my 2c-worth -- Peter Jamieson http://tips.pjmsn.me.uk "MagGyver" wrote in message ... Wow, Peter, that's quite a mouthful. Thanks ever so much for the quick response. To be honest I've never seen the VB editor in action before and quite frankly am having a hard time figuring out how to customize the code you provided, where I need to insert my own paths etc. When I talked about recording a macro, I was doing so in MS Word, visually using the macro recorder, rather than on the backend of the code through VB editor. I'm not entirely sure I have the knowledge or skills to operate on this level. I will show this to a programmer friend of mine, but in the meantime, is there any way you can see that someone could use the macro recorder in Word to accomplish what I'm after? Thanks On Jul 23, 2:19 am, "Peter Jamieson" wrote: You can use an Excel macro such as the following. But please see the notes afterwords. Sub mergeme() Dim bCreatedWordInstance As Boolean Dim objWord As Word.Application Dim objMMMD As Word.Document On Error Resume Next bCreatedWordInstance = False Set objWord = GetObject(, "Word.Application") If objWord Is Nothing Then Err.Clear Set objWord = CreateObject("Word.Application") bCreatedWordInstance = True End If If objWord Is Nothing Then MsgBox "Could not start Word" Err.Clear On Error GoTo 0 Exit Sub End If ' Let Word trap the errors On Error GoTo 0 ' During testing. make sure we can see what we are doing objWord.Visible = True Set objMMMD = objWord.Documents.Open("C:\Users\pjj.DOMAINX\Docum ents\mergefromxl.docx") objMMMD.Activate With objMMMD .MailMerge.OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement:="SELECT * FROM [" & ActiveSheet.Name & "$]" ' Set this as required .MailMerge.Destination = wdSendToNewDocument .MailMerge.Execute End With ' If you need to work with the output document, ' it is now the ActiveDocument, unless there were errors objWord.ActiveDocument.SaveAs "my output document 2.docx" objWord.ActiveDocument.Close savechanges:=wdDoNotSaveChanges ' Close the Mail Merge Main Document objMMMD.Close savechanges:=wdDoNotSaveChanges Set objMMMD = Nothing ' If you have dealt with the new document and want ' to close Word, use something like this... If bCreatedWordInstance Then objWord.Quit End If Set objWord = Nothing End Sub Notes. 1. In the Excel VB Editor, with the relevant Excel document open, you need to use Tools-Reference make a reference to the Microsoft Word 12.0 Object Library 2. You should create the Mail Merge Main Document and go through the usual steps of connecting to the data source then laying out the labels. Then you should se the document back to being a "Normal Word Document", then select the Labels option and (probably) cancel the dialog. Do not reconnect the data source, because... 3. ...you need to save the Mail Merge Main Document with no data source attached. 4. You probably also have to make the registry change described in http://support.microsoft.com/kb/825765/en-us 5. The OpenDataSource code above assumes you want to open the currently selected sheet. If you want to open a specific named range or some other sheet, you will need to modify this line: sqlstatement:="SELECT * FROM [" & ActiveSheet.Name & "$]" You may also have to deal with other security issues, the business of how your friend initiates the macro, etc. etc. Not much, eh? :-) -- Peter Jamiesonhttp://tips.pjmsn.me.uk "MagGyver" wrote in message ... I know I must be just a step or two away from resolving this thing - let's hope this one's easy for someone out there. My friend has an Excel 2007 spreadsheet for a mailing list she keeps. She updates this spreadsheet once or twice a month and wants to print labels from it using mail merge, but would like it as automated as possible. I'm assuming this must be done with a macro. I can get the mail merge to work correctly if I do it manually; however if I record a macro of the merge process, it seems to freeze the spreadsheet in time to that moment and any subsequent updates to the list are not reflected in the next mail merge. After perusing this newsgroup a bit and Googling for various results, I've tried a few things but cannot get it to work. I've tried creating an ODBC data source from the spreadsheet (which was missing a key field when I tried to link to it) and I've tried importing the data into Access or Outlook instead (not going to work either). Once I solve this issue of the data not being updated in the merge, then I need to make the merge creation process as streamlined as possible for my friend (a computer novice). The advice I found on the Web was to create a Word shortcut using the /t and /m switches to load a new file and start the macro. I'm envisioning this to be the simplest way for her to access her labels. Does that sound about right? Many thanks for any feedback you can offer. I'm banging my head against the wall on this one. |
#5
|
|||
|
|||
How can I automate a mail merge from Excel data & have it updateevery time?
Peter, thanks so much for your help! That last bit of code you gave me
for automation was what did the trick. I created a macro within the mail merge document (using ODBC to link to the Excel worksheet) and substituted "wdSendtoNewDocument" for "wdSendtoPrinter" to tailor it for my friend, and then created a shortcut to the mail merge document using the /m switch to invoke the macro. Now all she needs to do is to click on the shortcut, answer one confirmation about linking, and voila! The labels appear as they should. Thanks again. On Jul 23, 9:41*am, "Peter Jamieson" wrote: Sorry, I had assumed you meant "start from Excel, because if you're starting from Word, all you should have to do is open the Word document, ensure it reconnects to the data source, then perform the merge. You might need to save the Excel workbook first. To be honest, I try not to automate stuff with as few steps as that except for myself, because for most people it's easier to repeat the steps than struggle with all the additional stuff that having a macro forces on you. As a minimum, I'd say that your friend should *a. save and close the Excel document *b. open Word *c. open the relevant Mail Merge Main document, which should already be connected to the data source. *d. answer the security questions If you want to automate the rest of it, you can put the following in a module in that document: Sub AutoOpen() With ActiveDocument * .MailMerge.Destination = wdSendToPrinter * .MailMerge.Execute End With End Sub and it should run after all those security questions have been answered. Just my 2c-worth -- Peter Jamiesonhttp://tips.pjmsn.me.uk "MagGyver" wrote in message ... Wow, Peter, that's quite a mouthful. Thanks ever so much for the quick response. To be honest I've never seen the VB editor in action before and quite frankly am having a hard time figuring out how to customize the code you provided, where I need to insert my own paths etc. When I talked about recording a macro, I was doing so in MS Word, visually using the macro recorder, rather than on the backend of the code through VB editor. I'm not entirely sure I have the knowledge or skills to operate on this level. I will show this to a programmer friend of mine, but in the meantime, is there any way you can see that someone could use the macro recorder in Word to accomplish what I'm after? Thanks On Jul 23, 2:19 am, "Peter Jamieson" wrote: You can use an Excel macro such as the following. But please see the notes afterwords. Sub mergeme() Dim bCreatedWordInstance As Boolean Dim objWord As Word.Application Dim objMMMD As Word.Document On Error Resume Next bCreatedWordInstance = False Set objWord = GetObject(, "Word.Application") If objWord Is Nothing Then Err.Clear Set objWord = CreateObject("Word.Application") bCreatedWordInstance = True End If If objWord Is Nothing Then MsgBox "Could not start Word" Err.Clear On Error GoTo 0 Exit Sub End If ' Let Word trap the errors On Error GoTo 0 ' During testing. make sure we can see what we are doing objWord.Visible = True Set objMMMD = objWord.Documents.Open("C:\Users\pjj.DOMAINX\Docum ents\mergefromxl.docx") objMMMD.Activate With objMMMD .MailMerge.OpenDataSource _ Name:=ActiveWorkbook.FullName, _ sqlstatement:="SELECT * FROM [" & ActiveSheet.Name & "$]" ' Set this as required .MailMerge.Destination = wdSendToNewDocument .MailMerge.Execute End With ' If you need to work with the output document, ' it is now the ActiveDocument, unless there were errors objWord.ActiveDocument.SaveAs "my output document 2.docx" objWord.ActiveDocument.Close savechanges:=wdDoNotSaveChanges ' Close the Mail Merge Main Document objMMMD.Close savechanges:=wdDoNotSaveChanges Set objMMMD = Nothing ' If you have dealt with the new document and want ' to close Word, use something like this... If bCreatedWordInstance Then objWord.Quit End If Set objWord = Nothing End Sub Notes. 1. In the Excel VB Editor, with the relevant Excel document open, you need to use Tools-Reference make a reference to the Microsoft Word 12.0 Object Library 2. You should create the Mail Merge Main Document and go through the usual steps of connecting to the data source then laying out the labels. Then you should se the document back to being a "Normal Word Document", then select the Labels option and (probably) cancel the dialog. Do not reconnect the data source, because... 3. ...you need to save the Mail Merge Main Document with no data source attached. 4. You probably also have to make the registry change described in http://support.microsoft.com/kb/825765/en-us 5. The OpenDataSource code above assumes you want to open the currently selected sheet. If you want to open a specific named range or some other sheet, you will need to modify this line: sqlstatement:="SELECT * FROM [" & ActiveSheet.Name & "$]" You may also have to deal with other security issues, the business of how your friend initiates the macro, etc. etc. Not much, eh? :-) -- Peter Jamiesonhttp://tips.pjmsn.me.uk "MagGyver" wrote in message .... I know I must be just a step or two away from resolving this thing - let's hope this one's easy for someone out there. My friend has an Excel 2007 spreadsheet for a mailing list she keeps. She updates this spreadsheet once or twice a month and wants to print labels from it using mail merge, but would like it as automated as possible. I'm assuming this must be done with a macro. I can get the mail merge to work correctly if I do it manually; however if I record a macro of the merge process, it seems to freeze the spreadsheet in time to that moment and any subsequent updates to the list are not reflected in the next mail merge. After perusing this newsgroup a bit and Googling for various results, I've tried a few things but cannot get it to work. I've tried creating an ODBC data source from the spreadsheet (which was missing a key field when I tried to link to it) and I've tried importing the data into Access or Outlook instead (not going to work either). Once I solve this issue of the data not being updated in the merge, then I need to make the merge creation process as streamlined as possible for my friend (a computer novice). The advice I found on the Web was to create a Word shortcut using the /t and /m switches to load a new file and start the macro. I'm envisioning this to be the simplest way for her to access her labels. Does that sound about right? Many thanks for any feedback you can offer. I'm banging my head against the wall on this one. |
Thread Tools | |
Display Modes | |
|
|