Solved

Access/Word integration

Posted on 2004-03-24
9
702 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



0
Comment
Question by:rsclark
  • 4
  • 4
9 Comments
 
LVL 1

Expert Comment

by:Evolve2k
ID: 10683466
Hi,

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

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

0
 
LVL 54

Accepted Solution

by:
nico5038 earned 100 total points
ID: 10703493
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
rs.MoveLast
rs.MoveFirst

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
    doc.Tables(3).Rows.Add
    rs.MoveNext
Next

'Remove last dummy row
doc.Tables(3).Rows(doc.Tables(3).Rows.Count).Delete

Nic;o)
0
 
LVL 2

Author Comment

by:rsclark
ID: 10704306
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
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 54

Expert Comment

by:nico5038
ID: 10704626
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)

0
 
LVL 2

Author Comment

by:rsclark
ID: 10722472
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10722544
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)
0
 
LVL 2

Author Comment

by:rsclark
ID: 10755650
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





0
 
LVL 54

Expert Comment

by:nico5038
ID: 10759696
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)
0
 
LVL 2

Author Comment

by:rsclark
ID: 10763807
Points awarded, more for taking the time and effort to look at my problem (and coming up with an inovative solution!).
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Attachment field in SQL 3 29
Calculation in Access 5 29
Batch Export Reports (with multiple parameters) As PDF 2 26
Progress bar in access form 11 26
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question