Wiki Home

Word Automation Print Merge


Namespace: VFP
I have revisited this. In doing so I have found a MUCH cleaner way to automate word from VFP and attach a data source.

The main issue is that if you don't get it exactly right, the user is presented with a system dialogue box.

This is pretty simple: Pass in the full path to the document file, and the excel file. Also pass in the name of the worksheet from the workbook that you want to use as merge data. I only got it to work when the worksheet name matched the workbook name, but I didn't fiddle around with it too hard.

I didn't get this to work with a named range, or using a R1C1 type references. IT also doesn't seem to work with docx files. :(

NOTE What you MUST get right are: the weird backward single quotes around the sheet name AND the $ at the end of the sheet name. I didn't know how to type the quotes so I ended up cutting and pasting them into the code ... and was shocked when it actually WORKED!! ?cs

function openDataSource( tcDocFile, tcXLSFile, tcSheet ) && tcSheet could also be a named range

  local loWord, loMerge, lcSheet
  loWord = createobject( 'word.application' )
  loWord.Document.Open( m.tcDocFile )

  loMerge  = m.loWord.ActiveDocument.MailMerge
  lcSheet = "SELECT * FROM `"+m.tcSheet+"$`"

  loMerge.openDataSource(m.tcXLSFile,,,,,,,,,,,'Entire Spreadsheet',m.lcSheet)

endfunc


In a nutshell, this explains how I automate word 2003, attach an excel 2003 data source, and complete a print merge without user or programmer intervention. cs
This solution has a story to go along with it: I have a web site where users keep track of participants in a medical protocol. Users produce mail merges based results of recent treatement. The web site is on a very secure machine where I am not allowed by network authority to install word or excel, though I can place both file types on the secure server. However, I can access the data on the secure server from a machine that has the complete Office 2003 suite.

Provided within the web application is a form where users can request a particular mail merge. The form adds a record to a queue, a fox pro table. A listner program, running on a computer with a less restrictive installation requirement, periodically checks for new print merge request in the queue. When a new request is discovered, the listner launches the automated print merge routine below to produce the document, and then updates the queue to reflect that the merge has been completed.

Complications: I needed a way to merge a Word 2003 with an Excel 2003 spreadsheet in an unattended mode, and I could not seem to avoid dialogue boxes. Though I considered using a different file format for my data source, I stubbornly felt I wanted to utilize the Office 2003 suite as it should work. I read the approachs in "Microsoft Office Automation with Visual FoxPro" and also one in the "1001 things you Wanted to Know About Visual FoxPro" I felt those solutions were utilizing the power MS had built (clunkily) into the office suite.

So here are the things I did to set up my word templates.

1. Created a normal word document with the merge fields embedded

Created sample excel data containing fields I needed for merging with word
Created the document
Used the word interface to attach the excel data to the word document
Inserted all merge fields needed into the word document
Converted the word document to a NORMAL word document. The merge fields remain, but if printed will print as literals, not as merged data.

Here is what Word 2003 says about converting a merge template document into a normal document:

"By removing the associated data source from a main document, you can convert it to a regular document.

-Open the main document from which you want to remove the data source.
-On the View menu, point to Toolbars, and then click Mail Merge.
-Click Main Document Setup button.
-Click Normal Word document radio selection.
-Click Okay."



2. Set my macro security lower than I like and allowed access to VBA Project (from word look under options security follow the macro security button edit both tabs in the "Security" dialogue that pops up enable access to the VBA project)

3. Wrote a routine to attach a data source to a word document on the fly from Fox and then to do the print merge.

4 Wrote a routine to check for consistency between the data source attached and the word document. Using my methodology you can have some inconsistencies that are show stoppers if you do not do this step. See: Data Source Consistency Check

My methods are not without drawbacks. We should not have to use automation to add a macro to attach a data source for the soul purpose of avoiding a dialogue during the print merge. I did not find an easier way and would welcome one. I was completely happy to find a way that worked at all! I also do not like the requirement of needing to trust access to the VBA Project, however did not find a way around it. In my particular situation that is not an issue, but for many people that would be a show stopper.

?cs

* --------------------------------------------------------------------------------------
* Print Merge
* --------------------------------------------------------------------------------------
function PrintMerge( )

  local lcTemplateDoc, lcTemplateCopy, lcMergeData, llContinue
  local lcTempMacro, lcMergedDoc, loWord, loDocument, loMergedDocument

  * --------------------------------------------------------------------------------------
  * In production these parameters are passed in from a parameter object, spelled
  * out here for clarification
  * --------------------------------------------------------------------------------------

  llContinue = .t.

  lcTempFolder       = [c:\temp] && temporary folder

  lcTemplateDoc      = [c:\template\MergeTemplate] && actual word document merge template, keep templates in a seperate folder
  lcTemplateCopy     = [c:\temp\TemplateCopy] && copy of the word document merge tempate

  lcTempMacro        = [c:\temp\AttachSourceMacro.txt] && macro to attach source data
  lcMergeData        = [c:\temp\ExcelData.xls] && source data

  lcMergedDoc        = [c:\result\MergedDocument.doc] && output document in result folder

  * ----------------------------------------------------------------------------------------------------------------
  * Checking for files omitted for simplicity
  * ----------------------------------------------------------------------------------------------------------------
  this.createMacroTextFile(m.lcTempMacro, m.lcMergeData )

  * --------------------------------------------------------------------------------------
  * work with a copy of the template word document, that way if things go
  * bad you really haven't hurt anything
  copy file(m.lcTemplateDoc + [.doc]) to (m.lcTemplateCopy + [.doc])


  * --------------------------------------------------------------------------------------
  * Error trapping code not included here, but you DO want it since
  * it is very easy for things go wrong at any of these steps
  * --------------------------------------------------------------------------------------

  * --------------------------------------------------------------------------------------
  * Open word automation object
  loWord = createobject([word.application])

  * --------------------------------------------------------------------------------------
  * Open copy of template
  loDocument = m.loWord.Documents.open( m.lcTemplateCopy)  && open template copy

  * --------------------------------------------------------------------------------------
  * Macro security and access to VBA Project must be enabled for this step
  * See word options security tab.
  loDocument.VBProject.VBComponents("ThisDocument").CodeModule.AddFromFile(m.lcTempMacro)

  * --------------------------------------------------------------------------------------
  * attaches the text subroutine in the text file to the word document
  loWord.run("AttachSource")

  * --------------------------------------------------------------------------------------
  * at this stage I do have a routine for checking the document validity I run
  * prior to executing the merge, it can be found under the wiki topic: Data Source Consistency Check

  * --------------------------------------------------------------------------------------
  * perform the merge
  loDocument.MailMerge.Execute

  * --------------------------------------------------------------------------------------
  * save and close down merged document
  loMergedDocument = m.loWord.ActiveDocument          && Focus to the merged document
  loMergedDocument.saveas( m.lcMergedDoc )            && Save with the outputfile name
  loMergedDocument.close(0) && close w/o prompt

  * ----------------------------------------------------------------------------------------------------------------
  * save and close down copy of word template document
  * ----------------------------------------------------------------------------------------------------------------
  if vartype(m.loDocument) = [O] && only close if it is an object
    loDocument.close(0) && close w/o prompt
  endif

  * --------------------------------------------------------------------------------------
  * Release objects
  * --------------------------------------------------------------------------------------
  loDocument = .null.
  loMergedDocument = .null.
  loWord.quit
  loWord = .null.
  release loWord

  * --------------------------------------------------------------------------------------
  * Throw away copy of template document and template macro
  * --------------------------------------------------------------------------------------
  delete file (m.lcTemplateCopy + [.doc])
  delete file (m.lcTempMacro)

endfunc

function createMacroTextFile(tcFile,tcSource)
  * --------------------------------------------------------------------------------------
  * NOTE: the SQL statement parameter:
  *
  *		SQLStatement:="SELECT * FROM `<>$`
  *
  * The "$" is very important.  I lost it once and it took me a long time to figure
  * out that I needed it.  The way I created this connection string was to record a
  * macro in word where I attached an excel file to a word document as a data source,
  * I then examined the macro text generated.
  * --------------------------------------------------------------------------------------

  local lcMacroText, lcSourceFile, lcSource, lcSourceFile

  * parse the Source into the path, file and path + file + extention (always excel)
  lcSource     = alltrim(tcSource)
  lcSourceFile = juststem(m.lcSource)
  lcSourcePath = justpath(m.lcSource)
  lcSource     = m.lcSourcePath + [\] + m.lcSourceFile + [.xls]

  * --------------------------------------------------------------------------------------
  * The following code puts creates a variable containing the text of a VBA subroutine
  * --------------------------------------------------------------------------------------
  * The following is the text of a VBA subroutine that, when run in word, attaches the word
  * document to the data defined in the connection string.
  * --------------------------------------------------------------------------------------

  text TO lcMacroText TEXTMERGE NOSHOW
Sub AttachSource()

    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "<>" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=<>;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet O" _
        , SQLStatement:="SELECT * FROM `<>$`", SQLStatement1:="" _
        , SubType:=wdMergeSubTypeAccess

End Sub
  ENDTEXT

  strtofile(m.lcMacroText,m.tcFile) && store the variable to a file on disk

  debugout m.lcMacroText

endfunc


Carole Shaw
Category Code Samples Category Automation
( Topic last updated: 2014.11.25 02:50:54 PM )