Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
"Peter Jamieson" wrote in message ... : First, if you open the Template (e.g. using Word File|Open, rather than : creating a new document based on it), is it connected to the data source? : (e.g. if you display the MailMerge toolbar using Tools|Customize, : a. are most/all the buttons after the first two greyed out? : b. if you click the first button, what type of document is selected?) If I open the template (rather than creating a new doc based on the template) I am prompted with the SQL prompt. If I choose No, then all the buttons but the first two are grayed out and Normal Word Doc is selected as the Main Document Type. If I choose Yes, then all the buttons are available and Letters is selected as the Main Document Type. (Good questions!) : Second... : : One is that when I run the MergeFromExcel : macro, I get prompted to select the table. This was previously included in : the macro. : : ...does your macro issue an OpenDataSource call? If so, can you copy/paste : the code for that call into a message in this thread? I don't know. Below is the code in the Macro (both macros, actually). It's a recorded macro, nothing fancy. I figured I could re-record the macro to fix this problem. It's the problem mentioned below that I don't know how to fix. Sub AutoNew() ' ' AutoNew Macro ' Macro created 12/18/2003 by Jennifer Q Dickens ' Selection.GoTo What:=wdGoToBookmark, Name:="DueDate" Selection.InsertBefore Format((Date + 14), "d MMMM, yyyy") End Sub Sub MergeFromExcel() ' ' MergeFromExcel Macro ' Macro recorded 2/19/2004 by Scot J dobson ' ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.OpenDataSource Name:= _ "K:\AA documents\SRC IP Tracking spreadsheet.xls", ConfirmConversions:= _ False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _ PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _ WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _ Connection:="CurrentData", SQLStatement:="", SQLStatement1:="" With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .MailAsAttachment = False .MailAddressFieldName = "" .MailSubject = "" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=True End With End Sub : The completed merged documents seem to be : retaining both their macros : : I'll look at this separately. I think I love you. Thanks. : Peter Jamieson : "Jeni Q" wrote in message : ... : I really do need the mail merge to be connected to the template. That's the : whole point of this exercise. My user has a letter that he has to complete : several times a day, but never in a big batch job. It's always a single : letter at a time. The text is the same on the letter with the exception of : a : few fields such as name, title, university, etc. : : Previously in Word 2000, I created a template with 2 macros and several : data : fields in it. The first macro is an AUTONEW macro that inserts a : calculated : date at a bookmarked location. The date is based on the CREATEDATE which : is : also inserted when the template is opened. The second macro : (MergeFromExcel) : is a very simple recorded macro which consists of opening the data source, : finding the named range, and merging the data. The data fields are in the : template already, so the macro runs through and dumps the data into the : document, leaving me with a completed merged document that has a : createdate, : a calculated date, and my 6 fields pulled from the excel spreadsheet. : : We have just upgraded to 2003 recently. So with this template, everything : is : working OK now but two things. One is that when I run the MergeFromExcel : macro, I get prompted to select the table. This was previously included in : the macro. This I can fix by rerecording the macro or editing it if I'm : really clever. (I don't know any VBA, which limits my abilities.) The : second : problem is a larger problem. The completed merged documents seem to be : retaining both their macros and their database connection. So when I open : one of the completed documents, I'm promted to enable/disable macros and : I'm : also prompted with the SQL warning. : : What's really weird about Word 2003 is that it seems to remember some of : the : data connections, sometimes, but it's not consistent. What's up with that? : : So, anyway, it seems to me that I do need the merge associated with the : special template. There may be a real obvious answer to this problem that : I'm missing. Any advice? : : : : "Peter Jamieson" wrote in message : ... : : Don't know if that is what has happened, but if it is and you do not : need : : your mail merge main document to be connected to this template, : disconnect : : it, or alternatively, disconnect the template and set up the connection : in : a : : VBA AutoNew macro if you know how to do that. In many cases, the : sensible : : thing to do when dealing with mailmerge is to develop your mail merge : main : : document, connect it to its data source, associate it with the : normal.dot : : template, then save it, and avoid using a special template - it's not : how : : Word is really supposed to work, but it probably causes fewer problems : with : : mailmerge, unless, for example, you need to use fields such as ASK, : FILLIN, : : and (especially) CREATEDATE, which tend to work differently when you : create : : a new document from a template. : : : : recieve : : : : can't believe I used that spelling, even by accident! : : : : Peter Jamieson : : "Jeni Q" wrote in message : : ... : : I think this must be what's happening. The weird thing is that one time : I : : got it to work right. But I can't remember how. :-O : : "Peter Jamieson" wrote in message : : ... : : : Is your document based on (associated with) a template (.dot) that : is : : : connected to a data source? If so, you might recieve this message in : : recent : : : versions of Word because when the template opens, it tries to : connect : to : : the : : : data source. : : : : : : Peter Jamieson : : : : : : "Jeni Q" wrote in message : : : ... : : : Yes, I'm pretty sure that the merge has been executed. Typically, : when : : you : : : have not executed the merge, most of the toolbar buttons are : available : : to : : : you. You can also see the field shading (I have mine set to : Always). : : : In : : : this : : : completed document, there are no shaded fields appearing and the : only : : : mailmerge toolbar buttons available are Main Document Setup and : Open : : Data : : : Source. So it clearly seems to me that it has merged. : : : : : : However, it's insistence on prompting to connect to the data : source : on : : : open : : : proves otherwise. What could be keeping the connection? : : : : : : Jennifer : : : : : : "Doug Robbins" wrote in message : : : ... : : : : Are you sure that you have actually executed the merge to a new : : : document? : : : : : : : : I suggest that you select Toolbars from the View menu and then : check : : the : : : : mailmerge item to display the mailmerge toolbar, then use the : : buttons : : on : : : the : : : : Right Hand end of the toolbar to execute the mailmerge. : : : : : : : : -- : : : : Hope this helps. : : : : : : : : Please reply to the newsgroup unless you wish to avail yourself : of : : my : : : : services on a paid consulting basis. : : : : : : : : Doug Robbins - Word MVP : : : : "Jeni Q" wrote in message : : : : ... : : : : Hello. I've read KB 825765 so I understand when and why I get : : prompted : : : : with : : : : the "Opening this will run the following SQL command" message : when : : you : : : : open : : : : a Word mail merge main document that is linked to a data : source. : : : : : : : : However, I do not understand why I get prompted for the : document : : after : : : the : : : : merge has run and my new document has been created and saved. : When : : I : : : close : : : : and reopen the newly created document, I still get the prompt. : : There : : : is : : : : obviously a data attachment still existing. Is there any way : to : : break : : : that : : : : attachment? : : : : : : : : I may not be understanding clearly how the merge process : worked. : I : : : thought : : : : it brought data into the new document and that's it. Is it : : : continuously : : : : going to the data source to get the data? : : : : : : : : Any help/advice would be appreciated. : : : : : : : : Thanks, : : : : Jennifer Dickens : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : |
#2
|
|||
|
|||
If I open the template (rather than creating a new doc based on the
template) I am prompted with the SQL prompt. OK, in that case I suggest you open the template, go through your connection process, then click the first button, select the "Normal Word Document" option, and save the template. At least that should stop the /template/ from trying to connect when it loads. I don't know. Below is the code in the Macro (both macros, actually). It's a recorded macro, nothing fancy. I figured I could re-record the macro to fix this problem. Always worth a try - unfortunately I don't think re-recording will help as Word records this stuff very badly. It's the problem mentioned below that I don't know how to fix. OK, the key statement is this one - I'll just chop out stuff I'm pretty sure is not needed for starters: ActiveDocument.MailMerge.OpenDataSource _ Name:="K:\AA documents\SRC IP Tracking spreadsheet.xls", _ Connection:="CurrentData", _ SQLStatement:="" The main questions are a. does this need to work in Word 2000 as well as Word 2003 - if so, is it easy for you to maintain two different versions? Or does it just need to work in Word 2003? b. are you connecting to a named range called CurrentData? Is it in the first sheet in your Excel workbook? : The completed merged documents seem to be : retaining both their macros : : I'll look at this separately. ....eventually :-) Peter Jamieson "Jeni Q" wrote in message ... "Peter Jamieson" wrote in message ... : First, if you open the Template (e.g. using Word File|Open, rather than : creating a new document based on it), is it connected to the data source? : (e.g. if you display the MailMerge toolbar using Tools|Customize, : a. are most/all the buttons after the first two greyed out? : b. if you click the first button, what type of document is selected?) If I open the template (rather than creating a new doc based on the template) I am prompted with the SQL prompt. If I choose No, then all the buttons but the first two are grayed out and Normal Word Doc is selected as the Main Document Type. If I choose Yes, then all the buttons are available and Letters is selected as the Main Document Type. (Good questions!) : Second... : : One is that when I run the MergeFromExcel : macro, I get prompted to select the table. This was previously included in : the macro. : : ...does your macro issue an OpenDataSource call? If so, can you copy/paste : the code for that call into a message in this thread? I don't know. Below is the code in the Macro (both macros, actually). It's a recorded macro, nothing fancy. I figured I could re-record the macro to fix this problem. It's the problem mentioned below that I don't know how to fix. Sub AutoNew() ' ' AutoNew Macro ' Macro created 12/18/2003 by Jennifer Q Dickens ' Selection.GoTo What:=wdGoToBookmark, Name:="DueDate" Selection.InsertBefore Format((Date + 14), "d MMMM, yyyy") End Sub Sub MergeFromExcel() ' ' MergeFromExcel Macro ' Macro recorded 2/19/2004 by Scot J dobson ' ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.OpenDataSource Name:= _ "K:\AA documents\SRC IP Tracking spreadsheet.xls", ConfirmConversions:= _ False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _ PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _ WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _ Connection:="CurrentData", SQLStatement:="", SQLStatement1:="" With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .MailAsAttachment = False .MailAddressFieldName = "" .MailSubject = "" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=True End With End Sub : The completed merged documents seem to be : retaining both their macros : : I'll look at this separately. I think I love you. Thanks. : Peter Jamieson : "Jeni Q" wrote in message : ... : I really do need the mail merge to be connected to the template. That's the : whole point of this exercise. My user has a letter that he has to complete : several times a day, but never in a big batch job. It's always a single : letter at a time. The text is the same on the letter with the exception of : a : few fields such as name, title, university, etc. : : Previously in Word 2000, I created a template with 2 macros and several : data : fields in it. The first macro is an AUTONEW macro that inserts a : calculated : date at a bookmarked location. The date is based on the CREATEDATE which : is : also inserted when the template is opened. The second macro : (MergeFromExcel) : is a very simple recorded macro which consists of opening the data source, : finding the named range, and merging the data. The data fields are in the : template already, so the macro runs through and dumps the data into the : document, leaving me with a completed merged document that has a : createdate, : a calculated date, and my 6 fields pulled from the excel spreadsheet. : : We have just upgraded to 2003 recently. So with this template, everything : is : working OK now but two things. One is that when I run the MergeFromExcel : macro, I get prompted to select the table. This was previously included in : the macro. This I can fix by rerecording the macro or editing it if I'm : really clever. (I don't know any VBA, which limits my abilities.) The : second : problem is a larger problem. The completed merged documents seem to be : retaining both their macros and their database connection. So when I open : one of the completed documents, I'm promted to enable/disable macros and : I'm : also prompted with the SQL warning. : : What's really weird about Word 2003 is that it seems to remember some of : the : data connections, sometimes, but it's not consistent. What's up with that? : : So, anyway, it seems to me that I do need the merge associated with the : special template. There may be a real obvious answer to this problem that : I'm missing. Any advice? : : : : "Peter Jamieson" wrote in message : ... : : Don't know if that is what has happened, but if it is and you do not : need : : your mail merge main document to be connected to this template, : disconnect : : it, or alternatively, disconnect the template and set up the connection : in : a : : VBA AutoNew macro if you know how to do that. In many cases, the : sensible : : thing to do when dealing with mailmerge is to develop your mail merge : main : : document, connect it to its data source, associate it with the : normal.dot : : template, then save it, and avoid using a special template - it's not : how : : Word is really supposed to work, but it probably causes fewer problems : with : : mailmerge, unless, for example, you need to use fields such as ASK, : FILLIN, : : and (especially) CREATEDATE, which tend to work differently when you : create : : a new document from a template. : : : : recieve : : : : can't believe I used that spelling, even by accident! : : : : Peter Jamieson : : "Jeni Q" wrote in message : : ... : : I think this must be what's happening. The weird thing is that one time : I : : got it to work right. But I can't remember how. :-O : : "Peter Jamieson" wrote in message : : ... : : : Is your document based on (associated with) a template (.dot) that : is : : : connected to a data source? If so, you might recieve this message in : : recent : : : versions of Word because when the template opens, it tries to : connect : to : : the : : : data source. : : : : : : Peter Jamieson : : : : : : "Jeni Q" wrote in message : : : ... : : : Yes, I'm pretty sure that the merge has been executed. Typically, : when : : you : : : have not executed the merge, most of the toolbar buttons are : available : : to : : : you. You can also see the field shading (I have mine set to : Always). : : : In : : : this : : : completed document, there are no shaded fields appearing and the : only : : : mailmerge toolbar buttons available are Main Document Setup and : Open : : Data : : : Source. So it clearly seems to me that it has merged. : : : : : : However, it's insistence on prompting to connect to the data : source : on : : : open : : : proves otherwise. What could be keeping the connection? : : : : : : Jennifer : : : : : : "Doug Robbins" wrote in message : : : ... : : : : Are you sure that you have actually executed the merge to a new : : : document? : : : : : : : : I suggest that you select Toolbars from the View menu and then : check : : the : : : : mailmerge item to display the mailmerge toolbar, then use the : : buttons : : on : : : the : : : : Right Hand end of the toolbar to execute the mailmerge. : : : : : : : : -- : : : : Hope this helps. : : : : : : : : Please reply to the newsgroup unless you wish to avail yourself : of : : my : : : : services on a paid consulting basis. : : : : : : : : Doug Robbins - Word MVP : : : : "Jeni Q" wrote in message : : : : ... : : : : Hello. I've read KB 825765 so I understand when and why I get : : prompted : : : : with : : : : the "Opening this will run the following SQL command" message : when : : you : : : : open : : : : a Word mail merge main document that is linked to a data : source. : : : : : : : : However, I do not understand why I get prompted for the : document : : after : : : the : : : : merge has run and my new document has been created and saved. : When : : I : : : close : : : : and reopen the newly created document, I still get the prompt. : : There : : : is : : : : obviously a data attachment still existing. Is there any way : to : : break : : : that : : : : attachment? : : : : : : : : I may not be understanding clearly how the merge process : worked. : I : : : thought : : : : it brought data into the new document and that's it. Is it : : : continuously : : : : going to the data source to get the data? : : : : : : : : Any help/advice would be appreciated. : : : : : : : : Thanks, : : : : Jennifer Dickens : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : |
#3
|
|||
|
|||
"Peter Jamieson" wrote in message
... : If I open the template (rather than creating a new doc based on the : template) I am prompted with the SQL prompt. : : OK, in that case I suggest you open the template, go through your connection : process, then click the first button, select the "Normal Word Document" : option, and save the template. At least that should stop the /template/ from : trying to connect when it loads. This works beautifully. When I first started messing around with this I thought for a little while that I could just keep changing the data in the data source (it's a short row of data) and then every time I opened the template the data would already be there. This seemed like a very cool option. However, the side effect of that was that the documents I saved from the templates still kept the data connection, which was no good. : : OK, the key statement is this one - I'll just chop out stuff I'm pretty sure : is not needed for starters: : : ActiveDocument.MailMerge.OpenDataSource _ : Name:="K:\AA documents\SRC IP Tracking spreadsheet.xls", _ : Connection:="CurrentData", _ : SQLStatement:="" : : The main questions are : a. does this need to work in Word 2000 as well as Word 2003 - if so, is it : easy for you to maintain two different versions? Or does it just need to : work in Word 2003? : b. are you connecting to a named range called CurrentData? Is it in the : first sheet in your Excel workbook? No, it only needs to work in 2003. Yes, we are connecting to a named range called CurrentData and it is in the first sheet of the workbook. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Upon opening, can Word pull up same 4 documents automatically? | Microsoft Word Help | |||
Certain "word" documents opening in larger font and appearing bigg | Microsoft Word Help | |||
best way to handle five documents that need to be merged in the e. | Microsoft Word Help | |||
Opening templates as documents | Microsoft Word Help | |||
Sending Merged Documents to Printer Individually | Mailmerge |