Using Access to dynamically create Word document

[panzer73]

Is it possible to use Access to dynamically create a Word document? Can someone give me an overview of how this is done?

I have a client who currently uses a huge excel document and basically mail merges the info into various word documents. They have a cumbersome process, so we are going to try to create an access database for them to manage the info that was in the excel document, but they need to be able to edit the reports that they create, so we can't just use the standard Access reports.

Ideally, they would like to view information on a specific job, click a button that would dynamically create a Word document with the various information filled in where it needs to be. Can we do this with Access?

 

[BrenoAguiar]

Panzer,

you can try the "Output to MsWord" option int he Tools\Office links menu. You can generate the report in Access and once you bring the report you can hit the output option and it will go in MSWord format.

 

[BrenoAguiar]

Sorry.. here is the code:

RunCommand acCmdOutputToWord

 

[panzer73]

I tried creating the report and I can export it to a Word document, but it lost some of my colorful formatting (they want some fields highlighted). Will the way that you mentioned allow me to create a button on my form and automatically dump the output to a word document?

 

[MajP]

You could put this right on the report's module:

Private Sub Report_Activate()
If MsgBox("Would you like to export the file to MS Word",
vbYesNo) = vbYes Then
'SyntaxoCmd.OutputTo objecttype[,objectname]
'[,outputformat][,outputfile][,autostart][,templatefile]
DoCmd.OutputTo acOutputReport, , acFormatRTF, , False
End If
End Sub

Now, you can only export to RTF format not DOC. You will be very lucky to get matching formatting. If you need detailed formatting, you will probably need to use automation and start coding Word objects.

 

[panzer73]

How would that work, would I use VB and create macros for my word document? I think they need the formatting to be fairly similar to what they have now with their mail-merge documents. One of our former employees had created an excel document like that that had a database source setup and pulled information from Access and displayed it in a spreadsheet.

Would I just have the Access form open up a specified Word document when the user clicks a button?

 

[MajP]

This is a huge area, and unless you are a good VB programmer and familiar with both Access and Word objects I would do some research first. There are a lot of add ins, free code, and utilities out there. Use the advance search feature of this site and search:
"snapshot viewer"
"PDF writer"
"using word as report writer"
"rtf utilities"
"report utilities"
"exporting reports to word"
etc. There are some good report utilities out there for free that may allow you to get a almost exact RTF format of your report.

If you feel compelled to code the following is my strategy, but there a multiple ways to skin the cat. I build word template files (.dot). I put the majority of my code in the template. I use the getObject function to open the template from excel and I pass it the applicable recordset. Then I manipulate the recordset to stick information in the right places. For me this makes updating a template much easier to distribute then containing the code in Access. The templates change more than the database.
Good Luck

 

[BoxHead]

Panzer73,

Another option (maybe): I have a form on which I pull data from a table. On the form, I can move the values around, edit the text, etc. I have the report that it feeds open along with the Forms onLoad event and there's a button on the form to "Refresh The Report". That command just closes and opens the report.

I use the Move method to place the report right next to the form and control the two windows' location and size.

Could something like that give you the editing capabilities you need and keep the formatting the way you need it?


HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[MajP]

To highlight the strategy I was talking about see
faq703-760