Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation code - so far and no further (2000 SR1a)

    I am making great strides on dumping my Cultural Directory information from Access into Word and actually have something that works. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> Given my complete lack of knowledge of the Word Object language, this is a start. The attached code (adapted from my email dump (which now works perfectly) and other code from one of my reference books, puts the info into a Word Document. Unfortunately it puts every name in the name field, every address in the address field, etc, so while pleased at my achievement, the data is useless <img src=/S/sad.gif border=0 alt=sad width=15 height=15> . I suspect one of two problems: I am not looping properly or I should be using merge fields rather than bookmarks in Word. In either case, this is as far as I can go on my own. Any help is greatly appreciated.

    Peter N

    Option Compare Database
    Option Explicit

    Private Const m_strDIR As String = "C:My DocumentsDataDump"
    Private Const m_strTEMPLATE As String = "DirectoryOutput.dot"

    Private m_objWord As Word.Application
    Private m_objDoc As Word.Document
    Private Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)

    ' selects the bookmark and inserts the text
    m_objDoc.Bookmarks(strBkmk).Select
    m_objWord.Selection.Text = varText & ""

    End Sub


    Public Sub DataDump()

    Dim db As Database ' current database
    Dim recListMain As Recordset ' recordset of listings
    Dim recClient As Recordset ' recordset of clients (for email address)
    Dim strSQL As String ' sql string
    Dim strListing As String ' string of listing info
    Dim strCat As String ' just the categories items
    Dim recListDetails As Recordset 'recordset of listing details
    Dim strCatDetails As Variant 'listing details as variant
    Dim strSQLDetail As String 'sql string of listing details


    ' open the database and recordset of suppliers
    Set db = CurrentDb()
    Set recClient = db.OpenRecordset("New Directory Listings")

    ' instantiate the word application and create a new
    ' document based upon the supplied template
    Set m_objWord = New Word.Application
    Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)

    ' now loop through the suppliers
    While Not recClient.EOF
    ' open a recordset of the reorderable items
    strSQL = "SELECT * FROM NewDirListMain WHERE ClientID = " & recClient("ClientID")
    Set recListMain = db.OpenRecordset(strSQL)

    ' create a string containing the order details
    strCat = ""
    While Not recListMain.EOF
    strCat = strCat & vbCrLf & recListMain("Directory Category") & " ~ "

    strSQLDetail = "SELECT * from qryNewDirectoryListingDetails WHERE ListingID = " & recListMain("ListingID")
    Set recListDetails = db.OpenRecordset(strSQLDetail)
    strCatDetails = Null
    While Not recListDetails.EOF
    strCatDetails = (strCatDetails + ", ") & recListDetails("NewDirectorySpecialties")
    recListDetails.MoveNext
    Wend

    recListDetails.Close
    strCat = strCat & strCatDetails
    recListMain.MoveNext
    Wend

    InsertTextAtBookMark "Name", recClient("FullName")
    InsertTextAtBookMark "Address1", recClient("Address1")
    InsertTextAtBookMark "Address2", recClient("Address2")
    InsertTextAtBookMark "City", recClient("City")
    InsertTextAtBookMark "WorkPhone", recClient("WorkPhone")
    InsertTextAtBookMark "Email", recClient("Email")
    InsertTextAtBookMark "ListDetails", strCat
    InsertTextAtBookMark "Statement", recClient("additionalDirectoryInfo")

    recListMain.Close

    ' move onto the next supplier
    recClient.MoveNext
    Wend
    m_objDoc.SaveAs FileName:=m_strDIR & "DataDump" & _
    " - " & FormatDateTime(Date, vbLongDate) & ".DOC"
    m_objDoc.Close
    m_objWord.Quit

    ' clean up
    Set m_objDoc = Nothing
    Set m_objWord = Nothing
    recClient.Close
    Set recClient = Nothing
    Set recListMain = Nothing

    End Sub

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

    Re: Automation code - so far and no further (2000 SR1a)

    You loop throught the records of New Directory Listings, but you keep on dumping the fields and subfields in the same bookmarks using InsertTextAtBookMark. In what kind of structure do you want to put the data? In the cells of a table, or what? If you try to explain what you want the result to look like, we might be able to help.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation code - so far and no further (2000 SR1a)

    It needs to be a clean unformatted text dump so that the book designer can just apply her templates in Quark to create the directory. The raw text will look like the following:
    ListingNumber Name
    Address workphone email
    City PostalCode webaddress
    listing details (from the SQL statements)
    AdditionalNotes

    Everything except the listing details will come from the Client table. The Listing tables are a MainCategory table linked by Client ID (ListingID, autonumber, primary key) and a CategoryDetails table linked by Listing ID with Listing ID and DetailID as the primary key.

    Each individual or organization will have a separate listing detailing what it is they do and their specialties. In the text dump, each record needs to only be separated by a couple of hard returns or a section break (I am waiting for the designer to tell me which she prefers).

    Hope this clarifies some.

    Peter

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Automation code - so far and no further (2000 SR1a)

    I'm not sure what you mean by
    <hr>a clean unformatted text dump<hr>
    but if you litterally mean a line of text followed by a carriage return and/or line feed, then that can be accomplished directly from Access, without involving Word at all. Simply export your table to a delimited text file or a fixed width text file - the help file tells you the basics.

    On the other hand, if you have multiple records in the listing details, and additional notes, so that you really have a one to many relationship, then you may need to resort to some sort of code like you have. But the trick is to not use bookmarks, but simply put in the paragraph marks, or insert section breaks as you put in text. I would guess that your Quark person would prefer multiple paragraph marks however.
    Wendell

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

    Re: Automation code - so far and no further (2000 SR1a)

    Instead of InsertTextAtBookMark "Name", recClient("FullName"), you could use

    Selection.TypeText recClient("FullName")
    Selection.TypeParagraph

    and so on for the other pieces of data.

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation code - so far and no further (2000 SR1a)

    If it were just one table, I simply would have used a mail merge. Exporting text files, I've done. The problem is that there is a sub table and a sub-subtable and I have to loop through those to collate the data and do some minor formatting to take that information and gather it all in a single line of text.

    I will change the code as Hans suggested and post back if I am still stuck. I had a feeling the bookmarks weren't going to work. Thanks.
    Peter

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation code - so far and no further (2000 SR1a)

    Your suggestion of changing the bookmarks to Selection.TypeText has worked fine, exactly what I need.

    There is only one little problem now: I am getting Run-time error '94' - Invalid use of null showing up. There *are* nulls in the fields. In the snippet of code below, Address2 has many nulls in the table and the de###### highlights that line. What are my options for getting around that, because that seems to be the last hurdle (until I actually get all 18 fields into the final version!)

    Selection.TypeText recClient("FullName")
    Selection.TypeParagraph
    Selection.TypeText recClient("Address1")
    Selection.TypeParagraph
    Selection.TypeText recClient("Address2")

    Peter

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

    Re: Automation code - so far and no further (2000 SR1a)

    Hi Peter,

    Replace Selection.TypeText recClient("FullName") by

    If Not IsNull(recClient("FullName")) Then
    Selection.TypeText recClient("FullName")
    End If

    If you want to insert an empty paragraph when the field is empty, keep the following Selection.TypeParagraph below the End If; if you don't want an empty paragraph, put Selection.TypeParagraph just above End If.

    Similarly for the other fields.

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automation code - so far and no further (2000 SR1a)

    I don't know if this can be applied in this case, as I normally always use either Bookmarks or FormFields when exporting from Access to Word via automation. I quickly found out that Word doesn't like Null in either of these, so I use NZ function to avoid "issues". Examples:

    If using Bookmarks:

    With Doc.Bookmarks
    .Item("FIELD1").Range.Text = Nz(frm.Subfrm3!FIELD1)
    .Item("FIELD2").Range.Text = Nz(frm.Subfrm3!FIELD2)
    End With

    With Doc.Bookmarks
    .Item("FIELD1").Range.Text = Nz(rst!FIELD1)
    .Item("FIELD2").Range.Text = Nz(rst!FIELD2)
    End With

    If using FormFields:

    With Doc.FormFields
    .Item("FIELD1").Result = Nz(frm.Subfrm3! FIELD1)
    .Item("FIELD2").Result = Nz(frm.Subfrm3! FIELD2)
    End With

    In above examples data is being exported to Word either from an open form, or from a recordset opened in code. Using NZ avoids the "Null" errors, it may be simpler that repeatedly testing for Null...
    HTH

  10. #10
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation code - so far and no further (2000 SR1a)

    I have solved the problem with Hans' solution, but thanks for the suggestion. I will store it for future reference. Once I got sorted out the with Word Object Model syntax, all was well. I even had enough information to sort out the last little snags on my own.

    Thanks to all of you who helped. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Have one on me.

    Peter

Posting Permissions

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