Solved

Access/Word integration

Posted on 2004-03-24
9
700 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 2

Author Comment

by:rsclark
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Points awarded, more for taking the time and effort to look at my problem (and coming up with an inovative solution!).
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now