MailMerge Problem 1

[SoftArt]

Hi,
I want to open an existing Word-Mailmerge-Document and change data source.

loWord = CREATEOBJECT( "Word.Application" )
loWord.Visible = .T.
loDoc = loWord.Documents.Open( lcWord, 0, 1 ) && ReadOnly
loDoc.MailMerge.OpenDataSource( lcFileName, 0,0,0,0,0,"","",0,"","","","SELECT * FROM [" + JUSTSTEM( lcFileName ) + "$]" )

This code workes fine if there are no more Word intances opened. If not and the user close this document, Word will write back to Normal.DOT

How can solve this?

Thomas

 

[cbasoz]

You can prevent normal.dot from saving by marking it as saved:

loWord.NormalTemplate.Saved = .T.

Instead of opening the source as readonly (you might still open as readonly), you could Add a new document based on that m.lcWord. Then that would be used as a "template":

loDoc = loWord.Documents.Add( m.lcWord )

Also why are you saving to an XLS and using it as source? You could directly use VFP data or a simple txt file.

Cetin Basoz
MS Foxpro MVP, MCP

 

[SoftArt]

Cetin,
I tried to use a txt file and it works same like an excelsheet. BTW, how can I use VFP-Data directly?

I saw this code loWord.NormalTemplate.Saved = .T.
in other forums and tried it before wrote this thread.
The result was same like without.

Also loDoc = loWord.Documents.Add( m.lcWord ) bring up the message to save to Normal.DOT. My problem is always the same if any other word instance is open. One more idea?

Thomas

 

[cbasoz]

Here is a sample code that I use. This works with old and new word versions (2000,2003,2007 and possibly with 2010 and later). I tried this one again while another two word sessions are open and it didn't ask for normal.dot (I also have DisplayAlerts = 0 besides .NormalTemplate.Saved=.T.):

*!*	Author: Cetin Basoz
*!*	MailMerge sample that works with newer versions too
Wait Window Nowait "Creating Word Document.Please wait..."

#Define wdOpenFormatAuto	0
#Define wdSendToNewDocument	0
#Define wdSendToPrinter	1
#Define wdSendToEmail	2
#Define wdSendToFax	3

#Define wdAlertsNone	0


lcConnection = ;
 "Provider=VFPOLEDB;Data source="+;
  _samples+'data\testdata.dbc'
Strtofile('','myDummy.udl')
m.lcSource = Fullpath('myDummy.udl')

lcSQLStatement = ;
 "select e.First_Name,e.Last_Name,e.Notes from [Employee] e"

*** set the LOCALEID to English
nlLocaleId=Sys(3004)  && Save local id
=Sys(3006,1033)    && We will be sending instructions in English
*** set the LOCALEID to English

oWordDocument=Createobject("word.application") && Create word object
With oWordDocument
  .documents.Add
  With .ActiveDocument.Mailmerge
    .OpenDataSource(m.lcSource, wdOpenFormatAuto,.F.,,.T.,,,,,,,m.lcConnection, m.lcSQLStatement)
    .EditMainDocument	&& Activate the main document


    *!*	Add some merge fields and typ text programmatically
    .Fields.Add(.Application.Selection.Range, "Last_Name")
    .Application.Selection.TypeText(", ")
    .Fields.Add(.Application.Selection.Range, "First_Name")
    With .Application.Selection.Font
      .Italic = .F.
      .Name = 'Arial'
      .Size = 10
    Endwith
    .Application.Selection.TypeText(Chr(13)+"Here is your notes data :"+Chr(13)+Chr(13))
    With .Application.Selection.Font
      .Bold = .T.
      .Color = Rgb(0,0,255)
    Endwith
    .Fields.Add(.Application.Selection.Range, "Notes")
    With .Application.Selection.Font
      .Bold = .F.
      .Color = Rgb(0,0,0)
    Endwith
    *    .Destination = wdSendToNewDocument
    *    .Execute()

  Endwith

  .Visible = .T.   && Show word app
  .Activate
  .DisplayAlerts = wdAlertsNone
  .NormalTemplate.Saved = .T.
