Export Data from a Form to MS Word 1

[rphbrew]

I have used the mail merge function, but this does not allow me to transfer a single set of data.

I want to be able to transfer the data of the current record present in the form to a word document. Can this be done? I have MS Access 97 and Word 97.

thanks

 

[wbwillson]

rphbrew,

I had exactly the same problem a week weeks ago, and after much research I was able to pass only the current record to word instead of the entire table. This is how I did it: (Hope this helps - Bill)

----

Creat a Word Template and place a BookMark (Example LastName). Save the word doc as MyDoc.doc or whatever you want to call it.

On the form that you want to send the data to Word from create an On Click event with the following:

On Button_Click()

On Error GoTo HandleErr

Dim objWord As Word.Application

'Start Microsoft Word
Set objWord = CreateObject ("Word.Application&quot

With objWord
'Make Word Visible (or Not)
.Visible = True

'Open the Word Document
.Documents.Open ("C:\MyDoc.doc&quot

'Move to the Bookmark and insert the data from your form
.ActiveDocument.Bookmarks ("LastName&quot.Select
.Selection.Text = (Forms!frmMyFormName!myFieldName)

'frmMyFormName is the name of your Form
'myFieldName is the name of the field on your form
' which contains the Last Name

'Print the Word Document and Close Word
.Options.PrintBackground = False ' No background Printing
.Application.DisplayAlerts = wdAlertsNone
.ActiveDocument.PrintOut 'Prints the Word Doc
'Close the Document without saving changes
.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
.Quit
End With

HandleErr:
If Err.Number = 94 Then
ObjWord.Selection.Text = ""
Resume Next
End If
Exit Sub

 

[rphbrew]

This works great,

Just one more question, Is it possible to have it save the document with a different name? I would like it to use the information in one of bookmark fields.

 

[wbwillson]

Try using:

.ActiveDocument.SaveAs FileName:="MyDoc.doc"


You could set your file name to be one of your fields

Dim MyFileName As String

MyFileName = [FileName]

.ActiveDocument.SaveAs FileName:=MyFileName

Though I haven't tried this it should work.

Bill

 

[drmansoor]

Hi rphbrew,

To transfer/export data from any table or query in MS excel format so that it can be used for merging purposes in MS Word is really very simple: you just need to create a new macro for this: in new macro select the action as "output to" and its properties select "table" in object type and select the name of your required table in "object name" and in "output format" select excell.xls format and thats it!

when you will run this macro by doulble click it will ask the location where you want to safe the new file and also the name of file, then the whole data of the table will be copied to that newly created file and that file can easily be used for mearging purposes in MS word.

Hope it will help!
let me know if any thing is not clear.
Mansoor
mansoor8@cyber.net.pk

 

[manners]

Hello.

I have tried the procedure listed by wbwillson but i get the error "User-defined type not defined" My Code is listed below - help is gratefully recieved

Private Sub Command46_Click()
    On Error GoTo HandleErr
    Dim objWord
    Dim objWord As Word.Application
    
    'Start MS Word
    Set objWord = CreateObject("Word.Application")
    
    With objWord
        .Visible = True
        .Documents.Open ("C:\TestDocument.doc")
        .ActiveDocument.Bookmarks("Fullname").Select
        .Selection.Text = (Forms!MainForm!Surname)
        .Options.PrintBackground = False
        .Application.DisplayAlerts = wdAlertsNone
        '.ActiveDocument.
    End With
    
    HandleErr
        If Err.Number = 94 Then
            objWord.Selection.Text = ""
            Resume Next
        End If
    Exit Sub
End Sub