Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47

    Question Unanswered: writing a recordset to word

    hi everyone. thank you for your time.

    I have a database that i have extracted data from using a recordset. I now have a word document that i want to populate with the data in the recordset. I need the code to write the data to the word document. I can open a word document and write header and footer text, but i don't know how to do it using a recordset.

    Thank you.

    nerdy_girl
    Nerdy Girl

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I use word for mail merges and use modules like the following to put recordset into text files:

    Open Filename For Random As #1 Len = Len(MyRecord)
    Lock #1
    If rsTemp.RecordCount > 0 Then
    MyRecord.Counter = Chr(34) & _
    "CompanyName" & Chr(34) & vbTab & Chr(34) & _
    "Contact" & Chr(34) & vbTab & Chr(34) & _
    "WorkPhone" & Chr(34) & vbTab & Chr(34) & _
    "WorkExtension" & Chr(34) & vbTab & Chr(34) & _
    "MobilePhone" & Chr(34) & vbTab & Chr(34) & _
    "FaxNumber" & Chr(34) & vbTab & Chr(34) & _
    "Address" & Chr(34) & vbTab & Chr(34) & _
    "BlankField" & Chr(34)
    MyRecord.Counter2 = vbCr & vbLf
    X = X + 1
    Put #1, X, MyRecord

  3. #3
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47
    is myRecord the recordset, and is this module in the wordVBA or AccessVBA. Should i pass my recordset to a method in the word doc and display it there?
    is that what your doing?
    Nerdy Girl

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I put this outside the functions just after the Option Explicit line:

    Private Type Record3
    Counter As String * 470
    Counter2 As String * 2
    End Type

    And in the access module, I put in this:

    Dim MyRecord As Record3

    You will have to change the 470 to the max length of your record
    Once the file is created, I create a mail merge and pull the data from the text file created with this code.

  5. #5
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47
    Thanks for your reply, but im a bit confused as to what you are doing?

    Here is my code, its a onclick event that should open word and write text in it from the record set.

    Private Sub cmdGenerateQuote_Click()
    On Error GoTo Err_cmdGenerateQuote_Click

    'application object
    Dim oWord As New Word.Document
    Dim oApp As New Word.Application
    Dim rstADO As ADODB.Recordset

    ' declaration for the records to be pulled
    Dim sSQL As String
    Dim sFile As String


    sSQL = "SELECT * FROM QuotationNew"

    'creates a new instance of the object
    Set rstADO = New ADODB.Recordset
    rstADO.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic

    'printing all the values to the immediate screen for viewing
    Do While Not rstADO.EOF
    For Each fld In rstADO.Fields
    Debug.Print fld.Value & ";";
    Next
    Debug.Print
    rstADO.MoveNext
    Loop

    'open the .doc document

    Set oWord = _
    oApp.Documents.Open(FileName:="c:/fleetcare/help/test.doc")
    oApp.Visible = True

    'hopefully print the recordset to the document
    Dim txt As String
    Do While Not rstADO.EOF
    For Each fld In rstADO.Fields
    txt = fld.Value & ";";
    oApp.Selection.Text = "Hello Nadia" ' this doesn't work! Why???
    'this is where i need the code to write to the document???

    Next
    rstADO.MoveNext
    Loop

    'destroying the instance
    Set oWord = Nothing
    Set oApp = Nothing
    Set rstADO = Nothing

    Exit_cmdGenerateQuote_Click:
    Exit Sub

    Err_cmdGenerateQuote_Click:
    MsgBox Err.Description
    Resume Exit_cmdGenerateQuote_Click

    End Sub

    Private Sub cmdMainMenu_Click()
    On Error GoTo Err_cmdMainMenu_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.Close
    stDocName = "frmMainMenu"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_cmdMainMenu_Click:
    Exit Sub

    Err_cmdMainMenu_Click:
    MsgBox Err.Description
    Resume Exit_cmdMainMenu_Click

    End Sub



    I hope someone can help me with this. Thank you.
    Nerdy Girl

Posting Permissions

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