Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30

    Unanswered: Export data to Word table using VBA - Solved!!

    I fixed it. There was one vital step missing:

    Code:
    rs.MoveNext
    Mike

    Hi all

    I want to use VBA to create a new Word document and export all the fields in an Access table to a table in the Word document.

    My VBA skills are limited so I put something together from examples I found on the internet. It starts off OK, opening the document and creating the table. Unfortunately it only copies fields from the first record and loops indefinitely. How can I get it to work down all the records in the Access table and stop when it reaches the end?

    TIA

    Mike

    Code:
    Option Compare Database
    Dim WordApp As Word.Application
    Dim doc As Word.Document
    
    Sub WordSetup()
    Set WordApp = New Word.Application
    WordApp.Documents.Add
    Set doc = WordApp.ActiveDocument
    WordApp.Visible = True
    End Sub
    
    Sub WordExport()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblTable")
    
    WordSetup
    
    doc.Tables.Add Range:=doc.Range, numrows:=1, numcolumns:=2
    
    'Create headers
    doc.Tables(1).Columns(1).Cells(1).Range.Text = "Column 1"
    doc.Tables(1).Columns(2).Cells(1).Range.Text = "Column 2"
    
    'Export data
    i = 1
    Do Until rs.EOF
        doc.Tables(1).Columns(1).Cells.Add
        doc.Tables(1).Columns(1).Cells(i + 1).Range.Text = rs.Fields(0)
        doc.Tables(1).Columns(2).Cells(i + 1).Range.Text = rs.Fields(1)
    
    i = i + 1
    Loop
    
    doc.SaveAs ("Export.doc")
    
    doc.Close True
    WordApp.Quit True
    Set doc = Nothing
    Set WordApp = Nothing
    
    End Sub
    Last edited by P&T; 06-15-11 at 05:53. Reason: Problem solved

Posting Permissions

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