Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Access to Word (Office 2k sp1)

    For some time now I have automated various letters from Access. It has always worked well until this last few days. It still does everything it's supposed to but for one thing. It now saves the original blank letter with the inserted text despite the instruction not to. It should send the information to a data file then open the blank letter, merge it then close it unsaved. Part of the code starts in Access but the instruction to insert and merge is on a button in Word.
    The odd thing is this. If I open the blank letter in Word and run the insert and merge code (makealetter) it works perfectly but if I start it from Access then it saves the blank with its insert(b4 merge).
    Any help out there? Here is the code.

    Code from the Access DB
    A button on main form sets it off (There is a choice of buttons and the results are the same for all

    Sub Button_Click
    DoaLetter("myquery","mydata","myletter")
    end sub

    Function DoaLetter(ByVal strQuery as string, strData as string, strFolderDoc as String)

    strData = "C:My Directory" & strData & ".txt"
    strFolderDoc = C:MyotherDirectory" & strFolderDoc & ".doc"
    Kill strData

    DoCmd.TransferText acExportMerge, , strQuery, strData, True

    Set myApp = GetObject(strFolderDoc, "Word.Document")
    myApp.Application.Visible = True
    Set myApp = Nothing
    end function
    This creates the basis for a letter in Word, which has a list of standard letter inserts on menu. The Word Code is as follows:

    Sub MyLetter()
    MakeLetter "myletter.doc"
    End Sub

    Sub MakeLetter(ByVal thisdoc As String)
    On Error GoTo ErrorHandler
    thisdoc = "C:My Directory" & thisdoc
    With Selection
    .EndKey unit:=wdStory
    .InsertFile thisdoc
    End With
    With ActiveDocument
    .MailMerge.Destination = wdSendToNewDocument
    .MailMerge.Execute
    .Close (WdSaveOptions.wdDoNotSaveChanges)
    End With

    End Sub

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Auto Access to Word (Office 2k sp1)

    I understand you had to edit this code a bit for posting, but some parts are hard to follow. My questions:

    (1) What kind of variable is myApp? If it is an Object, then this should work, but if it's a Word.Application, then this should give you some kind of error, I would think, when you GetObject. You could try this instead:

    Set myApp = GetObject(, "Word.Application")
    With myApp
    .Visible = True
    .Documents.Open strFolderDoc
    .Activate
    End With
    Set myApp = Nothing

    I have no idea whether this will make any difference for the problem you're having, but I think it's a good way to interact with Word. Even better, to handle the possibility that Word is not running:

    Dim myApp As Object
    On Error Resume Next
    Set myApp = GetObject(, "Word.Application")
    If Err.Number = 429 Then
    Set myApp = CreateObject("Word.Application")
    Err.Clear
    ElseIf Err.Number <> 0 Then
    MsgBox Err.Number & " = " & Err.Description, , "Error"
    Exit Sub
    End If
    On Error GoTo 0
    With myApp
    .Visible = True
    .Documents.Open strFolderDoc
    .Activate
    End With
    Set myApp = Nothing

    (2) Does the merge run automatically when Access opens the Word file?

    (3) A clearer way to close without saving would be:

    .Close SaveChanges:=wdDoNotSaveChanges

    (4) To avoid confusion between the "ActiveDocument" referring to your first document and to your merge document, you might change your Word code to:

    Sub MakeLetter(thisdoc As String)
    Dim docMaster As Document
    thisdoc = "C:My Directory" & thisdoc
    With Selection
    .EndKey unit:=wdStory
    .InsertFile thisdoc
    End With
    Set docMaster = ActiveDocument
    With docMaster
    .MailMerge.Destination = wdSendToNewDocument
    .MailMerge.Execute
    .Close SaveChanges:=wdDoNotSaveChanges
    End With
    Set docMaster = Nothing
    End Sub

    If these ideas don't help, then... post again.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Access to Word (Office 2k sp1)

    Thanks for your help. I tried your suggestions but it made no difference. My code all works but the code in Word saves even tho' it shouldn't, when document's originally called from Access. I decided the easiest way was to include this sub in Word and call it b4 I make my text insertion. It may not be ideal but it gives the desired end result.
    Thanks again.

    Sub OverWrite()
    Dim myRange As Range
    Set myRange = ActiveDocument.Range( Start:=ActiveDocument.Paragraphs(13).Range.Start,
    End:=ActiveDocument.Range.End)
    myRange.Select
    Selection.Delete
    Selection.TypeParagraph
    End Sub

    Peter Herworth

Posting Permissions

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