• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 717
  • Last Modified:

Access/Word integration

For a lot of the access reports that I generate, I have to integrate with Word documents. up to now I have achieved this by opening a new document based ono a template and then doing searc/replace for bookmarks within the template. eg:

    If Dir$(sDir, vbDirectory) = "" Then MkDir sDir
    Set oApp = CreateObject("word.application")
    oApp.Visible = True
    If Dir(sFilename) = "" Then
      NewWP = 1
      Set oApp = CreateObject("word.basic")
      With oApp
        .filenew template:=sTemplateName
        ' Find and replace bookmarks
        .editbookmark Name:="zwpnoz", Goto:=True
        .insert (Trim$(Me.WPNo))
        .editbookmark Name:="zwptitlez", Goto:=True
        .insert (Me.WPTitle)
        .editbookmark Name:="zprojtitlez", Goto:=True
        .insert (Forms![sys master form]![Text182])
        .editbookmark Name:="zwpmanagerz", Goto:=True
        If IsNull(Me.WPManager) Then
          .insert ("Unspecified")
          .insert (Me.WPManager)
        End If


I now have a report where some of the data will come from subrecords.  I don't know how many there will be, but the Word template will only allow for four subrecords.  

How do other people deal with this scenario?
If it wasn't for the need to use the Word template I would have resorted to a report/subreport.


  • 4
  • 4
1 Solution

Ive been addressing a similar question, maybe some of the insights gained so far might be useful.

Check this link:

Hmm, I used a table with a headerrow and one detail line with <None> in the word document to add as many rows as I would like.
Just define a table and use something like:

Set rs = CurrentDb.OpenRecordset("select * from tblTable where [systemdate] between format(#" & dtStart & "#,'mm/dd/yyyy') and format(#" & dtEnd & "#,'mm/dd/yyyy');")

If rs.EOF And rs.BOF Then
  doc.Tables(3).Cell(2, 1).Range.InsertAfter Text:="<None>"
  GoTo exit_table3
End If
'force correct recordcount by the movelast

For intI = 0 To rs.RecordCount - 1
    doc.Tables(3).Cell(intI + 2, 1).Range.InsertAfter Text:=rs.Fields(1).Value
    doc.Tables(3).Cell(intI + 2, 2).Range.InsertAfter Text:=rs.Fields(2).Value
    doc.Tables(3).Cell(intI + 2, 3).Range.InsertAfter Text:=rs.Fields(3).Value

'Remove last dummy row

rsclarkAuthor Commented:
I knew you would come up with a solution that would be different to mine - you definitely think out of the box!

I'm not that hot on the VB side, so how would I combine your bits with my existing bookmark search/replace? Would I have to put your code in the corresponding part of my code, or could it go after all of my search/replaces (even though my table is in the middle of the page in my word document).
Also, in your code I assume that the Table(3) refers to the third table in the word document; how do I find the table number in Word?
Any other hints/tips to ease my burdenin integrating this would be appreciated!

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Just count the number of tables used. I'm not sure or it starts with 0 or 1, but you'll notice it when it doesn't work :-)
Where you place the code isn't of any significance, just the table definition is needed (I also use a replace and event the fill of tables behind graphs in the same module) only make sure the number of columns and the header row is correct.


rsclarkAuthor Commented:
Sorry to bother you again, but I haven't managed to get this to work.  No doubt prooving my ingorance, I don't know how I get your doc. to match up with my oApp - can you explain?
I use the Word application like:

Dim doc as Word.Document

If acbIsWordLoaded Then
   Set aplWord = Word.Application
   Set aplWord = New Word.Application
End If

Set doc = aplWord.Documents.Open("[MonthlyReportsPath]" & "\" & "template.doc")

rsclarkAuthor Commented:
After a lengthy weekend spent on trying to find code for each of the stages in this task, I have finally managed to get this to work!!!

My Word template has a table with two rows, the header row and then a row for the data.

My code now looks like this:

Dim sFilename As String
Dim sDir As String
Dim sTemplatename As String
Dim strSQL As String
Dim puname As String
Dim tmpstring As String
Dim inti As Integer
Dim dbs As DAO.Database
Dim wordobj As Object
    sTemplatename = "\\support1\projdb\projtemplates\CR.dot"
    sDir = "\\support1\documents\projects\plans_etc\" & Left$(LTrim$(Forms![sys master form]!RequestNumber), 2) & "xxdocs\" & Forms![sys master form].RequestNumber
    sFilename = "\\support1\documents\projects\plans_etc\" & Left$(LTrim$(Forms![sys master form]!RequestNumber), 2) & "xxdocs\" & Forms![sys master form].RequestNumber & "\CR-" & Mid$(Me.CRDate, 7, 4) & Mid$(Me.CRDate, 4, 2) & Mid$(Me.CRDate, 1, 2) & ".doc"

    'Check directory, if not exist then use mkdir function to create directory.
    If Dir$(sDir, vbDirectory) = "" Then MkDir sDir
    Set wrdobj = CreateObject("word.application")
    If Dir(sFilename) = "" Then
      ' File does not exist
      ' Create it based on the template
      wrdobj.Documents.Add sTemplatename
      With wrdobj
        ' Search/replace bookmarks
        .Selection.Text = Trim$((Forms![sys master form]![RequestNo]))
        tmpstring = DLookup("[requestTitle]", "wbc_wf_projectRequest", "requestno= " & Forms![sys master form]!RequestNo)
        .Selection.Text = tmpstring
        .Selection.Text = Trim$((Me.OnTarget))

' etc for the rest of the bookmarks        
      End With
      ' Risks
      ' Open recordset for Risks details
      Set rs = CurrentDb.OpenRecordset("select * from [qry open risks] where [request]  = " & Forms![sys master form]![RequestNo] & ";")
      'Force position
      ' Write details info into table in Word Document
      For inti = 0 To rs.RecordCount - 1
        wrdobj.ActiveDocument.Tables(8).Cell(inti + 2, 2).Range = Trim$(rs.Fields(3).Value & "")
        wrdobj.ActiveDocument.Tables(8).Cell(inti + 2, 3).Range = Trim$(rs.Fields(4).Value & "")
        wrdobj.ActiveDocument.Tables(8).Cell(inti + 2, 4).Range = Trim$(rs.Fields(5).Value & "")
        wrdobj.ActiveDocument.Tables(8).Cell(inti + 2, 5).Range = Trim$(rs.Fields(6).Value & "")
        If rs.EOF Then Exit For
      inti = wrdobj.ActiveDocument.Tables(8).Rows.Count
      ' Merge all cells in Column 1 as this is the title of the table
      With wrdobj.ActiveDocument.Tables(8)
        .Cell(1, 1).Merge (.Cell(initi, 1))
      End With
      ' Processing complete, Save the document
      wrdobj.ActiveDocument.SaveAs sFilename
      ' Document aready exsists, so open
      wrdobj.Documents.Open sFilename
    End If
    wrdobj.Visible = True
In my references I have:
Visual Basic for Application
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Date Objects 2.1 Library
Microsoft Word 9.0 Word Library
Microsoft DAO 3.6 Object Library



Glad it worked out, well done Robert !

BTW I did have some trouble in the past when the references were once in another sequence (I believe Excel before Word), just a tip for you :-)

rsclarkAuthor Commented:
Points awarded, more for taking the time and effort to look at my problem (and coming up with an inovative solution!).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now