Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-13-2010, 11:34 AM
Brandi Brandi is offline custom toolbar with buttons to open new docs based on templates Windows XP custom toolbar with buttons to open new docs based on templates Office 2003
Novice
custom toolbar with buttons to open new docs based on templates
 
Join Date: Jun 2010
Posts: 13
Brandi is on a distinguished road
Default custom toolbar with buttons to open new docs based on templates

I have created a cutom toolbar with buttons that will open various templates that are mail merge documents and each template has a different MS Access table as its source.



I have tried making the hyperlink for these files using both the Open and New Commands but either way, it always opens the actual dot file and not a document (doc) based on the template.

How can I get the button to create a new document (doc) based on the template and therefore showing the merged data?

Thank you.
Brandi
Reply With Quote
  #2  
Old 06-14-2010, 03:32 AM
Kimberly Kimberly is offline custom toolbar with buttons to open new docs based on templates Windows 7 custom toolbar with buttons to open new docs based on templates Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

record a macro to create the file and attach the macro to the button
Reply With Quote
  #3  
Old 06-14-2010, 06:51 AM
Brandi Brandi is offline custom toolbar with buttons to open new docs based on templates Windows XP custom toolbar with buttons to open new docs based on templates Office 2003
Novice
custom toolbar with buttons to open new docs based on templates
 
Join Date: Jun 2010
Posts: 13
Brandi is on a distinguished road
Default

Quote:
Originally Posted by Kimberly View Post
record a macro to create the file and attach the macro to the button
I recorded the following macro and the last step is giving me an error. I recorded the merging of the data (ABC Merge button) and when I execute the macro it doesn't like that step. It just opens a new document showing the field codes.

Also, how do I attach this macro to a button on a toolbar? I know how to create a new button on a toolbar, but I didn't see a way to assign a macro to it.

Thank you.
Brandi

Sub Petition()
'
' Petition Macro
' Macro recorded 6/14/2010
'
Documents.Add Template:= _
"C:\Bergemann\Forfeiture\WordMailMerge\Petition.do t", NewTemplate:=False, _
DocumentType:=0
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub
Reply With Quote
  #4  
Old 06-14-2010, 07:19 AM
Kimberly Kimberly is offline custom toolbar with buttons to open new docs based on templates Windows 7 custom toolbar with buttons to open new docs based on templates Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

The (ABC Merge button) doesn't complete the merge, it only lets you see what it would look like merged.

Re toolbar button: In the Customize dialog box, Commands tab, select Macro... near the bottom of Categories list. After you add it to a toolbar, you can use the Modify button to make it look better.

Your macro to create the doc from a dot looks good.
Reply With Quote
  #5  
Old 06-14-2010, 07:20 AM
Brandi Brandi is offline custom toolbar with buttons to open new docs based on templates Windows XP custom toolbar with buttons to open new docs based on templates Office 2003
Novice
custom toolbar with buttons to open new docs based on templates
 
Join Date: Jun 2010
Posts: 13
Brandi is on a distinguished road
Default

I do see how to attach a macro to a button. I see now that there is a Macro category on the Commands tab of customize toolbars. So the remaining question is why am I getting a "requested object is not available" message when I execute my macro?
If I perform the exact same steps while not recording a macro, my template opens up a new document as expected and the merge data button is available. If I record these exact same steps, I get the "requested object is not available" debug on the last statement of my code (ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle) which should merge the data into the document. The merge button is greyed out and not available to do manually either.
Reply With Quote
  #6  
Old 06-14-2010, 07:28 AM
Brandi Brandi is offline custom toolbar with buttons to open new docs based on templates Windows XP custom toolbar with buttons to open new docs based on templates Office 2003
Novice
custom toolbar with buttons to open new docs based on templates
 
Join Date: Jun 2010
Posts: 13
Brandi is on a distinguished road
Default

Quote:
Originally Posted by Kimberly View Post
The (ABC Merge button) doesn't complete the merge, it only lets you see what it would look like merged.

Re toolbar button: In the Customize dialog box, Commands tab, select Macro... near the bottom of Categories list. After you add it to a toolbar, you can use the Modify button to make it look better.

Your macro to create the doc from a dot looks good.

So are you saying that it is not possible to automate the merge step so that the user of the template sees the document without the field codes?
I am getting a "requested object is not available" message when I execute my macro included below.

Sub Petition()
'
' Petition Macro
' Macro recorded 6/14/2010
'
Documents.Add Template:= _
"C:\Bergemann\Forfeiture\WordMailMerge\Petition.do t", NewTemplate:=False, _
DocumentType:=0
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub

If I perform the exact same steps while not recording a macro, my template opens up a new document as expected and the merge data button is available. If I record these exact same steps, I get the "requested object is not available" debug on the last statement of my code (ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle) which should merge the data into the document. The merge button is greyed out and not available to do manually either when I run my macro.