Endwith
**** Set the LocaleId to the previous value
=Sys(3006,Val(nlLocaleId))

PS: Note that [Employee] e and aliased fieldnames in SQL is crucial (undocumented trick to make it work - otherwise the only thing that works is *).

Cetin Basoz
MS Foxpro MVP, MCP

 

[SoftArt]

Alias fieldnames ist good to know, thanks.
Can you send me your code via mail, because I don´t know how to handle the forums-formatting.
-> info@softart-online.de

Can you try my code, please. I want to know if this could be a problem on my pc.

Thomas

 

[cbasoz]

I don't see your code.

No need to send via email.

-Select the code and press
[Ctrl+C]
-Launch VFP
-In command window
mc[spacebar][enter]
[Ctrl+V][Ctrl+A]
-Right click and select 'Execute selection'

Cetin Basoz
MS Foxpro MVP, MCP

 

[SoftArt]

The problem is not copy and paste but rather the format. It´s an endless string, VFP can´t execute. Did you tried this yourself?

I modified my code:
loWord = CREATEOBJECT( "Word.Application" )
loWord.DisplayAlerts = 0
loWord.NormalTemplate.Saved=.T.
loWord.Visible = .T.
loDoc = loWord.Documents.Open( lcWord, 0, 1 )loDoc.MailMerge.OpenDataSource( lcFileName, 0,0,0,0,0,"","",0,"","","","SELECT * FROM [" + JUSTSTEM( lcFileName ) + "$]" )

Can you test this code? It´s works different in IDE and EXE. In EXE it will ever write back to Normal.DOT and I don´t know why. Which part of this code changed the template?

Thomas

 

[SoftArt]

My mistake, IDE and EXE works same!

 

[PieroF62]

Hi,
i have the same problem when printing using a virtal pdf printer :

wd = CREATEOBJECT("Word.Application")
* wd.NormalTemplate.Saved = .T. && 1° position
wd.VISIBLE = .F.
wd.Documents.Open("c:\mydocument.doc")
cPrinterName = wd.activeprinter()
wd.ActivePrinter = "my virtual pdf printer..."
wd.printOut()
wd.ActivePrinter = cPrinterName
wd.NormalTemplate.Saved = .T. && 2° position
wd.quit()

in IDE and in EXE if i use the 2° position, WORD dont ask me for save normal.dot.

Piero

 

[SoftArt]

Hi,
this is interesting but I don´t quit my Word-instance myself, the user do this after printing, previewing or something else. It looks like Word must do something before the flag ..NormalTemplate.. works correctly. BTW I try to do something ,set the flag and look what happened.

thomas

 

[derren]

A big thank you to Cetin, your two tips about instancing the table

t and then specifying the fields as t.field, along with the creation of the dummy udl file has been a Godsend.

My mail merge had worked for years on many computers, then suddenly stopped working in what I can only guess is down to some windows updates. After weeks of (on and off) testing I remembered Tek-Tips, where I used to frequent, found your post and within an hour I had a new software version out.

You deserved your star!

Derren
[Mediocre talent - spread really thin]

 

[cbasoz]

Derren,
Thanks for your kind words. I know how big suffer it is to see mailmerge stops working with almost each new version of Office

PS: I plan to test this code with Office 2010 next week.

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

Update:
Windows 7 64 bits
MS Office 2010 Professional Plus 64 bits

It doesn't work I didn't dig yet for what might be the cause.

Cetin Basoz
MS Foxpro MVP, MCP

 

[cortiel]

word throws an error, it cant open the data file in the opendatasource statement

64 bit, VFP9SP2 windows 7, office 2010

Peter

PAC

 

[cbasoz]

Yes, check my message on '3 May 10 18:09'

Cetin Basoz
MS Foxpro MVP, MCP