Export Access Data To predefined Word Document
I have a table of 1 row and about 41 fields of data. I need to use this data to populate a predefined 7 page Word document. Each field needs to go to a specific spot on each page.
For example fields 1-2-3 need to go to page 1 at the top middle and end of the page. Fields 4-5-6 need to go to page 2 to specific spots on that page and so on and so forth. Can anyone point me in the direction of how to accomplish this. Like examples in here or on the internet where I can get a start on figuring this out. I'm pretty familiar with access and code and exporting however I'm dumb as a box of rocks when it comes to Word. I know I'm suppose to name the spaces that look like this <Account Name> on the predefined form but have no clue how to do that.
For example fields 1-2-3 need to go to page 1 at the top middle and end of the page. Fields 4-5-6 need to go to page 2 to specific spots on that page and so on and so forth. Can anyone point me in the direction of how to accomplish this. Like examples in here or on the internet where I can get a start on figuring this out. I'm pretty familiar with access and code and exporting however I'm dumb as a box of rocks when it comes to Word. I know I'm suppose to name the spaces that look like this <Account Name> on the predefined form but have no clue how to do that.
Agreed.
What you are looking to do is called a Mail Merge. The how-to's for that vary depending on W2003, 2007, 2010 or 2103. Pick your version.
You'd start by basing your merge off your existing document, and you would insert Merge Fields at the appropriate places. If you are able to post samples, that would be helpful.
One hates to say it, but Google "Mail Merge Word" and whatever version you are using. Formatting Merge Fields (dates!) can be a bitch. Otherwise it is fairly straight-forward.
What you are looking to do is called a Mail Merge. The how-to's for that vary depending on W2003, 2007, 2010 or 2103. Pick your version.
You'd start by basing your merge off your existing document, and you would insert Merge Fields at the appropriate places. If you are able to post samples, that would be helpful.
One hates to say it, but Google "Mail Merge Word" and whatever version you are using. Formatting Merge Fields (dates!) can be a bitch. Otherwise it is fairly straight-forward.
Formatting is more of a switch than a bitch.
The main thing to remember is that the display formatting in the source application (Access in this case) in irrelevant. Only the underlying data is transferred.
The unwanted default formatting that might appear is usually confined to dates and numbers (including currency). There are numeric and date formatting switches than can be added to individual MERGEFIELD locations on the Word document if necessary.
I have included some brief notes in this article:
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/A_12275-Formatting-Mail-Merge-Fields.html
The main thing to remember is that the display formatting in the source application (Access in this case) in irrelevant. Only the underlying data is transferred.
The unwanted default formatting that might appear is usually confined to dates and numbers (including currency). There are numeric and date formatting switches than can be added to individual MERGEFIELD locations on the Word document if necessary.
I have included some brief notes in this article:
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/A_12275-Formatting-Mail-Merge-Fields.html
Formatting is more of a switch than a bitchFinding the documentation for said switches and getting the syntax right is ... interesting... and not particularly intuitive coming from an Access/VBA background.
I get my desired Medium Date format -- but working out that \@"dd-MMM-yy" is what has to be appended (and is case-sensitive) to the toggled merge field isn't fun. Now that I've got it worked out, I copy-and-paste it. Until today, I've never gotten Word help to display any meaningful help about merge field switches. Probably because I've always searched 'merge field switches.' Today I searched 'field switches' and lo! and behold the 'Date-Time Picture (@) field switch' topic was useful.
But would a newbie know that was what they were looking for?
:)
Interesting @GrahamSkan.
I tried your link.
Got a big, ugly Permission Denied page
I tried your link.
Got a big, ugly Permission Denied page
ASKER
I am trying to run a process from an access button that will send a one record table to word to send the 40 fields to specific spots per page exp Field 1 to pg1 top Field 2 to page 1 middle etc etc to pages 3-4-5-6 etc I have the access part done but dont know how to set up the word doc when I try insert table its all very confusing in how to identify each spot as a field receptor. Attached is a small example of the form.
ASKER
here is the attachment
WordDoc.JPG
WordDoc.JPG
It is always difficult to make accurate assumptions about a document from just a picture, but your target locations seem to be simply place markers: i.e. text in the document that has to be found and replaced with some other text. Merge fields look more like <<MyMergeField>>
To do this automatically would require some VBA coding. Do you feel confident with that?
To do this automatically would require some VBA coding. Do you feel confident with that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Other commentators have pointed to Word's Mail Merge Wizard. You can still try that, and replace the placeholders with merge fields.
It will be helpful to note what version of Office you are using. The wizards and toolbar/Ribbon look radically different from version to version. Supplying you screenshots will be of little use if you are using a different version than those Answering.
ASKER
I appreciate the mini DB - it helped but the key was I didn't know you had to run a mail merge wizard from Acccess first to get the fields over to Word - I thought i was the other way around. Once I ran the access word merge then opened the template and went thru that mail merge as suggested and found the fields from access to input it was simple from then on - thanks everyone for all you help this helps me complete my project.
Ah! Version differences.
In O2003, you run the merge wizard on the Word side. It first asks you for a datasource and you point it at the appropriate Access file and table/query, and go from there. What version of Office are you using?
Another IMPORTANT gotcha to remember. When you open the Word document, it wants to open the Access database EXCLUSIVELY. If someone else has it open, Word will barf. If someone else tries to open the Access database while Word is running, Access will probably barf.
In O2003, you run the merge wizard on the Word side. It first asks you for a datasource and you point it at the appropriate Access file and table/query, and go from there. What version of Office are you using?
Another IMPORTANT gotcha to remember. When you open the Word document, it wants to open the Access database EXCLUSIVELY. If someone else has it open, Word will barf. If someone else tries to open the Access database while Word is running, Access will probably barf.
ASKER
we just converted to 2010 which is pretty awesome - just hard getting use to from the old 2003. This will be an independent database that the user will use for their particular clients, so shouldn't be an issue. I plan on adding the code to basically automate the merge from access and just save the files to pdf's which is another nice add on with 2010. Thanks for all your help!
Select
Tools
Letters and Mailings
Mail Merge
Follow the wizard-
Document type-Letter
Select data source-Your database and table
When prompted to write your letter, Choose More Items to select a given field.
SAve early and often