[Solved) Word-Excel mail merge
lazarus / 34-Windows

----------------------------------------------------------------------
folkeu08 · 2024-09-22 19:28

Hi

I created a word file containing merge fields from an Excel file.
By running the commands in the Word banner, the merger works.

I'm trying to automate this with OLE but I get this message on the
mailmerge command: "This method or property is not available because
the document is not a main merge document".

Thanks for your help.
Fanch

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
officAuto; {$IFDEF FPC}  {$MODE Delphi}{$ELSE}  {$APPTYPE
CONSOLE}{$ENDIF} uses   SysUtils, Variants, ComObj; var  Server,
Connect : Variant;  oWord, oPara0, oPara1, oPara2 : Variant;  
w:widestring;   begin    if Assigned(InitProc) then   
TProcedure(InitProc);   try    Server :=
CreateOleObject('Word.Application');  except    WriteLn('Unable to
start Word.');    Exit;  end;    // oWord := Server.Documents.Add; 
w:= UTF8Decode('c:\mydoc.docx');  Server.Visible := True;   
Server.Documents.Open(w);    [color=blue]oPara0 :=
Server.ActiveDocument.Mailmerge.execute;  [/color]   oPara1 :=
Server.ActiveDocument.Content.Paragraphs.Add;  oPara1.Range.Text :=
'This is a Heading';  oPara1.Range.Font.Bold := True; 
oPara1.Format.SpaceAfter := 24; 
oPara1.Range.InsertParagraphAfter();   oPara2 :=
Server.ActiveDocument.Content.Paragraphs.Add;  oPara2.Range.Text :=
'Where will this appear if at all!';  oPara2.Range.Font.Bold :=
False;  oPara2.Format.SpaceAfter := 24; 
oPara2.Range.InsertParagraphAfter();end.

----------------------------------------------------------------------
egsuh · 2024-09-23 02:47

I do not know much, but is two dots (..) correct?

----------------------------------------------------------------------
folkeu08 · 2024-09-23 19:58

It's good in my Lazarus. I have add a dot in my "copy,-paste' of the
code.
I try to convert an example in VBA to Pascal with copilot or other.
Thanks

----------------------------------------------------------------------
rvk · 2024-09-24 09:13

You are opening the mydoc.docx file as just a word file. Not a merge
document.

To make that file a merge document you need to add
MailMerge.OpenDataSource call to open the actual datafile.

So add this above the Mailmerge.execute and it should work.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Server.ActiveDocument.MailMerge.OpenDataSource('C:\path\mydata.mdb');
// <-- or other filetype
BTW. You also execute the Mailmerge and AFTER that you are adding
text.
You should do that the other way around. The Mailmerge.execute should
be last (after which the actual merging is done).

----------------------------------------------------------------------
folkeu08 · 2024-09-24 15:13

Thanks rvk for your help.

My data source is already in SQLite tables and with fpspreadsheet I
can export them to an excel format file.
This is why I use this file type. I don't know if there is anything to
convert to access format.
I posted earlier (when I got home) my source which works with an Excel
file but it asks me in which sheet it should look for the data.
I try to tell it that it is sheet 1 and I can then use Excel as a data
source.
Thanks
Fanch

----------------------------------------------------------------------
rvk · 2024-09-24 15:51

Quote from: folkeu08 on September 24, 2024, 05:13:46 pmThis is why I
use this file type. I don't know if there is anything to convert to
access format.You missed the point there. You don't need an mdb file.
But you DO need to use MailMerge.OpenDataSource.
Otherwise your document isn't seen as mail-merge document.

You can just use a .xls(x) for your datasource.
And if you don't want to get the Sheet-question, you need to add a
SQLStatement parameter to the OpenDataSource call.

Like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Server.Acti
veDocument.MailMerge.OpenDataSource('C:\path\your_data_source.xlsx',
SQLStatement := 'SELECT * FROM `Sheet1$`');oPara0 :=
Server.ActiveDocument.Mailmerge.execute;
Hope that helps.

----------------------------------------------------------------------
folkeu08 · 2024-09-24 21:31

Hi rvk,

I have write this code :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Main_unit; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
Forms, Controls, Graphics, Dialogs, StdCtrls, ComObj, Variants;
 //WordXP; type   { TForm1 }   TForm1 = class(TForm)    Button1:
TButton;    procedure VBA_vers_Lazarus_OnClick(Sender: TObject); 
private   public   end; var  Form1: TForm1; implementation {$R
*.lfm} { TForm1 } //**************************************************
****************************//                                Bouton 1
//********************************************************************
********** procedure TForm1.VBA_vers_Lazarus_OnClick(Sender:
TObject);  var    oApp: Variant;    oDoc: Variant;    oAutoText:
Variant;    sourcedata :string;  begin    sourcedata :=
UTF8Decode(ExtractFilePath(Application.Exename) +
'/pige/convention_export.xlsx');    oApp :=
CreateOleObject('Word.Application');    oApp.Visible := True;     oDoc
:= oApp.Documents.Open(UTF8Decode(ExtractFilePath(Application.Exename)
+ '/pige/Convention_diagnostic.docx'));     Showmessage('1');     //oD
oc.MailMerge.OpenDataSource(UTF8Decode(ExtractFilePath(Application.Exe
name) + '/pige/convention_export.xlsx')); // Spécifiez la source de
données ici     Showmessage('2');     //oDoc.MailMerge.OpenDataSource(
UTF8Decode(ExtractFilePath(Application.Exename) +
'/pige/convention_export.xlsx'), '', '', '', '', '', '', '', '', '',
'', 'SELECT * FROM "Feuil1$"', '', '', '');     Showmessage('3');    
//oDoc.MailMerge.OpenDataSource(ExtractFilePath(Application.Exename) +
'/pige/convention_export.xlsx', '', '', '', '', '', '', '', '', '',
'', '', 'SELECT * FROM Feuil1$', '', '', '');     Showmessage('4');  
 //oDoc.MailMerge.OpenDataSource(Name:=sourcedata,
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True,AddToRecentFiles:=False, PasswordDocument:='',
PasswordTemplate:='', WritePasswordDocument:='',
WritePasswordTemplate:='', Revert:=False, Format:='', Connection:='',
SQLStatement:='SELECT * FROM "Feuil1$"', SQLStatement1 :='');    
Showmessage('5');     oDoc.MailMerge.Execute;     Showmessage('6');  
 oApp.Visible := True;     Showmessage('7');  end;   end.            
                                 
Tomorrow, I'm going to take my first code and insert your information
with excel.
The second problem that I didn't mention with access is that it is not
installed on workstations and the IT department does not want to
install it.
To install the access runtime, I need an IT specialist to insert their
login. We can't install anything without them.
Thanks
Fanch

----------------------------------------------------------------------
folkeu08 · 2024-09-25 19:17

Thanks rvk
It's good for me now.
Fanch