Results 1 to 8 of 8
  1. #1
    Join Date
    May 2002
    Posts
    9

    Unanswered: Urgent: Automately updating Word file based on Access data

    I need to write a program that can check a Word doc. for some certain fields and update the contents with the newer data in Access database. I'm not sure if I should use VBA or something else? Anybody has the knowledge please help!!! Some code samples would be perfect or recommandations of starting the coding...

    Thx a lotttttt!

  2. #2
    Join Date
    Apr 2002
    Posts
    139
    Hi Flower,

    Why not use the Mail Merge features from within Word?

    Have a look at the possibilities in Word under Tools / Mail Merge.
    Simply connect your Word-document to your Access-database (tabel or query) via the Mail Merge Wizard.

    Do not think you need VBA-code at first instance at all.

    hth

  3. #3
    Join Date
    May 2002
    Posts
    9

    thanks but....

    hi marion,

    Thanks for the suggestion! But the file i need to deal with is an ordinary Word file (not mail or letter etc.) and i need to retrieve the data from an existing Access db which contains info. that is going to be used to update the Word fields. If no other simpler way, I'm thinking of putting bookmarks in the word file and then code in VBA from Access to manipulate the updating. still gotta find out how to make it work any suggestions are welcome and appreciated

    flower

  4. #4
    Join Date
    Apr 2002
    Posts
    139
    Flower,

    Using the Mail Merge option, you can connect whatever Word-document you have to whatever datasource you like (Access, Excel, txt etc).
    Does not need to be a mailing, can be just any single document in Word.

    My suggestion: Just play around with the Word Wizard for an hour, and be amazed. It really can be a "don't worry - be happy" solution.

    The other option using bookmarks is very well possible, I've done that few times. However, concerns quits some VBA-coding.
    Let me know when you want to hear more on this.

  5. #5
    Join Date
    May 2002
    Posts
    9

    Thanks again

    Yeah you are totally right! I've played with the merge tool for a while and found the tricks . Thanks for showing me the direction... I haven't found a way to save/record all the merge steps as a macro or something though cause I really don't wanna go through the steps every time I need to update the file. So every time the database is updated, I could simplely run the macro to update the word file directly... Do u have any idea about this part?

    About the bookmark and VBA method, do you mind send me a few samples? I really wanna try both ways to see which will work perfect to my boss's request. Thank you sincerely for your great help, Marion!

    flower

  6. #6
    Join Date
    Apr 2002
    Posts
    139
    Hi Flower,

    You can simply record the entire mail merge proces as a Word-macro.
    Start a new macro in Word and perform all the merge actions you want.
    Close the macro-recorder and look at it's results.

    I just created a mail merge from scratch and got the following code:

    'This connects database to Word-doc:
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
    "D:\yourdatabase.mdb", _
    ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
    Format:=wdOpenFormatAuto, Connection:="TABLE Clients", SQLStatement:= _
    "SELECT * FROM [Clients]", SQLStatement1:=""

    'Than add some fields from table Clients:
    ActiveDocument.MailMerge.EditMainDocument
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="client"
    Selection.MoveRight Unit:=wdCharacter, Count:=1
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= "contact"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="phone"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="street"

    'Finally perform the merge to a Word-document (188 pages)
    With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .Execute
    End With

    Life sure can't be easier, I would say!

    About VBA and Bookmarks, I will come back on this later.

  7. #7
    Join Date
    Apr 2002
    Posts
    139
    Hi Flower,

    About VBA and Bookmarks:

    1) Create your Word-template with bookmarks

    2) Run code like below from Access:

    Dim oApp As Object
    Dim lngRetVal As Long
    Dim varContent As Variant

    'Check whether Word is already running:
    lngRetVal = FindWindowByClass("OpusApp", 0&)
    If lngRetVal <> 0 Then
    'Found existing instance of Word
    Set oApp = GetObject(, "Word.Application")
    Else
    'Starting new instance of Word
    Set oApp = CreateObject("Word.Application")
    End If

    'Activate Word
    oApp.Visible = True
    oApp.Application.Activate
    oApp.Application.WindowState = wdWindowStateMaximize

    'Create a new Word-document based on your template:
    oApp.Documents.Add Template:=D:\MyTemplate.dot", NewTemplate:=False

    'Initiate variable holding content to place in bookmarks
    varContent = "..."

    'Cycle through bookmarks in your Word-document:
    oApp.Selection.Goto Name:="clientID"
    'Me. refers to the client form where this code was running from.
    'Cstr() because I only got it working when transferring text-values
    varContent = CStr(Me.ClientID)
    oApp.Selection.TypeText Text:= varContent
    varContent = "..."

    oApp.Selection.Goto Name:="client"
    'Nz() because Me.Client might be a Null value
    varContent = Nz(Me.Client, "...")
    oApp.Selection.TypeText Text:= varContent
    varContent = "..."

    'etc for all your bookmarks

    oApp.ActiveDocument.SaveAs "D:\MyNewDocument.doc"

    Code sample was quickly edited in Word, so there might be a minor bug.

    Hope this and the Mail Merge thing will be of your benefit.

  8. #8
    Join Date
    May 2002
    Posts
    9

    IT works! :)

    Hi Marion,

    I tried the mail merge recording macro over the weekend and it works perfect! I'm so delighted I'm going to test the VBA code now... Thx so much for all your kindly help, really appreciated! Thxxxxxxxx.........

    flower

Posting Permissions

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