Word mailmerge with Excel database
Posted: Tue Dec 29, 2015 3:17 pm
Hi to all,
I need to print mailmerge Word document with an Excel database into a fivewin+xharbour executable.
This is an example that works but requires manual connection to the Excel sheet, it is possible that the connection is selected by the code?
https://drive.google.com/file/d/0B6Oxri ... sp=sharing
This is my code:
Thank you for help.
I need to print mailmerge Word document with an Excel database into a fivewin+xharbour executable.
This is an example that works but requires manual connection to the Excel sheet, it is possible that the connection is selected by the code?
https://drive.google.com/file/d/0B6Oxri ... sp=sharing
This is my code:
Code: Select all
#include "FiveWin.ch"
PROCEDURE Main
LOCAL nError, nRek, nDoc := 0, oIni, cDoc, cXls, cTbl
INI oIni File ".\prova.ini"
cDoc := oIni:Get( "Default", "Document", CurDrive() + ":\" + CurDir() + "\Prova.doc" )
cXls := oIni:Get( "Default", "DataBase", CurDrive() + ":\" + CurDir() + "\Prova.xls" )
cTbl := oIni:Get( "Default", "Tabella", "Foglio1" )
FOR nRek := 10 TO 11
// devo stampare un documento per volta!
PrnDocMM( nRek, cDoc, cXls, cTbl, nDoc )
NEXT
RETURN
//------------------------------------------------------------------------------
#define wdSendToPrinter 1
#define wdSendToNewDocument 0
STATIC FUNCTION PrnDocMM( nRek, cFileDoc, cDataSource, cTable, nDoc )
LOCAL oWord, oFile, nReturn := 0
SysRefresh()
// Connessione Word
TRY
MsgInfo( "Apre Word" + LTrim( Str( nRek ) ), "Test" )
oWord := CREATEOBJECT( "Word.Application" )
CATCH
nReturn := 1
END
? nReturn
IF nReturn == 0
// Apertura File
TRY
MsgInfo( "Apre Doc " + LTrim( Str( nRek ) ), cFileDoc + IF( File( cFileDoc ), " OK", " KO" ) )
oFile := oWord:Documents:Open( cFileDoc )
CATCH
nReturn := 2
END
? nReturn
IF nReturn == 0
// Collegamento DataSource
TRY
MsgInfo( "Collega dati" + LTrim( Str( nRek ) ), IF( File( cDataSource ), " OK", " KO" ) )
oFile:MailMerge:OpenDataSource( cDataSource ) // , , , , , .F., "", "", .F., "", "", "Data Source="+cDataSource, "SELECT * FROM '" + cTable + "$'" )
// virtual HRESULT STDMETHODCALLTYPE OpenDataSource(BSTR Name/*[in]*/,
// VARIANT* Format/*[in,opt]*/= TNoParam(),
// VARIANT* ConfirmConversions/*[in,opt]*/= TNoParam(),
// VARIANT* ReadOnly/*[in,opt]*/= TNoParam(),
// VARIANT* LinkToSource/*[in,opt]*/= TNoParam(),
// VARIANT* AddToRecentFiles/*[in,opt]*/= TNoParam(),
// VARIANT* PasswordDocument/*[in,opt]*/= TNoParam(),
// VARIANT* PasswordTemplate/*[in,opt]*/= TNoParam(),
// VARIANT* Revert/*[in,opt]*/= TNoParam(),
// VARIANT* WritePasswordDocument/*[in,opt]*/= TNoParam(),
// VARIANT* WritePasswordTemplate/*[in,opt]*/= TNoParam(),
// VARIANT* Connection/*[in,opt]*/= TNoParam(),
// VARIANT* SQLStatement/*[in,opt]*/= TNoParam(),
// VARIANT* SQLStatement1/*[in,opt]*/= TNoParam()) = 0; // [103]
CATCH
nReturn := 3
END
? nReturn
IF nReturn == 0
// seleziono il record e stampo
TRY
WITH OBJECT oFile:MailMerge
:DataSource:FirstRecord = nRek
:DataSource:LastRecord = nRek
:Destination = wdSendToPrinter
// :Destination = wdSendToNewDocument
:SuppressBlankLines = .T.
:Execute()
END // With
// oWord:ActiveDocument:SaveAs2( ".\MyDoc" + PadL( nDoc, 3, "0" ) + ".doc", 0 ) // 0 = .doc; 16 = default=>docx; 17 = pdf
// oFile:SaveAs2( "s:\Consorzi\Stampaunione\MyDoc" + PadL( nDoc, 3, "0" ) + "", 0 ) // 0 = .doc; 16 = default=>docx; 17 = pdf
// oFile:SaveAs2( ".\MyDoc" + PadL( nDoc, 3, "0" ) + "", 0 ) // 0 = .doc; 16 = default=>docx; 17 = pdf
// ? "Stampato"
CATCH
nReturn := 4
END
ENDIF
oFile:Close()
ENDIF
oWord:QUIT()
ENDIF
SysRefresh()
RETURN nReturn