Export reports to MS Word using VBA

[Guest]

Hello Experts and Power Users

I need your help. I administer a mystery shop program and I do monthly
reporting. Each month I have to run one report for each region shopped. There
are 40 regions therefore I run the report 40 times and name/export to rtf 40
times. I created a query that gives me the current regions shopped.

Question 1: Can I use VBA to run the report for each regionID in result
query records? I use an input box to enter the valid regionID for the time
period

Question 2: Can I use VBA to export the report as a rtf file? Currently I
'plug-n-chug' the export/naming process.

Hopefully this is possible. Thanks for your help in advance!

 

[Roger Carlson]

Sure. You should create a table with each region in a separate record.
Call it tblRegions. You could have the region name or whatever in it, but
one field it must have is RegionID. Base your report on a query called
NewQuery (actually, it can be anything you want, but in the example below,
it's called NewQuery). Then you'll need some code to create your query over
again, once for each region, using the values in the tblRegion table. Once
you've created the query, you Output your report to a file in the RTF
format. The code would look something like this:

'*********************************
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rsCriteria As Recordset

Set db = CurrentDb
Set rsCriteria = db.OpenRecordset("tblRegions", dbOpenDynaset)

'*** the first record in the Criteria table ***
rsCriteria.MoveFirst

'*** loop to move through the records in Criteria table
Do Until rsCriteria.EOF
'*** create the Select query based on
' the first record in the Criteria table
strSQL = "SELECT * FROM MainTable WHERE "
strSQL = strSQL & "[RegionID] = " & rsCriteria![RegionID]

'*** delete the previous query
db.QueryDefs.Delete "NewQuery"
Set qdf = db.CreateQueryDef("NewQuery", strSQL)
DoCmd.OutputTo acReport, "rptGLTable", "RichTextFormat(*.rtf)",
"C:\GLTable.rtf", False
rsCriteria.MoveNext
Loop
'**************************
Of course, you will substitute your actual query SQL statement, query name,
table name, file name, and so forth into the above code, but that's the gist
of it.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L