WORD OLE INTEGRATION

[aaahhhrrggghhhh]

Hello.

I have been wrecking my head for the last 4 days on this problem, please help.

I have a form with a bound OLE Container control on it. I have dragged a word document (business letter) into it and when I press a button on the form, the following code is executed :

Private Sub btnMergeTheDocument_Click()

'collect the word document and merge it with the datasource

Dim tmpQryNm As String
Dim Word_Obj As Object

Set Word_Obj = CreateObject("Word.Basic&quot
Me!LettObject.Verb = -2
Me!LettObject.Action = 7
Set Word_Obj = LettObject.Object.Application.WordBasic
tmpsourcedb = Trim(CurrentProject.Path & "\" & CurrentProject.Name)
tmpsourcedbbe = "N:\YRS\Test\Split\YRS_be.mdb"
tmpQryNm = DFirst("DataSrcObjName", "OLE_DataSrc", "[DataSrcID] = " & Me.DataSrcID)
Dim myquery As QueryDef

Dim mytmpword As Word.Document
Debug.Print "Before : " & Word_Obj.MailMergeMainDocumentType

Word_Obj.MailMergeMainDocumentType (0)
Debug.Print "After : " & Word_Obj.MailMergeMainDocumentType
tme = Format(Now(), "ddmmyyhhnnss&quot
flenm = "C:\temp\" & CurrentUser & tme & ".doc"
LettObject.Verb = acOLEVerbPrimary
Connect$ = "[ODBC 32 bit Data Sources];DRIVER=Microsoft Access Driver (*.mdb) (32 bit);UID=Admin;" & _
"ReadOnly=1;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;" & _
"Exclusive=0;DriverID=281;DefaultDir=C:\YRS\Test;DBQ=C:\YRS\Test\Split\YRS_be.mdb"

tmpqry = "select org_name from tbltest"
Word_Obj.MailMergeOpenDataSource Name:=tmpsourcedbbe, Connection:="Query tmpnick", LinkToSource:=0

Word_Obj.MailMerge CheckErrors:=2, Destination:=0, MergeRecords:=0, Suppression:=0, MailMerge:=0

Select Case Word_Obj.MailMergeState(0)
Case 0
MsgBox "Not a mail merge main document."

Case 1, 3
For i = 1 To Word_Obj.countdocumentproperties
Debug.Print "Property (" & i & &quot = " & Word_Obj.documentpropertyname$(i) & " = " & docval

Next i
Debug.Print Word_Obj.MailMergeMainDocumentType

Case Else
End Select

End Sub

OK, the aim of the game is for Access to open the document in word (which it does), alter the document (using 'verb' and 'Action') so that it becomes a mailmerge main document (which works), then to attach a datasource to it (A query in the current database). However (and this is the shortened version of the code, as the stuff I took out didn't work), even though the "Word_Obj.MailMergeOpenDataSource" command specifies :

Connection:="QUERY myquery"

the word document still asks for a table to be selected from a list. Believe me I have tried ConnectionStrings and they don't work either.

Does anyone have any suggestions?

Also, here is a list of the references that I am using, just so you know beforehand :

Visual Basic For Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft Flexgrid Control 6.0 (SP3)
Microsoft Windows Common Controls-2 6.0 (SP4)
Microsoft Common Dialog Control 6.0 (SP3)
Microsoft Excel 10.0 Object Library
Microsoft Word 10.0 Object Library
Microsoft Windows Common Controls 6.0 (SP4)

Please please help.

Mr Unhappy. Dont be small. be BIG

 

[ormsk]

Dear aarrgh etc

I am by no means an expert in Access. However, I had cause to create WORD documents from an Access table and have had success, but not by using mail-merge.

Providing you know where you want to put the variable data these are the steps:

1. Create a master document with bookmarks where you want your data to appear
2. Give the bookmarks meaningful names (e.g. bkCustomerName) etc
3. Dim all the bookmarks in VB as string
e.g. Dim bkCustomerName As String

'Code Snippet
Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim db As Database
Dim bkCustomerName As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryCustomer&quot

Set wordApp = CreateObject("Word.Application&quot

bkCustomerName = rs![Customer Name]


With wordApp
.Documents.Open ("c:\MyPath\MyFile.doc&quot
.Visible = True
.ActiveDocument.SaveAs NewFileName ' so master file is not changed
.ActiveDocument.Close
.Documents.Open NewFileName
End With

With wordApp.ActiveDocument
.GoTo wdGoToBookmark, , , "bkCustomerName "
.Bookmarks("bkCustomerName &quot.Select
.Bookmarks("bkCustomerName &quot.Range.InsertAfter bkCustomerName
.Bookmarks("bk001Customer&quot.Range.Cut
End With

wordApp.ActiveDocument.Close wdSaveChanges

wordApp.Quit

Set wordApp = Nothing
rsQHeader.Close
db.Close
Set rsQHeader = Nothing
Set db = Nothing

This is a little hurried as I am having to leave work and I have not tested this. Please advise if it is OK or not. Tomorrow I will test it.

Rgrds

Why did kamikazi pilots wear helmets?

 

[aaahhhrrggghhhh]

Hi Ormsk,

Thanks for the code, but I feel that setting bookmarks in the word document would be too much trouble both for me and for the users, in the long run.

I am hoping that somebody has faced this problem themselves and resolved it.

I need for the users to just be able to type in a standard letter in word, then Access will connect the word document to the data that they want to merge with, then they can merge it within word, but using a reference (CreateObject) to the word application.

The users have many hundreds of different types of letters, so to set up bookmarks within each one would be too strenuous for them (they will have to learn a new concept!).

Another point is that I would prefer to give the users a list of fieldnames (as when you click "Insert Database Field", all the datasource fields appear), rather then have them create their own bookmark names (as I can't 100% say that they will only ever stick to "CustomerName", "AddressLine1" etc), this is too risky.

It is a good idea, though, in concept, but in practice it wouldn't work.

I much appreciate your help, though.
Regards Dont be small. be BIG