Recent

Author Topic: [Solved) Word-Excel mail merge  (Read 661 times)

folkeu08

  • Full Member
  • ***
  • Posts: 111
[Solved) Word-Excel mail merge
« on: September 22, 2024, 09:28:21 pm »
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][+][-]
  1. program officAuto;
  2.  
  3. {$IFDEF FPC}
  4.   {$MODE Delphi}
  5. {$ELSE}
  6.   {$APPTYPE CONSOLE}
  7. {$ENDIF}
  8.  
  9. uses
  10.    SysUtils, Variants, ComObj;
  11.  
  12. var
  13.   Server, Connect : Variant;
  14.   oWord, oPara0, oPara1, oPara2 : Variant;
  15.  
  16.   w:widestring;
  17.  
  18.   begin
  19.     if Assigned(InitProc) then
  20.     TProcedure(InitProc);
  21.  
  22.   try
  23.     Server := CreateOleObject('Word.Application');
  24.   except
  25.     WriteLn('Unable to start Word.');
  26.     Exit;
  27.   end;
  28.  
  29.  
  30.   // oWord := Server.Documents.Add;
  31.   w:= UTF8Decode('c:\mydoc.docx');
  32.   Server.Visible := True;  
  33.   Server.Documents.Open(w);
  34.  
  35.   [color=blue]oPara0 := Server.ActiveDocument.Mailmerge.execute;  [/color]
  36.  
  37.   oPara1 := Server.ActiveDocument.Content.Paragraphs.Add;
  38.   oPara1.Range.Text := 'This is a Heading';
  39.   oPara1.Range.Font.Bold := True;
  40.   oPara1.Format.SpaceAfter := 24;
  41.   oPara1.Range.InsertParagraphAfter();
  42.  
  43.   oPara2 := Server.ActiveDocument.Content.Paragraphs.Add;
  44.   oPara2.Range.Text := 'Where will this appear if at all!';
  45.   oPara2.Range.Font.Bold := False;
  46.   oPara2.Format.SpaceAfter := 24;
  47.   oPara2.Range.InsertParagraphAfter();
  48. end.
  49.  
« Last Edit: September 25, 2024, 09:16:26 pm by folkeu08 »

egsuh

  • Hero Member
  • *****
  • Posts: 1468
Re: Word-Excel mail merge
« Reply #1 on: September 23, 2024, 04:47:13 am »
I do not know much, but is two dots (..) correct?

folkeu08

  • Full Member
  • ***
  • Posts: 111
Re: Word-Excel mail merge
« Reply #2 on: September 23, 2024, 09:58:28 pm »
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

  • Hero Member
  • *****
  • Posts: 6476
Re: Word-Excel mail merge
« Reply #3 on: September 24, 2024, 11:13:20 am »
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][+][-]
  1. 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

  • Full Member
  • ***
  • Posts: 111
Re: Word-Excel mail merge
« Reply #4 on: September 24, 2024, 05:13:46 pm »
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

  • Hero Member
  • *****
  • Posts: 6476
Re: Word-Excel mail merge
« Reply #5 on: September 24, 2024, 05:51:05 pm »
This 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][+][-]
  1. Server.ActiveDocument.MailMerge.OpenDataSource('C:\path\your_data_source.xlsx', SQLStatement := 'SELECT * FROM `Sheet1$`');
  2. oPara0 := Server.ActiveDocument.Mailmerge.execute;

Hope that helps.

folkeu08

  • Full Member
  • ***
  • Posts: 111
Re: Word-Excel mail merge
« Reply #6 on: September 24, 2024, 11:31:18 pm »
Hi rvk,

I have write this code :
Code: Pascal  [Select][+][-]
  1. unit Main_unit;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, ComObj, Variants;  //WordXP;
  9.  
  10. type
  11.  
  12.   { TForm1 }
  13.  
  14.   TForm1 = class(TForm)
  15.     Button1: TButton;
  16.     procedure VBA_vers_Lazarus_OnClick(Sender: TObject);
  17.   private
  18.  
  19.   public
  20.  
  21.   end;
  22.  
  23. var
  24.   Form1: TForm1;
  25.  
  26. implementation
  27.  
  28. {$R *.lfm}
  29.  
  30. { TForm1 }
  31.  
  32. //******************************************************************************
  33. //                                Bouton 1
  34. //******************************************************************************
  35.  
  36. procedure TForm1.VBA_vers_Lazarus_OnClick(Sender: TObject);
  37.   var
  38.     oApp: Variant;
  39.     oDoc: Variant;
  40.     oAutoText: Variant;
  41.     sourcedata :string;
  42.   begin
  43.     sourcedata := UTF8Decode(ExtractFilePath(Application.Exename) + '/pige/convention_export.xlsx');
  44.     oApp := CreateOleObject('Word.Application');
  45.     oApp.Visible := True;
  46.  
  47.     oDoc := oApp.Documents.Open(UTF8Decode(ExtractFilePath(Application.Exename) + '/pige/Convention_diagnostic.docx'));
  48.  
  49.     Showmessage('1');
  50.  
  51.     //oDoc.MailMerge.OpenDataSource(UTF8Decode(ExtractFilePath(Application.Exename) + '/pige/convention_export.xlsx')); // Spécifiez la source de données ici
  52.  
  53.     Showmessage('2');
  54.  
  55.     //oDoc.MailMerge.OpenDataSource(UTF8Decode(ExtractFilePath(Application.Exename) + '/pige/convention_export.xlsx'), '', '', '', '', '', '', '', '', '', '', 'SELECT * FROM "Feuil1$"', '', '', '');
  56.  
  57.     Showmessage('3');
  58.  
  59.     //oDoc.MailMerge.OpenDataSource(ExtractFilePath(Application.Exename) + '/pige/convention_export.xlsx', '', '', '', '', '', '', '', '', '', '', '', 'SELECT * FROM Feuil1$', '', '', '');
  60.  
  61.     Showmessage('4');
  62.  
  63.     //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 :='');
  64.  
  65.     Showmessage('5');
  66.  
  67.     oDoc.MailMerge.Execute;
  68.  
  69.     Showmessage('6');
  70.  
  71.     oApp.Visible := True;
  72.  
  73.     Showmessage('7');
  74.  
  75.  
  76. end;
  77.  
  78.  
  79.  
  80. end.                                              
  81.  

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

  • Full Member
  • ***
  • Posts: 111
Re: [Solved) Word-Excel mail merge
« Reply #7 on: September 25, 2024, 09:17:20 pm »
Thanks rvk
It's good for me now.
Fanch

 

TinyPortal © 2005-2018