Automate Mail merge from VFP 9.0 Using Word 2003 or 2007 2

[SergeantC]

I'm using VFP9SR2 and trying to do a mail merge 2ith MSWord2003.
My problem is that under code my template will not automatically merge. I have to manually select the Excel Table from the merge tool bar then view the merged data.

If I open it from Word, or double click it from windows Explorer, Word brings up a Dialog box asking if I want to run an SQL command.

I've apparantly done something stupid with the link to the mail letter and the data source but, I don't know what.

Any help will be greatly appreciated. I'm a rookie at this!

I'm using the following code:
#DEFINE wdMainAndDataSource 2
#DEFINE wdSendToPrinter 1
#DEFINE wdDoNotSaveChanges 0

CLOSE TABLES all
USE ccauth
COPY TO ccauth TYPE xl5
CLOSE TABLES all


oWord = CREATEOBJECT("Word.Application")
oWord.Visible = .t.

** change the path and file name to suit
oWord.Documents.Open("C:\_0_applications\myTempl.dot")

** check to make sure the documnet is attached to a
** datasource and ready to merge.
If oWord.ActiveDocument.MailMerge.State = wdMainAndDataSource
oWord.ActiveDocument.MailMerge.DataSource.FirstRecord = 1
oWord.ActiveDocument.MailMerge.DataSource.LastRecord = 1
oWord.ActiveDocument.MailMerge.Execute
endif

Code I'm planning to add later:
*oWord.ActiveDocument.PrintPreview()
*oWord.Quit(wdDoNotSaveChanges) && close file no save
release oWord

** property to retrive OR set the first record to print
*oWord.ActiveDocument.MailMerge.DataSource.FirstRecord

** property to retrive OR set the last record to print
*oWord.ActiveDocument.MailMerge.DataSource.LastRecord

 

[TamarGranor]

Are you sure the Excel file you're creating is the right name and in the right place? That is, that it's the file the document is looking for as a data source?

Tamar

 

[SergeantC]

Thank you for your response I appreciate your time.

It appears the Excel file has the correct place and the document recgonizes it and its location. If I double cllick on the file name from Microsoft Windows Explorer the document opens, I get an a MS Word dialog box stating
"Opening this document will run the following SQL command: SELECT * FROM 'ccauth$'
Data from your database will be placed in the document. Do you want to continue?
When I answer yes the old data is replaced with the new data and the document opens. I have changed the data in the VFP table prior to running the code, and it copies it to the Excel file in the correct location, but when Word opens under VFP code I do not get the Word SQL dialog box.

Should I be using GETOBJECT(), I'm not fimiliar with it and from the command line when I use it Word starts in the back ground I get the SQL window but cannot make word show from VFP. I know its there as it appeaars as a Process in Windows Task Manager, but does not appear as an Application. That tells me I've done something stupid but have not been able to find a solution.

Thanks again for you time!

 

[Dan Freeman]

Interesting. I never knew you even COULD do mailmerge with a document _TEMPLATE_.

I suspect you really want to create a document from the template, and attach your data source to the new document.

 

[SergeantC]

Thank you
Actually I've tried it with the document and with the template and cannot get either to work.I tried just using
oWord=getobject("c:\mypath\mydoc.doc"), made word visible and the document active. From the command line it works, from a .prg file I get not one but two open documents. Figured the GETOBJECT method may not be stable.
I'm snowed.

I really appreciate your time, thank you again

 

[Dan Freeman]

Was the doc based on the dot?

You do understand, I hope, that these are NOT the same kind of file at all. You cannot make a valid dot by just renaming a doc, and it doesn't work the other way either.

There *IS* some funkiness about DOT files (templates) as mailmerge documents, but I can't for the life of me remember what it was. I do remember some chatter among the Word MVPs following the last SP for office, though.

You should be starting with a blank DOC (*not* DOT) file, not a DOC file based on a DOT-with-data-source.

 

[SergeantC]

Thanks,
I'll create a new .DOC from a blank. I messed with this so much over the last week or so I'm not sure, but there is a very good chance the .DOC was based on a Template (.DOT) or even a Word2007 .DOTX.

Thank for your hint, I really appreciate your time and effort

 

