We help IT Professionals succeed at work.

Access/Word integration

rsclark
rsclark asked
on
779 Views
Last Modified: 2011-06-13
Hi
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")
        Else
          .insert (Me.WPManager)
        End If



etc


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.

Robert



Comment
Watch Question

Commented:
Hi,

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

Check this link:
https://www.experts-exchange.com/Databases/MS_Access/Q_20894072.html#10682737

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Nico
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!

Robert

Commented:
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.

Nic;o)

Author

Commented:
Nico
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?
Robert

Commented:
I use the Word application like:

Dim doc as Word.Document

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

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

Nic;o)

Author

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
        .ActiveDocument.Bookmarks("zrefz").select
        .Selection.Text = Trim$((Forms![sys master form]![RequestNo]))
       
        .ActiveDocument.Bookmarks("ztitlez").select
        tmpstring = DLookup("[requestTitle]", "wbc_wf_projectRequest", "requestno= " & Forms![sys master form]!RequestNo)
        .Selection.Text = tmpstring
       
        .ActiveDocument.Bookmarks("zTargetz").select
        .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
      rs.MoveLast
      rs.MoveFirst
     
      ' 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 & "")
       
        rs.MoveNext
        If rs.EOF Then Exit For
        wrdobj.ActiveDocument.Tables(8).Rows.Add
      Next
     
      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
   
    Else
      ' 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


Regards

Robert





Commented:
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 :-)

Nic;o)

Author

Commented:
Points awarded, more for taking the time and effort to look at my problem (and coming up with an inovative solution!).
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.