Any new way to get Access to Word with formatting

[MN88]

I am well aware of the limitations of Access-> RTF. I always explain it to my customers. However, one customer now has required standards that just cannot be met. I thought maybe Total Access Memo would work, but it doesn't seem to be able to export what it creates to Word either. Are there any 3rd party tools? Any new ways to do this - or am I stuck doing mail merge templates for all of these? yuk!

Thanks

 

[isladogs]

Does the exported file need to be editable?

As you said, exporting to RTF is usually rubbish.
You could try exporting to PDF then converting back to docx but again it won't be perfect.
Another possibility is exporting to HTML and opening in Word. That may give reasonable results

I usually use Albert Kallal's Super Easy Mail Merge. The advantage is that its all managed from Access.

 

[MN88]

Yes, it has to be editable. It is for a court and they fill stuff out. I tried pdf -> docx but it is bad. I think I tried the html approach at one point but will revisit. These are highly formatted unfortunately. I will look at that mail Merge program. I have done these using Word MM and buttons from Access and all and the users still get it messed up somehow. I had high hopes for Total Access Memo - very disappointed. Thanks!

 

[MN88]

Does the exported file need to be editable?

As you said, exporting to RTF is usually rubbish.
You could try exporting to PDF then converting back to docx but again it won't be perfect.
Another possibility is exporting to HTML and opening in Word. That may give reasonable results

I usually use Albert Kallal's Super Easy Mail Merge. The advantage is that its all managed from Access.

Any chance you have current link or sample code for Super Easy Mail Merge? Everything i see is old. This is for Access 365. Thanks!

 

[isladogs]

Never tried Total Access Memo.
I do strongly recommend Albert's approach to mail merge. Its been available for about 15 years and you should be able to find it from various sources. Don't be put off by Albert's very antiquated site. The code still works perfectly in A365.

Here's one link Super Easy Word Merge - Access Tutorials, TIPs, Examples (hitechcoach.com)
I think it was last updated back in 2009

 

[theDBguy]

Hi. Welcome to AWF!

Mail merge is probably easier than this, have you tried using Word Automation where you would basically create a formatted Word document from Access using VBA?

 

[Pat Hartman]

What, exactly, is the problem with your export to RTF? Automating Word is like playing pin the tail on the donkey. They blindfold you, spin you around a few times and you have no idea where to pin that tail I have created RTF files from COBOL and it really isn't hard. However, in COBOL, I was formatting the report myself. In your case Access is doing the formatting. To get what you want (which we don't even know), you would need to produce the report completely in VBA so you could format each part as you exported it.

Although, you could let Access do the formatting and put your RTF code in the format event of each section. You would open the file in the report's Open event using normal Access print methods. RTF is a plain text file format so this process would not use Word at all to create the file. So the format event of the header section creates the header and so you would write the header including its formatting. The format event of the detail section creates one detail at a time so you would write that with formatting, etc. You wouldn't actually be using the formatting of the report. You would only be using the report events to guide in the creation of each specific line. As a programmer, I wouldn't do this since I am capable of writing the report logic from scratch but if you have never done that, this is a method that lets Access guide the logic since it knows when to write a new line and it knows when to write a new page.

I dislike mail merge. It is convoluted and limited in what it can do. As theDBguy said, automation is far better but again, we need to know what the problem is. Typically with either mail merge or automation, you have a template document and have bookmarks where you place specific data. This works great for a letter but it is clumsy with a report because the report almost certainly has a repeating section which you can't actually create a template for. I created a sample app that shows Word automation that includes building a "table" and inserting it at a bookmark. This is how you would get the repeating stuff in the document. You cannot do this with mail merge. Mail merge processes only a single record per document.

 

[The_Doc_Man]

I have been able to build documents straight from Access to a Word .DOCX file. It was SO unlovely a process that I would hesitate to advise it unless you claim shared bloodline with Donatien Alphonse Francois, Marquis de Sade or with Leopold Sader-Masoch. Then you might enjoy it. The problem with Word, in a nutshell, is that it is difficult verging on impossible to isolate stuff that you might wish to manipulate in a certain way like highlight, underscore, change case, italicize, etc. "Meticulous" is too loose a word for the kind of work you REALLY need to do with Access/Word App objects.