[SergeantC]

I created a new mail merge document from a blank document saved it as a .DOC

From the command line I did the following:

*update EXCEL file used as merge data
SELECT mydata.dbf
SET SAFETY OFF
COPY TO mydata TYPE xl5

RELEASE ALL LIKE o*
LOCAL oWordDoc, oDoc
oWordDoc = LOCFILE(myMergeDoc.doc')
oWordDoc = "'"+oWordDoc+"'"
oDoc = getobject( &oWordDoc ) and rean it
oDoc.MailMerge.execute()
oDoc.Application.Visible = .t.
oDoc.Application.Activate

I got ask if I wanted to run the SQL, said YES, and got one instance of MS Word that did not appear on the Task Bar or Screen. When I told it .Visible = .t. it did appear on the screen as I expected. When I entered the oDoc.Application.Activate the oWordDoc updated with current data.

I then put the code in a .PRG I got ask to run the SQL, said YES, got updated data in the myMergeDoc.Doc but I also have a second instance with a title of "Form Letters1" with the current data.

When I try the CREATEOBJECT("Word.Application") and oWord.Documents.ADD(ADDBS("c:\myFoxApps\myMergeDoc.doc")
I get the document but I never get the run SQL box, and have to manually update the data.

Thanks again for your time. I have not messed with mailmerge since 1995 and now I know why. Using it is one theing, but Automating it from VFP is another.

 

[Dan Freeman]

Couple of things:

* I don't think you want Locfile(). It messes with the Foxpro search path. I suspect you want Getfile() instead.

* You don't need to add the quotation marks, and you don't need the &macro

* You don't want AddBS() on a *file* name. It's for directories.

On your second instance of word, I would expect that because I don't see you releasing the first one anywhere. (oWord.Quit) You may see different results if you stick to just one instance.

Is there a reason you're using documents.add() instead of documents.open() for an existing document?

 

[SergeantC]

I used LOCFILE() because my user would not know which document to pick, and there is a 99.9% chance the document file is in VFPs current directory. GETFILE() starts in the current directory and shows the file extensions requested. I can't rely on the user knowing a file name. I just don't know for sure what the directory name might be.

I changed my code to:
RELEASE ALL LIKE o*

oDoc = getobject("C:\_0_applications\myDoc.doc")

oDoc.MailMerge.Execute()

oDoc.Application.Visible = .t.

This worked well from the command line. I got the SQL window then with Visible=.T. saw only the myDoc document with updated data. I then copied the code from the command window to a PRG file. and opened Windows Task Manager.

With Windows Task Manager open, I started the VFP Debugger and stepped down through the code with the following results:

At the code line oDoc = GETOBJECT( &oWordDoc) -- the Task Manager Processes Tab shows WINWORD.EXE and I get the SQL Dialog box where I select yes and the Debugger stepps to oDoc.MailMerge.EXECUTE() The Task Manager Applications Tab does not show a Word Application.

When I step out of the oDoc.Applications.Visible = .T. code line -- Task Manager shows one WINWORD.EXE Process in the Process Tab, and myDoc.Doc - Microsoft Word Running along with Form Letters1 - Microsoft Word Running under the Applications Tab of the Task Manager.

If I use oDoc.Quit after making VFP active The Form Letters1 and myDoc ask if I want to save them. I may be wrong but that tells me something is opening two instances of Word and tying them both to oDoc.

I tried this earlier this AM --
oWord=CREATEOBJECT("word.application")
oWord.documents.open("C:\myPath\myDoc.Doc")
oWord.Visible = .T.

I got the myDoc in Word but no SQL update request and the document had the previous sessions data in it.

Thanks for your time and effort I appreciate it very much

 

[TamarGranor]

When you perform a mail merge, there should be two open documents at the end. One is the mail merge main document, that is, the one that contains the document to be merged. The other is the result of the mail merge. In my experience, oWord.ActiveDocument points to that document after the merge.

Tamar

 

[SergeantC]

Tamar,
Thank you for the information and your time. It is working just as you describe, and apparantly that's notmal.

Again I appreciate your tiem and effort in this. I works both using Word 2003 and 2007.

SergeantC