Export Microsoft Access Query Results to Word with VBA
Question details
Create monthly Word reports from a parameterized Access query, placing each field in its own bookmark.

- Product
- Microsoft Access and Microsoft Word
- Device & OS
- not provided
- Scenario
- An Access VBA procedure reads query results through a DAO recordset and fills a Word report template.
- Observed behavior
- The existing procedure places data into one area and encounters VBA errors while generating reports.
Use desktop installations of Access and Word, and confirm that the saved query returns the fields Child, CMV1, CMV2, and CMV3. In the Word template, create separate bookmarks named Child, visit2, visit3, and visit4, and make sure the output folder already exists.
Create a separate Word document for each query record
Use a DAO QueryDef to supply query parameters and populate the corresponding Word bookmarks for each record.
This example assumes the saved query ChildVisit declares parameters named StartDate and EndDate. Adapt the parameter names and types to your query; for an exclusive end date, the query should filter dates with a condition such as VisitDate >= [StartDate] And VisitDate < [EndDate].
Creating a new document from the template for each record prevents later records from overwriting earlier ones. The helper procedure recreates each bookmark after inserting text because replacing a bookmark range can remove the bookmark.
In Access, press Alt+F11 and choose Tools > References. Select the Microsoft Word object library and a Microsoft Office Access database engine object library that provides DAO, then click OK.
In the VBA editor, choose Insert > Module and paste the following code. Replace the template and output paths with your own existing paths: Public Sub ExportNamesToWord() Const TEMPLATE_PATH As String = "C:\Reports\MonthlyReport.dotx" Const OUTPUT_FOLDER As String = "C:\Reports\Output\" Dim wApp As Word.Application Dim wDoc As Word.Document Dim qdf As DAO.QueryDef Dim rs As DAO.Recordset Dim startText As String, endText As String Dim outputPath As String, runId As String Dim recordNumber As Long startText = InputBox("Enter the start date:") endText = InputBox("Enter the exclusive end date:") If Not IsDate(startText) Or Not IsDate(endText) Then MsgBox "Enter valid dates." Exit Sub End If If CDate(endText) <= CDate(startText) Then MsgBox "The end date must be after the start date." Exit Sub End If On Error GoTo ExportError Set qdf = CurrentDb.QueryDefs("ChildVisit") qdf.Parameters("StartDate").Value = CDate(startText) qdf.Parameters("EndDate").Value = CDate(endText) Set rs = qdf.OpenRecordset(dbOpenSnapshot) Set wApp = New Word.Application runId = Format$(Now, "yyyymmdd_hhnnss") Do Until rs.EOF recordNumber = recordNumber + 1 Set wDoc = wApp.Documents.Add(Template:=TEMPLATE_PATH) PutBookmark wDoc, "Child", CStr(Nz(rs!Child, "")) PutBookmark wDoc, "visit2", CStr(Nz(rs!CMV1, "")) PutBookmark wDoc, "visit3", CStr(Nz(rs!CMV2, "")) PutBookmark wDoc, "visit4", CStr(Nz(rs!CMV3, "")) outputPath = OUTPUT_FOLDER & "MonthlyReport_" & runId & "_" & recordNumber & ".docx" If Len(Dir$(outputPath)) > 0 Then Err.Raise vbObjectError + 1, , "Output file already exists: " & outputPath wDoc.SaveAs2 FileName:=outputPath, FileFormat:=wdFormatXMLDocument wDoc.Close SaveChanges:=False Set wDoc = Nothing rs.MoveNext Loop MsgBox recordNumber & " report(s) created." GoTo CleanUp ExportError: MsgBox "Export failed: " & Err.Description CleanUp: On Error Resume Next If Not wDoc Is Nothing Then wDoc.Close SaveChanges:=False If Not wApp Is Nothing Then wApp.Quit If Not rs Is Nothing Then rs.Close Set wDoc = Nothing Set wApp = Nothing Set rs = Nothing Set qdf = Nothing End Sub Private Sub PutBookmark(ByVal doc As Word.Document, ByVal bookmarkName As String, ByVal value As String) Dim rng As Word.Range If Not doc.Bookmarks.Exists(bookmarkName) Then Err.Raise vbObjectError + 2, , "Missing Word bookmark: " & bookmarkName End If Set rng = doc.Bookmarks(bookmarkName).Range rng.Text = value doc.Bookmarks.Add Name:=bookmarkName, Range:=rng End Sub
Choose Debug > Compile in the VBA editor, correct any reported reference or naming errors, and run ExportNamesToWord. Enter the start date and exclusive end date when prompted, then open the generated DOCX files in the output folder.

Review exported Word reports in WPS Writer
WPS Office is a free, lightweight alternative for opening and editing exported Word reports. Its familiar interface and DOCX compatibility make it easy to move report documents between Office suites. The Access DAO and Word automation procedure itself still requires Microsoft Access and Word.

Frequently Asked Questions
Why does replacing bookmark text cause a later bookmark error?
Setting a Word bookmark's Range.Text can remove the bookmark. Retain the range and call doc.Bookmarks.Add with the same name after writing the text, as in the PutBookmark helper.
How do I keep each query record from overwriting the previous report?
Create a new document with Documents.Add for each record and use SaveAs2 with a different DOCX filename. The example adds a timestamp and record number to each filename.
What should I check if DAO.Recordset or Word.Application is not defined?
In the Access VBA editor, open Tools > References and select the DAO-providing Access database engine library and the Microsoft Word object library. Then choose Debug > Compile.
Why does the query prompt for a parameter or fail when opening?
Check the saved query's parameter names and field names in Access. Assign every declared parameter through the QueryDef Parameters collection before calling OpenRecordset, and correct any misspelled field names.
Can WPS Office run this Access-to-Word automation?
WPS Writer can open the resulting DOCX reports, but this procedure uses the Microsoft Access DAO engine and Microsoft Word's object library. Run the export in Access with Word installed.