Thanks.
Brandi
Reply With Quote
  #7  
Old 06-14-2010, 07:58 AM
Kimberly Kimberly is offline custom toolbar with buttons to open new docs based on templates Windows 7 custom toolbar with buttons to open new docs based on templates Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

"(ActiveDocument.MailMerge.ViewMailMergeFieldC odes = wdToggle) which should merge the data into the document."
No, it shouldn't. Again, the ABC button does not perform a merge. It only previews. I don't know why that is giving you an error though, but it isn't supposed to merge.

I am not clear... is it true that you have templates that contain merge fields and you want to create a macro that creates a new doc based on the dot and then completes the merge to a new doc?
If so, I would expect your macro to resemble:
Code:
Sub NewDocAndMerge()
'
' NewDocAndMerge Macro
' Macro recorded 6/14/2010 by kimberly
'
    Documents.Add Template:= _
        "C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates\mergetest.dot" _
        , NewTemplate:=False, DocumentType:=0
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End Sub
Reply With Quote
  #8  
Old 06-14-2010, 08:02 AM
Kimberly Kimberly is offline custom toolbar with buttons to open new docs based on templates Windows 7 custom toolbar with buttons to open new docs based on templates Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

No, I am not saying you cannot hide field codes. I am saying that hiding the field codes and performing the merge are not the same thing.

Besides... if your macro creates the doc and then does the merge, the field codes don't have time to display!

Am I missing something? Why would field codes display?
Reply With Quote
  #9  
Old 06-14-2010, 09:19 AM
Brandi Brandi is offline custom toolbar with buttons to open new docs based on templates Windows XP custom toolbar with buttons to open new docs based on templates Office 2003
Novice
custom toolbar with buttons to open new docs based on templates
 
Join Date: Jun 2010
Posts: 13
Brandi is on a distinguished road
Default

Quote:
Originally Posted by Kimberly View Post
No, I am not saying you cannot hide field codes. I am saying that hiding the field codes and performing the merge are not the same thing.

Besides... if your macro creates the doc and then does the merge, the field codes don't have time to display!

Am I missing something? Why would field codes display?
I think I am posting before I am seeing your responses. I tried the code that you suggested above and now it is stopping me on the following line.
.Destination = wdSendToNewDocument

and says "requested object is not available".

Yes, I am trying to have the template open up a new doc with the merged data displayed on the document. Instead, it is opening up a new doc and telling me the requested data is not available and therefore displays the document with field codes before it is merged.
The data source is correct and as I mentioned above, the process works correctly if I manually open the template. Any ideas?

Thank you.
Brandi
Reply With Quote
  #10  
Old 06-14-2010, 09:38 AM
Kimberly Kimberly is offline custom toolbar with buttons to open new docs based on templates Windows 7 custom toolbar with buttons to open new docs based on templates Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Your code is just like mine, and does NOT include "ActiveDocument. MailMerge.Data Source.Included"
Right?

And you are sure the template is already connected to the data source before it was saved, right?
Reply With Quote
  #11  
Old 06-14-2010, 09:43 AM
Brandi Brandi is offline custom toolbar with buttons to open new docs based on templates Windows XP custom toolbar with buttons to open new docs based on templates Office 2003
Novice
custom toolbar with buttons to open new docs based on templates
 
Join Date: Jun 2010
Posts: 13
Brandi is on a distinguished road
Default

OK. Maybe I am not understanding the capabilities of Mail Merge. My application is to create legal documents that draw data from an Access database. The access database creates a table (with one record in it) that is the data source for my mail merge document. My users need to modify the document and save it as a Word doc.
The next time they want to print the document the information in the linked table will have changed.

I was hoping that the Merge to New Document command would create a document that could be saved and not continue to ask for the link to the data source.

When I am opening my template manually, I get a message about "opening this document will run the following SQL command" as I would expect as it is linking to the data source. When I open the template via a recorded macro, I do not get this message and instead "requested data source is not available".

If I open the template manually, and the document is linked to the data source and then I choose Merge to New Document, current record, I want to be able to save that document and no longer have it request a link to the data source. If I can't do this, I don't know where to turn next. The data source will constantly be changing. I want to be able to open the mail merge document and save it with the source at that point in time and break the link so that the user can save the information as a normal document.

Is this possible?

Thank you so much for your help.
Brandi
Reply With Quote
  #12  
Old 06-14-2010, 10:10 AM
Kimberly Kimberly is offline custom toolbar with buttons to open new docs based on templates Windows 7 custom toolbar with buttons to open new docs based on templates Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Brandi, certainly what you want is possible. But I am still not clear.

Is your goal simply to create a file that is linked to an Access and won't pester the user for the data source?

