Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access data to Word 2003

Discussion in 'Information Technology' started by MSU Sptn2, Jul 28, 2006.

Tags:
  1. MSU Sptn2

    MSU Sptn2
    Expand Collapse
    Guest

    I created an automation to send data from an Access table to Word 2000 using
    bookmarks in Word. Some of the computers on the server have Word 2003 and
    the code does not work. I think the problem lies in the following code:

    .ActiveDocument.Bookmarks("App_No").Select
    If [Form_ODDAPP FORM].[App No] <> 1 Then
    .Selection.Text = (CStr([Form_ODDAPP FORM].[App No]))
    Else
    .Selection.Text = .Selection.Text & (CStr(""))
    End If
    .ActiveDocument.Bookmarks.Add name:="App_No", Range:=Selection.Range

    Is there something in the code that is not compatible with Word 2003? It
    works smoothly with Access 2000 and Word 2000 but not Word 2003.

    Thanks
     
  2. Loading...


  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    It might not have anything to do with the problem, but why are you calling
    the Add method of the Bookmarks collection? You've already assigned text to
    an existing bookmark of that name.

    FWIW here's some code I use for addressing a template letter with values
    from an Access form. It was developed in Access 2002 but the application has
    been distributed to Office 2003 users and I've had no re[orts of any
    problems. The path to the Word template document is passed into the
    CreateLetter procedure as its argument:

    ''''module begins''''
    Option Compare Database
    Option Explicit

    Sub CreateLetter(strTemplate As String)

    ' Opens a document in Word and inserts values from
    ' current record at bookmarks in Word document.
    ' Accepts: path to Word template file - String

    On Error GoTo Err_Handler

    Dim objWord As Object
    Dim objDoc As Object
    Dim frm As Form
    Dim strAddress As String

    ' return reference to form
    Set frm = Forms!frmAddresses

    ' if Word open return reference to it
    ' else establish reference to it
    On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
    If Err.Number = 429 Then
    Set objWord = CreateObject("Word.Application")
    End If

    AppActivate "Microsoft Word"
    On Error GoTo Err_Handler

    ' open Word document in maximised window
    objWord.Visible = True
    Set objDoc = objWord.Documents.Add(strTemplate)
    objWord.WindowState = wdWindowStateMaximize

    ' insert text at bookmarks, getting values from form
    InsertAtBookmarks objWord, objDoc, "FirstName", frm!FirstName
    InsertAtBookmarks objWord, objDoc, "LastName", frm!LastName
    strAddress = (frm!Address + vbNewLine) & (frm!Address2 + vbNewLine) & _
    (frm!City.Column(1) + vbNewLine) & (frm!County + vbNewLine) &
    frm!PostCode
    InsertAtBookmarks objWord, objDoc, "Address", strAddress
    InsertAtBookmarks objWord, objDoc, "CurrentDate", Format(VBA.Date(), "d
    mmmm yyyy")
    InsertAtBookmarks objWord, objDoc, "ToName", frm!FirstName

    Set objDoc = Nothing
    Set objWord = Nothing

    Exit_here:
    On Error GoTo 0
    Exit Sub

    Err_Handler:
    MsgBox Err.Description & " (" & Err.Number & ")"
    Resume Exit_here

    End Sub

    Private Sub InsertAtBookmarks(objW As Object, _
    objD As Object, _
    strBookmark As String, _
    varText As Variant)

    ' select bookmark
    objD.Bookmarks(strBookmark).Select
    ' insert text at bookmark
    objW.Selection.Text = Nz(varText, "")

    End Sub
    ''''module ends''''

    Ken Sheridan
    Stafford, England

    "MSU Sptn2" wrote:

    > I created an automation to send data from an Access table to Word 2000 using
    > bookmarks in Word. Some of the computers on the server have Word 2003 and
    > the code does not work. I think the problem lies in the following code:
    >
    > .ActiveDocument.Bookmarks("App_No").Select
    > If [Form_ODDAPP FORM].[App No] <> 1 Then
    > .Selection.Text = (CStr([Form_ODDAPP FORM].[App No]))
    > Else
    > .Selection.Text = .Selection.Text & (CStr(""))
    > End If
    > .ActiveDocument.Bookmarks.Add name:="App_No", Range:=Selection.Range
    >
    > Is there something in the code that is not compatible with Word 2003? It
    > works smoothly with Access 2000 and Word 2000 but not Word 2003.
    >
    > Thanks
    >
    >
     
  4. MSU Sptn2

    MSU Sptn2
    Expand Collapse
    Guest

    Ok, apparently it had everything to do with the problem. I deleted the Add
    bookmarks portion and it worked fine. I used the Add method of the bookmarks
    collection to re-assign the bookmark to the selected text so I could
    reference it later in the document using a field. Is there a better way to
    do this without creating mutliple bookmarks for the same text?

    "Ken Sheridan" wrote:

    > It might not have anything to do with the problem, but why are you calling
    > the Add method of the Bookmarks collection? You've already assigned text to
    > an existing bookmark of that name.
    >
    > FWIW here's some code I use for addressing a template letter with values
    > from an Access form. It was developed in Access 2002 but the application has
    > been distributed to Office 2003 users and I've had no re[orts of any
    > problems. The path to the Word template document is passed into the
    > CreateLetter procedure as its argument:
    >
    > ''''module begins''''
    > Option Compare Database
    > Option Explicit
    >
    > Sub CreateLetter(strTemplate As String)
    >
    > ' Opens a document in Word and inserts values from
    > ' current record at bookmarks in Word document.
    > ' Accepts: path to Word template file - String
    >
    > On Error GoTo Err_Handler
    >
    > Dim objWord As Object
    > Dim objDoc As Object
    > Dim frm As Form
    > Dim strAddress As String
    >
    > ' return reference to form
    > Set frm = Forms!frmAddresses
    >
    > ' if Word open return reference to it
    > ' else establish reference to it
    > On Error Resume Next
    > Set objWord = GetObject(, "Word.Application")
    > If Err.Number = 429 Then
    > Set objWord = CreateObject("Word.Application")
    > End If
    >
    > AppActivate "Microsoft Word"
    > On Error GoTo Err_Handler
    >
    > ' open Word document in maximised window
    > objWord.Visible = True
    > Set objDoc = objWord.Documents.Add(strTemplate)
    > objWord.WindowState = wdWindowStateMaximize
    >
    > ' insert text at bookmarks, getting values from form
    > InsertAtBookmarks objWord, objDoc, "FirstName", frm!FirstName
    > InsertAtBookmarks objWord, objDoc, "LastName", frm!LastName
    > strAddress = (frm!Address + vbNewLine) & (frm!Address2 + vbNewLine) & _
    > (frm!City.Column(1) + vbNewLine) & (frm!County + vbNewLine) &
    > frm!PostCode
    > InsertAtBookmarks objWord, objDoc, "Address", strAddress
    > InsertAtBookmarks objWord, objDoc, "CurrentDate", Format(VBA.Date(), "d
    > mmmm yyyy")
    > InsertAtBookmarks objWord, objDoc, "ToName", frm!FirstName
    >
    > Set objDoc = Nothing
    > Set objWord = Nothing
    >
    > Exit_here:
    > On Error GoTo 0
    > Exit Sub
    >
    > Err_Handler:
    > MsgBox Err.Description & " (" & Err.Number & ")"
    > Resume Exit_here
    >
    > End Sub
    >
    > Private Sub InsertAtBookmarks(objW As Object, _
    > objD As Object, _
    > strBookmark As String, _
    > varText As Variant)
    >
    > ' select bookmark
    > objD.Bookmarks(strBookmark).Select
    > ' insert text at bookmark
    > objW.Selection.Text = Nz(varText, "")
    >
    > End Sub
    > ''''module ends''''
    >
    > Ken Sheridan
    > Stafford, England
    >
    > "MSU Sptn2" wrote:
    >
    > > I created an automation to send data from an Access table to Word 2000 using
    > > bookmarks in Word. Some of the computers on the server have Word 2003 and
    > > the code does not work. I think the problem lies in the following code:
    > >
    > > .ActiveDocument.Bookmarks("App_No").Select
    > > If [Form_ODDAPP FORM].[App No] <> 1 Then
    > > .Selection.Text = (CStr([Form_ODDAPP FORM].[App No]))
    > > Else
    > > .Selection.Text = .Selection.Text & (CStr(""))
    > > End If
    > > .ActiveDocument.Bookmarks.Add name:="App_No", Range:=Selection.Range
    > >
    > > Is there something in the code that is not compatible with Word 2003? It
    > > works smoothly with Access 2000 and Word 2000 but not Word 2003.
    > >
    > > Thanks
    > >
    > >

    >
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    My own approach would be to create separate bookmarks in the document and
    assign the same text expression to them. In fact the code I sent you does
    this, assigning the FirstName control's value to both the FirstName and
    ToName bookmarks The former is part of the first line of the address in the
    letter, the latter the appellation. I'd be the first to admit that my
    knowledge of the Word object model is limited, however, and there may be
    other ways. The gurus of Word discussion group would doubtless be able to
    assist.

    Ken Sheridan
    Stafford, England

    "MSU Sptn2" wrote:

    > Ok, apparently it had everything to do with the problem. I deleted the Add
    > bookmarks portion and it worked fine. I used the Add method of the bookmarks
    > collection to re-assign the bookmark to the selected text so I could
    > reference it later in the document using a field. Is there a better way to
    > do this without creating mutliple bookmarks for the same text?
    >
     

Share This Page