IF you wanted to do ANYTHING except simple formatting and pagination control, doing Word documents via an Application Object is very close to a total pain in the patootie and is fraught with pitfalls. If you can tell us at a high-to-medium level what you want to do, maybe ... MAYBE there is is way. However, the Word Document Object Model starts by simply being confusing and gets worse from there because everything overlaps everything else. For instance, creating a table or document to go in a specific place can get really tough.

If you are trying to develop a complex legal document, all I can say is "Ouch."

 

[MajP]

I may be an outlier here but I disagree that the Word object model is as painful as portrayed. However, I am pretty conversant in all things VBA (Word, Outlook, Access, Excel, Power Point, Project) so my opinion may be different than others here.
IMO the Word Object module is pretty extensive with lots of ways (sometimes too many) to do the same thing, and some of these are a little confusing. However the 90% of what you often need to do is pretty straight forward. The object model is very well documented.

Word object model for Visual Basic for Applications (VBA)

This section of the Word VBA Reference contains documentation for all the objects, properties, methods, and events contained in the Word object model.

docs.microsoft.com

IMO it is in many ways like Excel. It is a big Range object. I do not code that often in Word, but the times I do I can find most answers on the MS site or another site pretty easily. So I would not let this doom and gloom dissuade you, if you are willing to do some reading up. Like anything VBA there are tons of posted code on line.

If it is a highly formatted document, it may be cumbersome to format everything from Access. In this case I do it backwards. I pull the data instead of pushing it. I build a Word VBA enabled template (.dotm). I format the template and place 95% of the code in the template. I may have lots of bookmarks and named ranges to aid in formatting and inputting data. Then I launch the template from Access and the Autoexec of the template kicks off the code. The template "pulls" the data from the database and creates itself. Since it is a template, you cannot easily overwrite this and it creates a new document. I have not done this for a while, but we created an application with many complicated reports/documents. Most of these documents had multiple tables, and numerous references and hyperlinks.

This is a little more involved than some of the other methods described, but if it is something with a lot of details then IMO this is the way to go.

 

[MajP]

Here is a simple example filling a single table that makes up a pretty highly formatted military form. In this example you click on the template and it prompts you for the database. Then it makes the connection and fills out the form. This was a demo for a thread someone posted. I do not remember how I got all those form fields. I am sure I am too lazy to have created that manually, and must have done it in code. This is not a working solution but a partial demo of the concept. I fill in the header and 22 line items. I do not clean up and blank out the unused fields and remove the form field highlighting.

 

[The_Doc_Man]

If it is a highly formatted document, it may be cumbersome to format everything from Access.

Perhaps I may have been overly negative, but I fully and absolutely agree with this choice of words about Word.

Although there is a phrase, "Consistency is the hobgoblin of little minds," in the case of using Access to drive a word App, a consistent approach is the only way to do this without going crazy. Here is a thread where I discuss what I did that finally gave me mostly what I wanted.

Solved - What is the correct way to linearly insert text into Word?

I have searched this site and many others. Web searches galore, but nothing seems to work quite right for me. I am making a document linearly. I.e. providing headers and text and bookmarks and other material in the sequence that I would expect them to be read. But it ain't working. I think...

www.access-programmers.co.uk

 

[MajP]

I will probably write a complete FAQ on doing this, because there seems to be a lot of interest lately in using word as a report writer. In this recent thread I do it the other way around. I open a predefined word template and push some information into the table. This is a very simplistic example, but demonstrates the basics of getting a reference to an already open Word document, or if not open then uses a file browser to open it.
Shows how to reference a table and the cells in the table and push information from Access. You will see that the code is far from daunting.

Copying currency values from Access to paste into Word

Our computer system has a variety of Word templates with prompts for currency values to be entered one at a time to populate a grid within the letter. The users have been using an Excel spreadsheet to calculate the values and then selected the group of cells and pasted them into the Word...

www.access-programmers.co.uk

This is all the basics you need to get started. If you want to do much more complicated formatting and inserts, then it just requires some time learning the Word object model.

 

[isladogs]

@MN88
You might find some useful info that's new to you in this e-book by Helen Feddema:
Access Archon: Working with WORD - Office Watch (office-watch.com)