OR

Is your goal to create an automated process that will perform a mail merge using a template and an Access table?

Also, you keep saying you are "opening" a template. Are your really opening a template?
Reply With Quote
  #13  
Old 06-14-2010, 11:07 AM
Kimberly Kimberly is offline custom toolbar with buttons to open new docs based on templates Windows 7 custom toolbar with buttons to open new docs based on templates Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Please post your code.
Reply With Quote
  #14  
Old 06-14-2010, 11:21 AM
Brandi Brandi is offline custom toolbar with buttons to open new docs based on templates Windows XP custom toolbar with buttons to open new docs based on templates Office 2003
Novice
custom toolbar with buttons to open new docs based on templates
 
Join Date: Jun 2010
Posts: 13
Brandi is on a distinguished road
Default

Quote:
Originally Posted by Kimberly View Post
Brandi, certainly what you want is possible. But I am still not clear.

Is your goal simply to create a file that is linked to an Access and won't pester the user for the data source?

OR

Is your goal to create an automated process that will perform a mail merge using a template and an Access table?

Also, you keep saying you are "opening" a template. Are your really opening a template?
My goal is to create an automated process that will perform a mail merge using a template and an Access table?

The data source for the template will always be a table in Access for example tblPetition. The user selects a Defendant in the Access database and Access performs some complicated procedures to gather and calculate all of the information necessary to print in a document. We are unable to work with the document directly in Access because the user needs to be able to modify the document once it is created and save the document as a Word document. Therefore, we created Word mail merge templates to create a number of different legal documents. Each document has its own table in Access that is the datasource for a Word mail merge template.

Before I worry about why the macro isn't picking up a data source, I need to figure out how to save a Word document from the merged data that isn't always looking back to the data source as it will always be changing.

Example: In Access I have chosen Mickey Mouse and a table is created with all of the information about Micky Mouse. Then I double click my Word template (Petition.dot) (This is the only way I know to "open a document based on a template") and a Word document opens based on that template. The document is linked to this table and displays all of the inserted field information in the appropriate places to create a document about Mickey Mouse. Then I choose Merge to a New document, current record and save the document as MickeyMouse.doc where the user may make further modifications. When I test this now, and open MickeyMouse.doc after it has been saved, it still wants to open the SQL data source. I don't want the saved document to be linked to a data source because the record in the data source will constantly be changing. I may need to create a document about Donald Duck next.

So, my first question is when you save a new document that was created from a mail merge template, why would it ask me for the source each time? I want it to save the document with the actual data and no longer look for a link. Is that possible?

Thank you.
Brandi
Reply With Quote
  #15  
Old 06-14-2010, 12:02 PM
Kimberly Kimberly is offline custom toolbar with buttons to open new docs based on templates Windows 7 custom toolbar with buttons to open new docs based on templates Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Quote:
So, my first question is when you save a new document that was created from a mail merge template, why would it ask me for the source each time?
It shouldn't! It seems like you are not saving the finished document, but instead saved the merge doc underneath....

When you 'merge to a new document', it makes a new, finished document that is not linked to the data source. The file that is still linked to the data source is underneath the finished document. If you have your Show/Hide turned on, you can see the section breaks in the finished document.

When you double-click on a template, you are opening the template (which you would only do when you are not happy with the template and wish to edit it for future use), not making a document based on the template. Opening a template to use it eliminates the point of having a template.



Please do this:
Either verify that you have a template that is connected to the data source and includes merge fields. Then close all instances of Word completely and then open Word and use the code I provided (edited to change the path to the template), to make a macro,


or create a new template and write your own macro

Here are the steps...
  1. Close all instances of Word completely
  2. Open Word
  3. Create a new document
  4. Start a mail merge... connect to the data source and insert the merge fields. You can preview them if desired, but don't complete the merge
  5. File > Save as.. document template... Word will want to save this in the Templates folder. Do so for now, you can move it later.
  6. Close the template
  7. Record a macro... do these steps while recording:
  8. File > New > Templates on My Computer
  9. Double-click on your new template - now you might have to say OK to the SQL dialog box
  10. Click Merge to New Document button
  11. OK to the all records dialog box
  12. Stop Recording
  13. Close everything that is open
  14. Run the macro to test it
After the macro has run, save the file that is on top. Close without saving the other file.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
custom toolbar with buttons to open new docs based on templates Default Custom Dictionary Based on Document PatrickYork Word 3 04-03-2015 11:19 PM
Dock a custom floating toolbar? itgoeson PowerPoint 0 05-25-2010 08:34 AM
Docs open under formatting toolbars clive Word 0 01-22-2010 06:26 PM
command buttons ronf Excel 0 04-28-2006 08:32 AM
command buttons ronf Excel 0 12-03-2005 06:26 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft