Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there syntax to output information to Word in such a way as it will go into a table? My client wants the info to be in a grid with lines. I don't have any problems outputting to Word using the Word object, I just want to get a handle on what is involved to put it in table form.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='PeterN' post='787551' date='04-Aug-2009 13:01']Is there syntax to output information to Word in such a way as it will go into a table? My client wants the info to be in a grid with lines. I don't have any problems outputting to Word using the Word object, I just want to get a handle on what is involved to put it in table form.[/quote]
    It is possible to do, but the automation code to do it is quite complex as you have to construct the table in VBA and then keep track of where you are with indexes and the like. If I were doing it, I would probably export the data to Excel and then paste the Excel cells into Word as a table. Neither approach is trivial, but it is definitely doable.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is an example. It uses the following variables:

    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim wrdTbl As Word.Table
    Dim c As Integer
    Dim n As Integer
    Dim r As Integer
    Dim rst As DAO.Recordset

    I will assume that wrdApp and wrdDoc have already been set, and that the recordset rst has already been opened. The following code creates a table in the document with as many columns as there are fields in the recordset. It fills the first row with the field names, then adds a row for each record.

    Code:
    n = rst.Fields.Count
    ' Create table
    Set wrdTbl = wrdDoc.Tables.Add(Range:=wrdApp.Selection.Range, NumRows:=1, NumColumns:=n)
    r = 1
    ' Fill the first row with field names
    For c = 1 To n
      wrdTbl.Cell(r, c).Range.Text = rst.Fields(c - 1).Name
    Next c
    ' Loop through the records
    Do While Not rst.EOF
      r = r + 1
      ' Add a row
      wrdTbl.Rows.Add
      ' Fill from record
      For c = 1 To n
    	wrdTbl.Cell(r, c).Range.Text = rst.Fields(c - 1).Value
      Next c
      rst.MoveNext
    Loop
    Followed by the usual cleanup (close the recordset etc.)

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks Hans. This should be enough to get me started. I'll post back if I run into any snags.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    I haven't done much Word programming recently but if you're driving the process from Word (not pushing data from Access) have a look at the InsertDatabase method of the Word Range object...

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='jeremybarker' post='787618' date='05-Aug-2009 04:17']I haven't done much Word programming recently but if you're driving the process from Word (not pushing data from Access) have a look at the InsertDatabase method of the Word Range object...[/quote]

    Thanks. I'll look at this. This particular process needs to be driven from inside Access.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787560' date='04-Aug-2009 15:41']Here is an example. It uses the following variables:

    Dim wrdApp As Word.Document
    Dim wrdDoc As Word.Document
    Dim wrdTbl As Word.Table
    Dim c As Integer
    Dim n As Integer
    Dim r As Integer
    Dim rst As DAO.Recordset[/quote]

    Hans, I was just looking at this. In your declarations your first one is wrdApp as Word.Document. Are both wrdApp and wrdDoc documents or should wrdApp be Word.Application? It certainly makes more sense to me given your naming of the variable.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='PeterN' post='788124' date='09-Aug-2009 04:29']Hans, I was just looking at this. In your declarations your first one is wrdApp as Word.Document. Are both wrdApp and wrdDoc documents or should wrdApp be Word.Application? It certainly makes more sense to me given your naming of the variable.[/quote]
    You are entirely correct. It should be

    Dim wrdApp As Word.Application

    I have corrected my reply. Thanks for pointing out my mistake!

  9. #9
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. I have got this far.

    The next thing I am trying to do is open a template and set the insertion point at a specific spot - bkmk1 (bookmark1) and then insert the table there.

    In fact, since I already know what the column headings will be, just not how many rows there will be, what I would ideally like to do is have a table set up in the template with the column headings already there and formatted then start adding the data into the second row (either create the second row and start adding from there or start at a blank second row and add in additional ones as needed). I assume this would only mean dumping the three lines of code that insert the field names.

    The problem I have with the current code is that it doesn't seem to actually go to the bookmark, or if it does, it then goes back to the beginning and inserts the table there. I am very limited in my understanding of the Word Object and ranges so I can only assume that
    Set wrdTbl = wrdDoc.Tables.Add(Range:=Selection.Range, NumRows:=1, NumColumns:=n)
    is going back to the beginning.

    Code:
    Option Compare Database
    Option Explicit
    Private Const m_strDIR		  As String = "C:\Documents and Settings\PN\My Documents\DataDump\"
    
    Public Sub WordClin()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim wrdTbl As Word.Table
    Dim c As Integer
    Dim n As Integer
    Dim r As Integer
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim dbs As DAO.Database
    Dim dteStart As Date
    Dim dteEnd As Date
    
    Set wrdApp = New Word.Application
    wrdApp.Documents.Open (m_strDIR & "NIS - Template.DOC")
    Set wrdDoc = Word.ActiveDocument
    	
    dteStart = #4/1/2009#
    dteEnd = #6/30/2009#
    
    Set dbs = CurrentDb()
       
    	Set qdf = dbs.QueryDefs("aaa")
    	qdf.Parameters("[forms]![frmISAPDates]![txtStartDate]") = dteStart
    	qdf.Parameters("[forms]![frmISAPDates]![txtEndDate]") = dteEnd
    
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    n = rst.Fields.Count
    
    wrdDoc.GoTo what:=wdGoToBookmark, Name:="Bkmk1"
    
    ' Create table
    
     Set wrdTbl = wrdDoc.Tables.Add(Range:=wrdApp.Selection.Range, NumRows:=1, NumColumns:=n)
    
    
    r = 1
    ' Fill the first row with field names
    For c = 1 To n
      wrdTbl.Cell(r, c).Range.Text = rst.Fields(c - 1).Name
    Next c
    ' Loop through the records
    Do While Not rst.EOF
      r = r + 1
      ' Add a row
      wrdTbl.Rows.Add
      ' Fill from record
      For c = 1 To n
    	wrdTbl.Cell(r, c).Range.Text = Nz(rst.Fields(c - 1).Value, 0)
      Next c
      rst.MoveNext
      Loop
    
    wrdDoc.SaveAs FileName:=m_strDIR & _
    		"NIS - " & FormatDateTime(Date, vbLongDate) & ".DOC"
    	wrdDoc.Close
    	wrdApp.Quit
    
    	 ' clean up
    	Set wrdDoc = Nothing
    	Set wrdApp = Nothing
    	rst.Close
    	Set rst = Nothing
    
    
    End Sub

  10. #10
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Never mind about the range question, I changed this
    Set wrdTbl = wrdDoc.Tables.Add(Range:=wrdApp.Selection.Range, NumRows:=1, NumColumns:=n)
    to this
    Set wrdTbl = wrdDoc.Tables.Add(Range:=ActiveDocument.Bookmarks( 1).Range, NumRows:=1, NumColumns:=n)
    Nuthin I hate worse than being defeated by VBA and obscure, circular help files

    However, I still would like to move on to the next bit which is adding data into a pre-existing table with headers.

    I assume now that this means the above solution will be redundant since I won't need to create the table but rather go to a specific range within an existing one

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say that your predefined table is the second table in the document (starting from the top). You can replace

    Set wrdTbl = wrdDoc.Tables.Add(...)

    with

    Set wrdTbl = wrdDoc.Tables(2)

    Populating the table remains the same.

  12. #12
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Making splendid progress. Could not have done this without your help.

    Next question: Is there a way for Word to be opened either during the output process or at the end of the process so that the outputted document displays as it would if you chose OutputTo for a report?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    An instance of Word created by Automation is hidden by default. You can make it visible by inserting a line

    wrdApp.Visible = True

    at the point where you want it (after creating the instance, of course)

  14. #14
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='788922' date='13-Aug-2009 20:16']An instance of Word created by Automation is hidden by default. You can make it visible by inserting a line

    wrdApp.Visible = True

    at the point where you want it (after creating the instance, of course)[/quote]
    Okay. As usual, a simple solution if you know where to look.

    Last item for this test phase which may or may not be a problem for implementation.

    Right now, I am running the sub from the Immediate window for testing. The first time I run it, it runs fine and the instance of Winword.exe is not showing up in the task manager after the sub is done. When I go and run it the second time, I get runtime error 642: The remote server machine does not exist or is unavailable. When I click on the debug button, it is hanging on the line Set wrdDoc = Word.ActiveDocument. If I end the sub, go to the task manager and manually kill winword.exe and run it the third time, then it is fine again. This process also leaves a word temp file in the folder where my template is.

    So the question: Is this just something annoying to live with in the development phase or is this going to be a problem for implementation? Is there a reason why it is only happening every other time I run the sub?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    When using Automation to control Word, EVERY Word object that you use must refer directly or indirectly to the Word.Application object that you create, in your code wrdApp, otherwise you'll create an extra instance of Word that is not controlled by your application and that will remain in memory. In the line

    Set wrdDoc = Word.ActiveDocument

    Word is a generic object, not your own wrdApp object. You should change it to

    Set wrdDoc = wrdApp.ActiveDocument

    Or even better, combine the two lines

    wrdApp.Documents.Open (m_strDIR & "NIS - Template.DOC")
    Set wrdDoc = Word.ActiveDocument

    to

    Set wrdDoc = wrdApp.Documents.Open (m_strDIR & "NIS - Template.DOC")

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •