of course it's possible. all office products are interconnected, and VBA is the language to use for the purpose. I have this in my files from long ago. take a look. the instructions might be outdated though, as it says to put the sample letter document on the C:\ drive. but as I have learned from other people here, microsoft has done something with security on the c drive, and it might not work being place there. you might have to put the .doc somewhere else in the dirs.
Option Compare Database
Dim WithEvents oApp As Word.Application
Private Sub Command0_Click()
Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String
Set oMainDoc = oApp.Documents.Open("D:\Ashfaque\Test Letter")
oApp.Visible = True
With oMainDoc.MailMerge
.MainDocumentType = wdFormLetters
sDBPath = "D:\Ashfaque\Letter Database.mdb"
.OpenDataSource Name:=sDBPath, _
SQLStatement:="SELECT * FROM [tblcompanies]"
End With
With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute
End With
oApp.Activate
oApp.Documents.Parent.Visible = True
oApp.Application.WindowState = 1
oApp.ActiveWindow.WindowState = 1
End Sub
Private Sub Form_Load()
Set oApp = CreateObject("Word.Application")
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set oApp = Nothing
End Sub
As gasman stated withevents needs to be in a class module or a form module. It wont work in a standard module.
Additionally there is nothing raising any events so it is not needed.
I also dont see that you have declared Option Explicit. You should add it.
your path to the document is wrong. Set oMainDoc = oApp.Documents.Open("D:\Ashfaque\Test Letter") This should be Letter.doc
I made changes as follows which work however I wouldn't reccomend using it as is.
(note the changes to the path which point to the folder containing the mdb and doc. Should work regaurdless of location)
It opens 2 instances of word and leaves them open. you should close 1st instance.
I believe Albert when he recommends that you never let a document connect directly to a database.
Again I would recommend you look at Alberts code.
Code:
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim oApp As Word.Application
Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String
Set oApp = CreateObject("Word.Application")
Set oMainDoc = oApp.Documents.Open(CurrentProject.Path & "\Test Letter.doc")
oApp.Visible = True
With oMainDoc.MailMerge
.MainDocumentType = wdFormLetters
sDBPath = CurrentProject.Path & "\Letter Database.mdb"
.OpenDataSource Name:=sDBPath, _
SQLStatement:="SELECT * FROM [tblcompanies]"
End With
With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute
End With
oApp.Activate
oApp.Documents.Parent.Visible = True
oApp.Application.WindowState = 1
oApp.ActiveWindow.WindowState = 1
Set oApp = Nothing
End Sub
Not yet ....I am trying otherway but still not success...I just want to run vba code thru a btn and get the record to word document or even direct to PDF file and save it at the desired destination.
Did you try what I posted?
Comment out all your code including what you have in the declarations and put what I posted under a new button click.
Note that all code is within the one procedure including - Dim oApp As Word.Application