Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export query results as table into word at bookmar (a2k)

    I'm trying to export a specific query into a word document as a table. I have a bookmark set where I want the table inserted at, but I don't know how to export it. I can export the query into excel fine, but would like to cut excel out of the process.... any help/pointers into threads previously discussing this would be greatly appreciated (I couldnt' find any).
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Export query results as table into word at boo

    Jeremy

    Here is a slab of code copied from a project that does what you describe.

    It creates a recordset based on the query.

    It uses a bookmark Tablestart to indicate the first cell in the table then uses:


    The typeText method to put data into a cell, and the .Selection.MoveRight Unit:=wdCell to move to the next cell, At the end of each record, the same method moves to the next row.

    Let know if you need more detail. I am in a bit of a hurry at the moment so I have just given you the bare minimum.

    If objDocument.Bookmarks.Exists("tableStart") Then
    objDocument.Bookmarks.Item("tableStart").Range.Sel ect
    End If

    Do While Not rsTeaching.EOF

    With wrd
    .Selection.TypeText Text:=rsTeaching![RateCode]
    .Selection.MoveRight Unit:=wdCell

    .Selection.TypeText Text:=Format(rsTeaching![Rate], "$###,##0.00")
    .Selection.MoveRight Unit:=wdCell
    .Selection.TypeText Text:=rsTeaching![HoursPerWeek] * rsTeaching![TeachingWeeks]


    End With

    rsTeaching.MoveNext
    If Not rsTeaching.EOF Then
    wrd.Selection.MoveRight Unit:=wdCell
    End If
    Loop
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export query results as table into word at boo

    Using what you've supplied and what I've used before, I've got this so far: I get run time error '424' Object required. I thought I took care of that with the line in red, but I guess not. I get the error at the highlighted line.
    <pre>Private Sub Command52_Click()

    Dim strFileName As String
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    ' Open the table with the sequence number to be used
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryMedical")
    <font color=red>qdf.Parameters("[forms]![frmStudents]![text48]") = Me![Text48]</font color=red>
    Set rst = qdf.OpenRecordset


    Dim objWord As Word.Application

    'Start Microsoft Word 97.
    Set objWord = CreateObject("Word.Application")

    With objWord
    'Make the application visible.
    .Visible = True

    'Open the document.
    .Documents.Open ("Usmc_nfsadmin s-1Orders ToolsDatabaseOrdersLetterMedical.doc")

    'Move to each bookmark and insert text from the form.

    .ActiveDocument.Bookmarks("Date").Select
    .Selection.Text = Format(Now(), "dd mmm yy")

    .ActiveDocument.Bookmarks("tablestart").Range.Sele ct


    Do While Not rst.EOF



    With wrd
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold"> .Selection.TypeText Text:=rst![Pay_grade]</span hi>
    .Selection.MoveRight Unit:=wdCell

    .Selection.TypeText Text:=rst![FullName]
    .Selection.MoveRight Unit:=wdCell
    .Selection.TypeText Text:=rst![SSN1]

    End With

    rsTeaching.MoveNext
    If Not rsTeaching.EOF Then
    wrd.Selection.MoveRight Unit:=wdCell
    End If
    Loop

    End With
    End Sub
    </pre>

    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export query results as table into word at boo

    You use a variable wrd that hasn't been declared or set, at least within the procedure. Shouldn't that be objWord? (Or simply omit it, since you already have With objWord ... End With)

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export query results as table into word at boo

    Hmm...I'll check that when I get back to work, hopefully that'll fix it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export query results as table into word at boo

    Alrighty, got it all working, here's the finished code (thank you John for the code for working with the Word table):

    <pre>Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    ' Open the table with the sequence number to be used
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryMedical")
    'since this is a parameter query,
    'you must define where to find the parameter
    qdf.Parameters("[forms]![frmStudents]![text48]") = Me![Text48]
    Set rst = qdf.OpenRecordset


    Dim objWord As Word.Application

    'Start Microsoft Word 97.
    Set objWord = CreateObject("Word.Application")

    With objWord
    'Make the application visible.
    .Visible = True

    'Open the document.
    .Documents.Open ("Usmc_nfsadmin s-1Orders ToolsDatabaseOrdersLetterMedical.doc")

    'Move to each bookmark and insert text from the form.

    .ActiveDocument.Bookmarks("Date").Select
    .Selection.Text = Format(Now(), "dd mmm yy")

    'Select bookmark
    .ActiveDocument.Bookmarks("tablestart").Range.Sele ct

    Do While Not rst.EOF

    'Select the first cell
    .Selection.TypeText Text:=rst![Pay_grade] 'insert the info from the first field in the query
    .Selection.MoveRight Unit:=wdCell 'move to the next cell to the right in your table
    .Selection.TypeText Text:=rst![FullName]
    .Selection.MoveRight Unit:=wdCell
    .Selection.TypeText Text:=rst![SSN1]
    .Selection.MoveRight Unit:=wdCell
    .Selection.TypeText Text:=rst![SIGNATURE]

    rst.MoveNext 'move to the next record in the query

    If Not rst.EOF Then
    .Selection.MoveRight Unit:=wdCell
    End If

    Loop

    End With

    rst.Close
    Set rst = Nothing
    </pre>

    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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