TatPascalScripter : Using OLE automation with Excel, Word, Outlook, ADO and XMLDOM

All sample applications that show the use of COM with TatPascalScripter start from TFormScript as the samples all have interaction with form controls which TFormScript makes directly possible to access

Using ADO

COM opens the way to bind all kinds of database data through scripting. This small sample uses a script to preload and preset a country combobox from an Access database.

log.Items.Add('Start ADO demo for TatPascalScripter');
ObjConnC := CreateOleObject('ADODB.Connection');
log.Items.Add('ADO connection created');
myDSN := 'Driver={Microsoft Access Driver (*.mdb)};DBQ=countries.mdb';

ObjConnC.Open(myDSN);
log.Items.Add('ADO connection opened');
ObjRSC := CreateOleObject('ADODB.RecordSet');
mySQL := 'SELECT * FROM COUNTRIES';
ObjRSC.ActiveConnection := ObjConnC;
ObjRSC.Open(mySQL);
i := 0;
countrycombo.Items.Clear;
while (ObjRSC.Eof = False) do
begin
  { get the field }
  fld := ObjRSC.Fields('COUNTRY'); 
  { add field value to combobox }
  countrycombo.Items.Add( fld.value);
  { count nr. of items added }
  i := i + 1;
  ObjRSC.MoveNext;
end;
log.items.Add('Added '+inttostr(i)+' records');
ObjRSC.Close;
ObjConnC.Close;
log.items.Add('ADO connection closed');
{ Pre initialize the selected country in the combo }
countrycombo.SetItemIndex(12);
ShowMessage('Script executed');

Using Word

Providing access to database form fields and Word at the same time opens various capabilities to let end-users customize export and merging of data in your application with Word documents. By having built-in scripting in the application, the application gains unprecedent new flexibility, allows updating and adding functionality by just sending new scripts to customers or let end-users adapt application logic. In this sample app, the script performs merging of database fields with a Word template :

wordapp := CreateOleObject('Word.Application');
wordapp.Visible := ShowWord.Checked;
templ := GetCurrentDir + '\MERGETEMPLATE.dot';
worddoc:=wordapp.documents.add(templ);

s := DBEdit1.GetValue;
wordrange:=worddoc.content.goto(wdGotoBookMark,unassigned,0,'NAME'); 
wordrange.insertafter(s);

s := DBEdit2.GetValue + ' ' + DBEdit3.GetValue;
wordrange:=worddoc.content.goto(wdGotoBookMark,unassigned,0,'ADDRESS'); 
wordrange.insertafter(s);

s := DBEdit4.GetValue + ' ' + DBEdit6.GetValue;
wordrange:=worddoc.content.goto(wdGotoBookMark,unassigned,0,'COUNTRY'); 
wordrange.insertafter(s);
ShowMessage('Merge done. Generated document will be saved as RESULT.DOC');
ResultFile := GetCurrentDir + '\RESULT.DOC';
worddoc.saveas(ResultFile);
worddoc.close;
wordapp.quit;

Using Outlook

The powerfull mail, scheduling and contact management in Outlook can be accessed from your applications through scripts with COM. Based on this technology, you have the flexibility to create emails from your application, schedule new tasks and appointments or just access the Contacts database to show a telephone list as is shown here :

listbox1.Items.Clear;
outlook := CreateOleObject('Outlook.Application');
namespace := outlook.GetNameSpace('MAPI');
contacts := namespace.GetDefaultFolder(10);

contactcount.caption := '# contacts in Outlook : '+inttostr(contacts.items.count);

for j := 1 to contacts.items.Count do
begin
  contact := contacts.items(j);

  if phonesel.ItemIndex = 0 then
    phone := contact.HomeTelephoneNumber;
  if phonesel.ItemIndex = 1 then
    phone := contact.BusinessTelephoneNumber;
  if phonesel.ItemIndex = 2 then
    phone := contact.MobileTelephoneNumber;

  listbox1.items.Add(contact.FirstName + ' ' + contact.LastName+ ' (Tel:' +phone+')');
end;

Using Excel

One of the most boring jobs is most likely order entry. Suppose a Delphi order entry application is being used at Company X and the application provides scriptability of the order intake form. Sales person Y suddenly decides to no longer fax a sheet with his orders but send an Excel file. Having a scriptable form, the job of the order intake clerk can suddenly be reduced to simply executing the script. The script below takes all information from the Excel file and appends it as orders into the database :

{ excel demo }
excel := createoleobject('excel.application');
excel.visible := ShowExcel.Checked;
FileName := GetCurrentDir + '\orders.xls';
workbook := excel.workbooks.open(FileName);
worksheet := workbook.ActiveSheet;

i := 1;
ordid := '*';
while (ordid <> '') do
begin
  cell := worksheet.Cells(i,1);
  ordid := cell.Text;
  if (ordid <> '') then
  begin
    Append;
    dbEdit1.SetValue(ordid);
    cell := worksheet.Cells(i,2);
    dbEdit2.SetValue(cell.Text);
    cell := worksheet.Cells(i,3);
    dbEdit3.SetValue(cell.Text);
    cell := worksheet.Cells(i,4);
    dbEdit4.SetValue(cell.Text);
    cell := worksheet.Cells(i,5);
    dbEdit5.SetValue(cell.Text);
    Post;
  end;
  i := i + 1;
end;

workbook.Close;
Excel.Quit;
Excel := unassigned;

Using XML

Suppose that in the above scenario where the huge time saving scripting solution was implemented, the sales person suddenly decides to send the orders in XML format. Again the scriptability gives the advantage to keep using the automation of the order entry job in this changing situation. Instead of getting the order information out of the Excel file, some modifications are done in the script to use XMLDOM instead and in a snap, the order entry person is saved again from the boring order entry task. Below is the modified code to use XML instead :

{ XMLDOM demo }
xmld := CreateOleObject('microsoft.xmldom');

if xmld.Load('orders.xml') then
begin
  nodel := xmld.DocumentElement.ChildNodes;

  for i := 1 to nodel.Length do
  begin
    node := nodel.Item(i-1);
    nodec := node.ChildNodes;
    Append;
    dbEdit1.SetValue(nodec.Item(0).Text);
    dbEdit2.SetValue(nodec.Item(1).Text);
    dbEdit3.SetValue(nodec.Item(2).Text);
    dbEdit4.SetValue(nodec.Item(3).Text);
    dbEdit5.SetValue(nodec.Item(4).Text);
    Post;
  end;
end;

xmld := unassigned;

These are just some samples that show how scripting can give your applications entirely new powerfull flexibility. We look forward to hear from you the creative and productive capabilities scripting will add to your application.