Internet Explorer Automation Part 2

Internet Explorer is a very nice program to automate. There are a large number of actions you can do programmatically from your own application. But when IE is already opened with a bunch of tabs, it is not a trivial task to programmatically select and activate the tab you want.

Here after, I will present all the code required to do that. It has been developed using Delphi XE3 but of course as automating IE is independent of the language, you should be able to translate my code to C#, C++ or any language supporting COM programming.

The code I present is basically in a single function with a number of small supporting functions. The main function is:

function WebBrowserSelectTabByUrl(
    const Wb           : IWebBrowser2;
    const Url          : String;
    out   HwndTopLevel : HWND) : Boolean;

You pass an existing IWebBrowser interface (see for example my previous article at http://francois-piette.blogspot.com/2013/01/internet-explorer-automation-part-1.html) and an URL. The function will select the tab having the given URL loaded, if any. It will also return a window handle that can be used to bring the actual window in the foreground or to restore it if it was minimized.

To achieve his goal, WebBrowserSelectTabByUrl is using a seldom know interface. I mean IAccessible (http://msdn.microsoft.com/en-us/library/windows/desktop/dd318466(v=vs.85).aspx). This interface is normally used by software written for the visual impaired person. This kind of software is able to discover almost every interface gadget on screen, return a description and perform a default action such as clicking on it if it is a button.

Internet Explorer is exposing a complete IAccessible interface for its entire user interface. And this is what I use to search for the tab rows displaying IE tabs, and get the URL assigned to each of the tab.

IAccessible interface and related definitions is defined in OleAcc unit which is an import from OLEACC.DLL type library. This unit also contains a lot of constants that were not included in the type library.

Beside the interface, there are a few API functions which give an IAccessible interface from a window handle or the reverse. We need two functions which are not defined in OleAcc and you’ll find the required import in the code at the end of this article. It is WindowFromAccessibleObject and AccessibleChildren.

IAccessible is just the programmatic way to interact with the underlying user interface gadgets. It is organized in an hierarchical tree. One you get an IAccessible interface for something, you can “travel” thru the tree to find what you need. Each gadget has a name. We are looking for “Tab Row” item. In Internet Explorer user interface, this represents the row usually below the address bar, where IE shows all tabs for all opened URL.

Once we get hand on the “Tab Row” gadget, we can iterate all of its descendants to find the one with the URL we are looking for. The URL is associated with each tab as a description. Actually the tab description is composed of the text IE show on the tab and the associated URL that IE shows in the address bar when the tab is selected.

Finally, when we have the IAccessible for the exact tab were looking for, we can invoke his default action programmatically. The net effect is the same as the effect a user produce when clicking on the tab.

There is still an issue: As IAccessible is made to help visually impaired users, the name of each gadget is localized. So “Tab Row” in English becomes “Onglet Ligne” in French! I have not found any way to discover the translation so I have to code a small routine querying the language from Windows configuration and use it to select the correct translation. If you use my code, you must add the language you need because I only programmed the English and French translation. See WebBrowserGetLocalizedTabRowName function at the end of this article. [The translation is probably somewhere in one resource in IE executable or DLL. Let me know if you know where it is]

The fastest way to find the first IAccessible interface we need is to travel Internet Explorer window tree. I used Microsoft Spy++ tool to see how those windows are organized. The outermost window handle is given my IWebBrowser interface in his HWND property. Then the hierarchy of window classes is “WorkerW” (or “CommandBarClass” depending on IE version), “ReBarWindow32”, “TabBandClass” and finally “DirectUIHWND”. In used the API function FindWindowEx to navigate thru the hierarchy. Yhe result is the functions WebBrowserGetDirectUIHWND.

From the DirectUIHwnd, we can get the IAccessible interface calling AccessibleObjectFromWindow. Let’s name it AccDirectUI.

The, as I said above, we have to traverse the IAccessible tree to find one with name “Tab Row” (Or the translated is you don’t use an English IE). This is FindAccessibleDescendantByName function. This is a classical tree traversal algorithm. The only complex thing is that there is a variant in the process. A cast and a call to QueryInterface are required to get hand on the IAccessible interface of the child.

Almost the same tree traversal is used from the “Tab Row” to find the right tab. Instead of checking the name, I check the description which contain the URL.

Enough story, here is the code:

function WebBrowserSelectTabByUrl(
  const Wb           : IWebBrowser2;
  const Url          : String;
  out   HwndTopLevel : HWND) : Boolean;
var
  HwndDirectUI     : HWND;
  AccDirectUI      : IAccessible;
  TabRow           : IAccessible;
  CandidateTab     : IAccessible;
  I                : Integer;
  LocalUrl         : String;
  HwndCandidateTab : HWND;
  ChildArray       : array of OleVariant;
  ChildDispatch    : IDispatch;
  ChildCount       : Integer;
  CountObtained    : Integer;
begin
  Result       := FALSE;
  HwndDirectUI := WebBrowserGetDirectUIHWND(Wb);
  AccessibleObjectFromWindow(HwndDirectUI, OBJID_WINDOW,
                             IID_IAccessible, AccDirectUI);
  if not Assigned(AccDirectUI) then
    Exit;

  TabRow := FindAccessibleDescendantByName(AccDirectUI, 
                           WebBrowserGetLocalizedTabRowName);
  TabRow.Get_accChildCount(ChildCount);
  if ChildCount <= 0 then
    Exit;
  SetLength(ChildArray, ChildCount);
  if AccessibleChildren(Pointer(TabRow), 0, ChildCount,
                        ChildArray[0], CountObtained) <> S_OK then
    Exit;
  for I := 0 to CountObtained - 1 do begin
    if VarType(ChildArray[i]) = varDispatch then begin
      ChildDispatch := TVarData(ChildArray[i]).VDispatch;
      if (ChildDispatch <> nil) and
         (ChildDispatch.QueryInterface(Ole2.TGUID(IID_IAccessible),
                   CandidateTab) = S_OK) then begin
        if not Assigned(CandidateTab) then
          continue;
        LocalUrl := WebBrowserUrlForTab(CandidateTab);
        if SameText(LocalUrl, Url) then begin
          CandidateTab.accDoDefaultAction(0);
          WindowFromAccessibleObject(CandidateTab, HwndCandidateTab);
          HwndTopLevel := FindIEFrameWnd(HwndCandidateTab);
          Result := TRUE;
          Exit;
        end;
      end;
    end;
  end;
end;


function WebBrowserUrlForTab(AccTab : IAccessible) : String;
var
  Desc : WideString;
  I    : Integer;
begin
  try
    SetLength(Desc, 1024);
    AccTab.Get_accDescription(CHILDID_SELF , Desc);
    if Desc <> '' then begin
      I := Pos(String(#13#10), String(Desc));
      if I > 1 then
        Result := Copy(Desc, I + 2, MAXINT)
      else
        Result := Desc;
      Exit;
    end;
  except
    Result := '??';
  end;
end;

// The IAccessible name for the tab row in Internet explorer is localized
// This function fetch the language code and return the appropriate value
// according to the current system default language
function WebBrowserGetLocalizedTabRowName : String;
var
  Lang : String;
begin
  Lang := GetLocaleStr(LOCALE_SYSTEM_DEFAULT, LOCALE_SISO639LANGNAME, '');
  if Lang = 'fr' then
    Result := 'Onglet Ligne'
    // YOU MUST ADD a "else if" clause for each language you want to support
  else
    Result := 'Tab Row'; // English
end;

function WebBrowserGetDirectUIHWND(Wb : IWebBrowser2): HWND;
begin
  // try IE 9 first:
  Result := FindWindowEx(Wb.HWND, 0, 'WorkerW', nil);
  if Result = 0 then begin
    // IE8 and IE7
    Result := FindWindowEx(Wb.HWND, 0, 'CommandBarClass', nil);
  end;
  Result := FindWindowEx(Result, 0, 'ReBarWindow32', nil);
  Result := FindWindowEx(Result, 0, 'TabBandClass', nil);
  Result := FindWindowEx(Result, 0, 'DirectUIHWND', nil);
end;


// Recursively trave the tree of descendant IAccessible interface object
// to search for the one having a given name.
function FindAccessibleDescendantByName(
  const AParent : IAccessible;
  const AName   : String) : IAccessible;
var
  ChildArray    : array of OleVariant;
  Child         : IAccessible;
  ChildName     : WideString;
  ChildDispatch : IDispatch;
  ChildCount    : Integer;
  CountObtained : Integer;
  I             : Integer;
begin
  Result := nil;
  Aparent.Get_accChildCount(ChildCount);
  if ChildCount <= 0 then
    Exit;
  SetLength(ChildArray, ChildCount);
  if AccessibleChildren(Pointer(AParent), 0, ChildCount,
                        ChildArray[0], CountObtained) <> S_OK then
    Exit;
  for I := 0 to CountObtained - 1 do begin
    if VarType(ChildArray[i]) = varDispatch then begin
      ChildDispatch := TVarData(ChildArray[i]).VDispatch;
      if (ChildDispatch <> nil) and
         (ChildDispatch.QueryInterface(Ole2.TGUID(IID_IAccessible),
                                       Child) = S_OK) then begin
        if not Assigned(Child) then
          continue;
        Child.Get_accName(0, ChildName);
        if SameText(AName , ChildName) then begin
          Result := Child;
          Exit;
        end;
        Result := FindAccessibleDescendantByName(Child, AName);
        if Assigned(Result) then
          Exit;
      end;
    end;
  end;
end;

// Given a HWND for a window deep in the hierarchy of windows, go back to
// the top level window which has the class name 'IEFrame'.
function FindIEFrameWnd(Hndl : HWND) : HWND;
var
    H     : HWND;
begin
    H := Hndl;
    while TRUE do begin
        if SameText(GetClassName(H), 'IEFrame') then begin
            Result := H;
            Exit;
        end;
        H := GetParent(H);
    end;
end;

function WindowFromAccessibleObject(
             pAcc      : IACCESSIBLE;
             var phwnd : HWND) : HRESULT; stdcall;
             external 'oleacc.dll';

function AccessibleChildren(
             paccContainer     : Pointer;
             iChildStart       : LongInt;
             cChildren         : LongInt;
             out rgvarChildren : OleVariant;
             out pcObtained    : LongInt) : HRESULT; stdcall;
             external 'oleacc.dll';

The first part of this article is at:
   http://francois-piette.blogspot.be/2013/01/internet-explorer-automation-part-1.html

This article is at:
   http://francois-piette.blogspot.be/2013/01/internet-explorer-automation-part-2.html

Follow me on Twitter

Internet Explorer Automation Part 1

Internet Explorer can be automated just like Word or Excel. Most automation is done using IWebBrowser2 interface. Getting hand on a IWebBrowser interface is easy. It is enough to call CreateComObject, passing the Internet Explorer ID. This will create a new instance of Internet Explorer:

FWebBrowser := CreateComObject(CLASS_InternetExplorer) as IWebBrowser2;

Once the instance is created (A new IE window will open), we can call for example the Navigate method to load a page:


FWebBrowser.Navigate('http://www.overbyte.be', EmptyParam,
                     EmptyParam, EmptyParam, EmptyParam);

Sometimes, we do not need a new Internet Explorer Window but access an existing window to automate some processing on that window.

There exists several ways of finding an existing Internet Explorer window. One of the easiest is to use the Windows Explorer API. There is a bunch of interfaces to work with Windows Explorer. IShellWindows handle a collection of Explorer windows and this is exactly what we need. We will iterate thru all the windows and locate the Internet Explorer. Since there can be several IE opened windows, we will use the URL to find the one we are looking for.

Here is the code:

function GetIERunningInstanceByUrl(const Url : String): IWebBrowser2;
var
    ShWindows : IShellWindows;
    I         : Integer;
begin
    ShWindows := CoShellWindows.Create;
    for I := 0 to ShWindows.Count - 1 do begin
        Result := ShWindows.Item(I) as IWebBrowser2;
        if Assigned(Result) then begin
            if SameText(GetClassName(Result.HWND), 'IEFrame') then begin
                if SameText(Url, Result.LocationURL) then
                    Exit;
            end;
        end;
    end;
    // Not found
    Result := nil;
end;

GetClassName is a simple wrapper around Windows API to make it easier to use with Delphi:

function GetClassName(Hndl : HWND) : String;
var
    L : Integer;
begin
    SetLength(Result, MAX_PATH * SizeOf(Char));
    L := WinApi.Windows.GetClassName(Hndl, PChar(Result), Length(Result));
    SetLength(Result, L);
end;

Share this article if you like it!


http://francois-piette.blogspot.com/2013/01/internet-explorer-automation-part-1.html

See aldo the second part:
    http://francois-piette.blogspot.be/2013/01/internet-explorer-automation-part-2.html

Microsoft Word or Excel calls a Delphi application

 

This tutorial shows how you can have a Microsoft Office (Word, Excel,…) call your Delphi application. For the demonstration, I will use Word. From Word, a macro will call my Delphi application which will prompt the user for some data which will be inserted in the Word document.

 

In the real world, it is likely that your Delphi application will be a large application managing enterprise data. Calling it from Word or Excel will use existing function to fetch data and return it back to Word or Excel.

 

In this tutorial, we will: 

• Create a simple automatable Delphi application
• Create a Word VBA macro invoking the automatable Delphi application to get data and insert it in the document.

 

To build this tutorial I used Delphi XE3 and Word 2010. You can apply the same features using other Delphi or Word versions. Details may vary slightly with different versions but globally it remains the same.
 

 

Automatable Delphi application

 

Let’s create an automatable Delphi application!

 

Launch Delphi and create a new “VCL Forms Application” (File / New / VCL Forms Application – Delphi).

 

 

Save the application: Do Menu / File / Save project, name the main form unit “DelphiAppMain.pas” and the project file “DelphiApp.dproj”. Compile and run just to check everything is OK.

 

To make the application automatable, we need to add an “Automation object”. Do Menu / File / Other. Select Delphi projects / ActiveX on the left and select “Automation Object” on the right.

Click OK. On the next form, fill the fields as shown in this screen capture:
 

 

 

CoClass name “DataInterface” will be used in the VBA code we will see in a moment.

Description is anything you like to describe your application.

Threading model and Instancing will instruct the Windows COM engine about how to handle request. Using “single” and “Single instance” will make your application run automatically for each request. This may not be the best choice in all cases, but for now, it is the simplest and working choice suitable for this simple application.

 

Click OK to save your changes. This will create three files:

• DelphiApp.ridl (A type library source file)
• DelphiApp_TLB.pas (The type library imported into Delphi code)
• Unit1.pas (A class to implement the interfaces declared in the type library)

In the project manager (Ctrl+Alt+F11 if it is not displayed), you see the files in our project:

 

 

In the main window, where you normally see your code, you should now see the “type library editor”. If yoy don’t see it, do Menu / View / Type Library.

Right click on IDataInterface branch on the treeview. Click on “New” and select “Method”:
 

 

Change the name to “ReadData”:

 

“ReadData” is the name of the function we will call from VBA macro. Now we need to create and describe the arguments and return value. Since we intent to ask the user some data, we will pass two arguments and have a return value:

• A string to prompt the user
• A reference to a string to return the data
• Return value will be an integer

In the type library editor, there is a tab with parameters. There is button to add or delete parameters.

 

Data type deserve a little bit of explanations. Since OLE / COM / ActiveX is independent of the language, the data types are not only limited to a subset of what Delphi can handle, but their names is somewhat different than what Delphi uses. To make a long discussion short, here we need string and integer. Strings are named “BSTR” and integers are named “int”. We have in and/or out parameters. For “out” parameters, we must use a pointer. A pointer is specified by appending a start to the type name. So “int*” correspond to Delphi ^integer (A pointer to an integer).

 

The return value, as seen from VBA code, is an “out” parameter marked as “retval”. Do not confuse this return value with “return type” which should always be HRESULT is merely describe a low level API return value and type we don’t really care here.

 

With that knowledge, fill in the type library as the screen dump below shows:

 

 

Once the screen is as shown, click on the “Refresh implementation” tool button.

 

Click on the “Save All” button in Delphi main tool bar. This will prompt you for the implementation unit name currently named “unit1”. Name it “DelphiAppComInterface.pas”. You are also prompted for the type library file. Name it “DelphiApp_TLB.pas”.

 

Click on the DelphiAppComInterface tab to have a look at the source code which has been generated for you. You should see a single class named TDataInterface with a single method named ReadAdata, taking two WideString parameters Prompt and Value. The first is “const” , the second is “var”. This correspond to the “[in]” and “[in, out]” modifiers we used in the type library editor. The return value is of type SYSINT which is an alias of “integer”.

 

Here is the code:

 

Now we have to fill the gap and write the implementation code:

 

 

To use “InputQuery”, add “Dialogs” into the uses clause. ReadData, in my mind, is made to return a kind of error code. In a real application, you would query the data from some data source which might trigger several error conditions. ReadData should map those conditions to error codes and return it. Here in this tutorial, we just return 0 if OK and 1 as a single error code saying “not OK”.

 

We can compile and run the application which will just… do nothing!

 

Technically, the automatable application is an out of process COM object as Microsoft names it. Delphi runtime has everything required to build such a beast and this is exactly what we have done so far. Well, we just instructed Delphi to generate all the code except a single line…

 

As we wrote it, the application only responds when invoked from the outside via a COM interface. As it is now, that COM interface already exists but is almost unusable unless it is registered in Windows registry so that other applications can locate it, learn which interfaces are defined and call one of the interfaces methods.

 

Registering the application

 

COM object must be registered Windows registry. We don’t need to know all the complexity involved in that registration since Delphi runtime provides a method for doing exactly that.

 

When you build an application containing an automation object, Delphi runtime silently add command line argument processing to register and unregister your application in the OLE registry keys.

 

You need administrator privilege to be able to register your application. So first open a command line prompt with administrator privilege by right clicking on the command prompt shortcut and select “Run as administrator”. If asked, confirm. Then at the command prompt type de fill path name for your application between double quotes and add “/REGSERVER”. On my system, this gives:

 

      “D:\Delphi\BlogArticle\Office\Word To Delphi\Win32\Debug\DelphiApp.exe” /REGSERVER

 

Nothing happens on screen and you get back the command prompt almost immediately. Now DelphiApp is registered and can be used from any other application capable of accessing a COM object. And this is the case for Microsoft Office applications using VBA.

 

Writing a VBA macro to call DelphiApp

 

We need to write VBA code in Word. For that purpose, we have to make the “Developer” ribbon page available: right click on the ribbon where there is nothing and click “Customize the ribbon”. On the right list, search for “Developer” and check the checkbox, then click OK. You should now see the developer ribbon page displayed. (This may be different in older Word versions. Consult Word online help).

 

On the “Developer” tab is visible, click on “Visual Basic” button (Alt+F11). This will bring the Visual Basic IDE.

In Visual Basic for Application (VBA) you must add a reference to your automation object: click on the tools menu, then references and in the dialog box, search for your automation object and check the checkbox on the right of his name. In our case, the object is “DelphiApp”.

 

“DelphiApp” comes in that list because we registered our Delphi application. If you don’t see it, you probably forgot to register it. See the first part of this article to see which step you missed.

 

Next, you must write VBA code to call your Delphi application. Let’s enter this code in a new module associated with “Normal”. “Normal” is the template which is always used. This makes your macro available in all documents. In VBA project explorer (Ctrl+R), right click on “Normal”, and select “Insert” and then “Module”. You then see “Module1” added in the “Project Explorer” and a code window where you’ll enter your VBA instructions.

 

In the code window, you can add the following VBA code:

 

 

 

Once the macro is created, you may assign it to a keyboard shortcut or to a ribbon button. Let’s see how you can do that with Word 2010:

 

Right click on the ribbon where there is nothing and select “Customize ribbon” in the popup menu. You see 3 columns. Above the middle column, in the drop down list, select “Macros”. You should now see your macro “ReadDataFromDelphi”.

 

On the right side, you see all existing tabs. Click on the tab which you would like to be just on the left of the new tab. Below the list, click on the button “New tab”. This will create both a new tab and a new group. Click on the new tab. Click on the button “Rename”. Select a new name such as “DelphiApp”. Click on the new group. Click on the rename, enter a new name such as “Delphi” and select an icon.

 

Click on your macro in the middle column. Click on the button “Add” between the two right most columns. This will add your macro to the new group. Select a name such as “ReadData” and an icon.

 

Finally, click OK! You now should see the new tab “DelphiApp” with a single group “Delphi” having a single icon “ReadData”. Activate the tab and click the icon. Your Delphi program will start and display the InputQuery dialog box we programmed in Delphi.

 

My VBA code displays the value returned by DelphiApp using MsgBox. To insert the value into the document, replace the call to MsgBox by:

 

Selection.Text = Value

 

That’ it!

Suggested reading

Automate Microsoft Office from Delphi

The full article is available at
    http://francois-piette.blogspot.be/2013/01/microsoft-word-or-excel-calls-delphi.html

Follow me on Twitter

Enabling floating form designer in Delphi XE3

Delphi XE3 has an interesting feature removed from previous versions: the floating VCL form designer. You can enable it again easily, at your own risk.

While the IDE is not running, launch the registry editor, locate the key  HKEY_CURRENT_USER\Software\Embarcadero\BDS\10.0\Form Design and set the "Embedded Designer" to FALSE.

Note: Don't do that if you use FireMonkey. It's form designer doesn't work when floating.

If you need sometimes to have the floating form designer and sometimes not, you way ask the IDE to load his options from another registry key. Use the "-r MyRegKey" in a new shortcut to BDS.EXE. The first time you launch Delphi with that option, the registry key is created with a copy of the current standard key. You can then change the options without affecting the standard registry key. You can also change packages and so on.

Automate Microsoft Office from Delphi

Microsoft Office (Word, Excel and others) are applications which can be fully automated from another application. Everything you can do by hand can also be done programmatically from another application and of course from your Delphi application.

Office API and Delphi components

Microsoft Office exposes his features thru a bunch of interfaces which are made accessible thru Windows COM API.

To ease the automation, Delphi is delivered with non-visual components which are "wrapper" around the underlying COM objects and exposes the same properties, methods and events as the COM interface.

Delphi has several sets of components for working with several Office versions. There are components for Office 2000, Office XP and Office 2010. 2010 version is available from Delphi XE2. Of course there are more Office versions, but don’t worry: Microsoft has carefully made his interfaces upward compatible. For example, if you use the Office 2000 components, you can still automate all Office versions from 2000 to the latest. The only restriction is that you cannot easily use new features if you use the old component.

Actually, it is better to use the older components which are capable of doing what you need to do!

This is because the compatibility in Microsoft office API goes upward and not downward.

Making the components available

Since there are several sets of components, you must make sure the correct version is installed.

For Office 2000 and Office XP, Embarcadero provides pre-built packages. For Office 2010, Embarcadero provides the source code but no package. Don’t worry, it is easy to create the package.

To see the installed packages, launch Delphi and go to the menu / Component / Install Packages. You see a long list of all installed design time packages with a checkbox telling that the package is available or not.

Locate “Microsoft Office 2000 Sample Automation Server Wrapper Components” (Or Office XP) and check the checkbox in front of the one you plan to use. Click OK and verify that your component palette now include a tab “Servers”.

To use Office 2010 components, first uncheck both Office 2000 and Office XP component. Then create a new package: go to menu / file / new / other. On the tree, select Delphi Projects and then on the right pane double click on the package icon.

Save the package in a convenient directory, naming it “Office 2010”. Then in the project manager, right click on Office 2010 and select “Add…”. The file open dialog is showing. Navigate to the directory where Delphi is installed, probably “C:\Program Files (x86)\Embarcadero\RAD Studio\10.0” and then navigate to “OCX\Servers\pas2010”. You’ll find all the files required to support the full Office 2010 suite. You may add all files but for the purpose of this article, only Word2010, Office2010 and VBIDE2010 are strictly required.

Once the files have been added, build and install the package: Right click on Office2010 in Project Explorer and select “Build” and then “Install”. In the process, you’ll be asked to add VCL framework. Accept. Finally, you have Office 2010 components installed and ready to be used. Strangely (Probably a small bug), then components are installed in the “OfficeXP” tab in the component palette and they all have the default icon. Not really a problem.

Using Office Components

As an example, we will create a sample application to insert a sentence at the end of a Word document. This is quick and easy!

Create a new VCL forms application, drop a TWordApplication and a TButton on the form. Then add the code below as the button’s OnClick handler.

The quickest way to locate it is to enter WordApplication in the component palette search tool.

TForm1.Button1Click(Sender: TObject);
begin
  WordApplication1.Connect;
  WordApplication1.Visible := TRUE;
  WordApplication1.Selection.EndOf(wdStory, wdMove);
  WordApplication1.Selection.Text := 'Delphi Rocks !' + #13;
  WordApplication1.Selection.EndOf(wdStory, wdMove);
  WordApplication1.Disconnect;
end;

Compile and run the application. Start Word, making both Word visible and your application. Click the button and see a line is added at the end of the Word document. Magic! You see that your Delphi application is automating Word.

To understand all the features you can do with Word automation, you must consult Microsoft documentation. Unfortunately, this documentation is written for Visual Basic. Don’t worry, whatever the language is, the object model, the functions and properties do not vary. This is only a matter of syntax. The above Delphi code is inspired from sample code provided by Microsoft.

This article is available at:
     http://francois-piette.blogspot.be/2013/01/automate-microsoft-office-from-delphi.html

Update: Delphi XE4 MS-Office components article

Follow me on Twitter
Follow me on LinkedIn
Follow me on Google+
Visit my website: http://www.overbyte.be

Using ADO to display database schema

In this article, I will show how to get the database schema and display it in a tree view using the ADO components and Delphi (This applies to other languages as well).

ADO stands for ActiveX Data Object and is a software layer that allows programmers a uniform and comprehensive way of developing applications that can access almost any datastore. ADO is actually using MDAC, Microsoft Data Access Components.

In Delphi, ADO is seen as a set of components named "dbGO". They exist in Delphi since Delphi 5.

Using ADO components, you can access database using MDAC, OLE-DB and ODBC. That is almost all well-known databases such as Oracle, Microsoft SQL Server, Access, InterBase, FireBird and many more.

Within Delphi, it is very easy to use ADO components. They are fully integrated in the VCL and all data-aware components. Delphi online documentation (http://docwiki.embarcadero.com/RADStudio/XE3/en/Working_with_ADO_components_Index) explains how to use those components.

Accessing the database schema

A database schema contain the complete description of a database: tables, fields, columns, indices and more. In most applications, you don’t need to worry about the schema because you already know what’s in your database. You probably created it for your application and you know all the tables, fields, columns you have to access to make your application do what it has to do!

But sometimes, you don’t know the database. For example if you are writing a query tool, or if you are writing a code generator, you have to discover what’s in the database. In such software you need to access the schema.

Discovering the tables

Getting the list of tables of a given database is quite easy. You need a TAdoConnection component to connect to the database and the call GetTableNames to have a string list filled with all table names, with or without system tables according to the last argument.

The code looks like this:

begin
  Tables := TStringList.Create;
  AdoConnection1.Connected := TRUE;
  ADOConnection1.GetTableNames(Tables, FALSE);
  Memo1.Lines := Tables;
  Tables.Free;
end;

The code above assumes that you have used the object inspector to setup a connection string to connect to your database. A connection string can be built and tested using the object inspector by clicking on the ellipsis at the right of the ConnectionString property.
Once you get the tables, you can get the field names using a similar call. GetFieldNames will take a table name and a string list and will fill the string list with the table’s field names. The code looks like this:

begin
  FieldNames := TStringList.Create;
  AdoConnection1.Connected := TRUE;
  ADOConnection1.GetFieldNames('Company', FieldNames);
  Memo1.Lines := FieldNames;
  Tables.Free;
end;

The above code is really simple, isn’t it? But there is a problem: you still don’t know each field data type.

To get hand on the data types and other attributes of each field, we have to use slightly more complex code:

var
  Fields : Recordset;
  FieldLen : Integer;
  Flags : Integer;
  DataType : DataTypeEnum;
begin
  Fields := ADOConnection1.ConnectionObject.OpenSchema(
                     adSchemaColumns,
                     VarArrayOf([Null, Null, ‘Company’]),
                     EmptyParam);
  while not Fields.EOF do begin
    DataType := Fields.Fields['DATA_TYPE'].Value;
    FieldLen := StrToIntDef(VarToStr(
    Fields.Fields['CHARACTER_MAXIMUM_LENGTH'].Value), 0);
    Flags := StrToIntDef(VarToStr(
    Fields.Fields['COLUMN_FLAGS'].Value), 0);
    Fields.MoveNext;
  end;
end;

This code deserves some explanations.

RecordSet is a data type defined in ADOInt unit. As its name implies, it represents a collection of records, like the result set of a query, each record is made of Fields and each fields has a value.

DataTypeEnum is actually an integer. And there is a bunch of constants identifying the data types supported by ADO. You find constants like adInteger and adChar. See ADOInt unit for all definitions.

ConnectionObject is a property of the TAdoConnection object. It is an interface of the underlying ActiveX and it represents the connection to the database. ConnectionObject interface has a method OpenSchema which will create a RecordSet containing a subset of the database schema.

The subset used here is identified by the constant adSchemaColumns which as you can guess represents the columns of a table.

The second parameter, which is Criteria, the sample code passes in an array that limits the returned information to just ‘Company’ table columns. To achieve this result, the array specifies a nonempty value for the TABLE_NAME, which is the third constraint available for adSchemaColumns. (adSchemaColumns has four available constraints: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME).

The third parameter is not used here could be a SchemaId.

The RecordSet returned by OpenSchema contains a record per field, with several columns describing the corresponding field. Fields are named “DATA_TYPE”, “COLUMN_NAME” and similar. See Microsoft documentation for further reference: http://msdn.microsoft.com/en-us/library/ee266174(v=bts.10).aspx

There is nothing special in accessing the columns except the value is a variant which could be empty. This is why I wrote lines such as:

 Flags := StrToIntDef(VarToStr(Fields.Fields['COLUMN_FLAGS'].Value), 0);

Fields.Fields['COLUMN_FLAGS'].Value returns the variant, VarToStr convert it to a string, possibly and empty string, and StrToIntDef convert the string to an integer defaulting to zero in case the string doesn’t represents an integer (for example when empty).

An example application

To better illustrate the use of all those functions, I wrote a little sample program which connects to a database and fetch the schema, displaying the result in a TTreeView component.

I kept the user interface fairly simple (See screen dump below). To avoid a long load time, I have loaded the list of tables when the user clicks on the “Load” button. I load the columns data when the user actually expand the table node, and only on the first click. This is really fast, even if the database is large.

I maintain the information related to each tree node into an object attached to each node. The code handles the node according to the attached object type.

Since handling a TTreeView is not the subject of this article, I won’t further describe the code. Interested people can download the full project from my website:  http://www.overbyte.be/arch/Using_ADO_to_display_database_schema.zip



In the next article, I plan to apply this code to a code generator, AKA “Wizard”, which will create a form according to the structure of a database table. Stay tuned!

Source code

The actual complete source code is available from my website here
The article is here.

Suggested readings

Using a custom attribute for data persistence
Writing an iterator for a container
Original method to iterate thru the bits of an integer
Adding properties to a set
Internet Component Suite (ICS)
MidWare multi-tier framework

Follow me on Twitter

Using a custom attribute for data persistence

Custom attributes is a relatively new Delphi feature and is seldom used. To help you grasp the concept, this article will apply custom attributes to ease data persistence of a class.

What is a custom attribute?

Basically, a custom attribute is a simple class. What makes it special is how it is used within a Delphi program.

Annotating Types and Type Members

Custom attributes are used to annotate types and type members. The syntax is described in Delphi online help. In the context of this article we use a single custom attribute to annotate fields and properties of any class.

As a simple example, I will create a class TConfig and annotate some of it in order to be able to save or load it from an ini file. The code is very similar if we used xml, or a database or the registry. To keep it easy and short, I will only demonstrate using an ini file.

To annotate a property or field of a class, simple add the attribute name and optional parameters between angle brackets above the field of property:

type
  TConfig = class(TComponent)
  public
    [PersistAs('Config', 'Version', '1.0')]
    Version : String;
    [PersistAs('Config', 'Description', 'No description')]
    Description : String;
    FTest : Integer;
    // No attribute => not persistent
    Count : Integer;
    [PersistAs('Config', 'Test', '0')]
    property Test : Integer read FTest write FTest;
  end;

As you can see, the custom attribute is PersistAs. We'll see in a moment how to create and handle it. PersistAs is taking 3 parameters: a section name, a key name and a default value. Those parameters will be used to read/write the field or property to the storage, here to read/write to an ini file.

What does a custom attribute?

By itself, a custom attribute just do nothing! It is just an annotation. What to do with the attribute is up to you. You have to write code which makes use of the attribute.

The code using the attribute can be in the annotated class itself but is frequently in another class. In this article, the custom attribute is handled in a separate class named TPersistHandler which delegate part of the work to the user. I have separated the code processing the custom attribue from the code doing the actual persistence so that it is easy to apply it to whatever storage is required.

Delphi compile will compile a custom attribute as data attached to the executable and linked to the data type it belongs to. In other words, a custom attribute is compiled as part of the Run Time Type Information (RTTI) attached to the data type. To find out which attribute (custom or not) is attached to a data type or a type member (field or property), the developer must call RTTI functions which are located in the RTTI and TypInfo units.

How to declare a custom attribute?

A custom attribute is a simple class. The only requirement is that this class must derive from the existing TCustomAttribute class defined in the System unit. TCustomAttribute directly derive from TObject and is just empty.

The custom attribute class can be as simple as an empty class if the attribute has no parameter at all, or can contain data fields to store one or more parameters.

In our sample, we use 3 parameters: a section name, a key name and a default value. So let's write the class:

type
  PersistAs = class(TCustomAttribute)
  public
    Section  : String;
    Key      : String;
    DftValue : String;
    constructor Create(const ASection  : String;
                       const AKey      : String;
                       const ADftValue : String = '');
  end;

Very simple isn't it?

Note that we have not named the class TPersistAs as we would normally have done. This is because the class name is the attribute name and having a T in front of it is not conforming to custom attribute naming.

The constructor has to store the parameters in the fields:

constructor PersistAs.Create(const ASection, AKey, ADftValue: String);
begin
  Section  := ASection;
  Key      := AKey;
  DftValue := ADftValue;
end;

Of course, the class could have several constructors so that the number of parameters could vary. It's up to you to decide. For our simple usage here, the 3 parameters are perfect: they are required to read/write the ini file.

Using the attribute PersistAs

We have seen above that a custom attribute doesn't do anything by itself. It is simply stored along with other RTTI data. To use it we need to write some code.

Our goal here is to use the custom attribute to make some of the data within a class (Any class! Here applied to TConfig) persistent in an ini file or other similar storage such as the registry, and xml file or even a database.

To let you reuse as much as possible my code and still be able to select the proper storage you need, I have delegated the actual read/write outside of the handler class by the way of two procedures.

  TPersistHandler = class
  protected
    class function  GetValue(var AValue : TValue) : String;
    class function  GetPersistAs(Obj : TRttiObject) : PersistAs;
    class procedure SetValue(var AValue : TValue;
                             const AData : String);
  public
    class procedure Process(const Obj    : TObject;
                            const Writer : TPersistWriterProc); overload;
    class procedure Process(const Obj    : TObject;
                            const Reader : TPersistReaderProc); overload;
  end;

The interesting part is the overloaded procedure Process. There is one version to write data and another to read data.

In the demo application, they are used like this:

procedure TAttributesDemoForm.WriteButtonClick(Sender: TObject);
begin
  TPersistHandler.Process(FConfig, AttrWriter);
end;

procedure TAttributesDemoForm.ReadButtonClick(Sender: TObject);
begin
  TPersistHandler.Process(FConfig, AttrReader);
end;

FConfig is an instance of TConfig shown above; AttrReader and AttrWriter are two procedures we will see in details in a moment.

AttrReader and AttrWriter are the key to the code reuse. You - as a user of my code - have to write those procedures in the context of your own application to store the data where you need it and how you need it. The complex work of applying the custom attributes to any class is completely abstracted in TPersistHandler. You can reuse it without any change whatever class the custom attribute is used into.

AttrReader and AttrWriter must be declared to fit the declarations:

  TPersistState = (pstBeginUpdate,
                   pstReadField,  pstReadProp,
                   pstWriteField, pstWriteProp,
                   pstEndUpdate);

  TPersistWriterProc = procedure (const Obj       : TObject;
                                  const State     : TPersistState;
                                  const ASection  : String;
                                  const AKey      : String;
                                  const AValue    : String) of object;

  TPersistReaderProc = procedure (const Obj       : TObject;
                                  const State     : TPersistState;
                                  const ASection  : String;
                                  const AKey      : String;
                                  const ADftValue : String;
                                  out   AValue    : String) of object;

AttrReader and AttrWriter will be called several times in a loop by the Process procedure. It will always be call one time before and one time after the loop, and as much time as there are properties or fields to read/write.

The argument State will help you do the storage work. The first call will have state pstBeginUpdate, the last call will have pstEndUpdate and the call to read/write actual fields/properties will have the other values of the enumeration.

The argument Obj is the object from which data is extracted, the one passed to the Process procedure as first argument.

The other arguments are self-explanatory, they correspond the custom attribute parameters.

Having those information in mind, we can now look at the actual implementation:

procedure TAttributesDemoForm.AttrWriter(
  const Obj      : TObject;
  const State    : TPersistState;
  const ASection : String;
  const AKey     : String;
  const AValue   : String);
begin
  case State of
  pstBeginUpdate : FIniFile := TIniFile.Create(FIniFileName);
  pstWriteField,
  pstWriteProp   : FIniFile.WriteString(ASection, AKey, AValue);
  pstEndUpdate   : FreeAndNil(FIniFile);
  end;
end;

procedure TAttributesDemoForm.AttrReader(
  const Obj       : TObject;
  const State     : TPersistState;
  const ASection  : String;
  const AKey      : String;
  const ADftValue : String;
  out   AValue    : String);
begin
  case State of
  pstBeginUpdate : FIniFile := TIniFile.Create(FIniFileName);
  pstReadField,
  pstReadProp    : AValue := FIniFile.ReadString(ASection, AKey, ADftValue);
  pstEndUpdate   : FreeAndNil(FIniFile);
  end;
end;

FIniFile and FIniFileName are declared in the class TAttributesDemoForm.

The code is really simple. We make use of the State argument to know what to do: open the ini file, read or write data or close the ini file.

Obviously, AttrWrite receive the data value to write as a const parameter while AttrReader receive it as an out parameter.

If you want to store data into an xml file or the registry or a database, the code is very similar: whatever the storage is, you can easily start/open, read/write and stop/close the resource used for actual storage.

Now let's see the magic part: How does TPersistHandler process the custom attribute used to annotate an arbitrary class. The magic is behind the Process procedure. This is an overloaded procedure to ease use: we use the same procedure name for reading or writing data. The compiler is able to call the correct one because the second argument is different.

To start, let's see the data write part:

class procedure TPersistHandler.Process(
  const Obj    : TObject;
  const Writer : TPersistWriterProc);
var
  RttiCtx    : TRttiContext;
  RttiType   : TRttiType;
  RttiField  : TRttiField;
  RttiProp   : TRttiProperty;
  AttrValue  : TValue;
  Attr       : PersistAs;
  AttrString : String;
begin
  if (not Assigned(Obj)) or (not Assigned(Writer)) then
    Exit;

  Writer(Obj, pstBeginUpdate, '', '', '');
  try
    RttiCtx := TRttiContext.Create;
    try
      RttiType := RttiCtx.GetType(Obj.ClassInfo);
      for RttiProp in RttiType.GetProperties do begin
        Attr := GetPersistAs(RttiProp);
        if Assigned(Attr) then begin
          AttrValue  := RttiProp.GetValue(Obj);
          AttrString := GetValue(AttrValue);
          Writer(Obj, pstWriteProp,
                 Attr.Section, Attr.Key, AttrString);
        end;
      end;
      for RttiField in RttiType.GetFields do begin
        Attr := GetPersistAs(RttiField);
        if Assigned(Attr) then begin
          AttrValue  := RttiField.GetValue(Obj);
          AttrString := GetValue(AttrValue);
          Writer(Obj, pstWriteField,
                 Attr.Section, Attr.Key, AttrString);
        end;
      end;
    finally
      RttiCtx.Free;
    end;
  finally
    Writer(Obj, pstEndUpdate, '', '', '');
  end;
end;

The first thing the code is doing is to check the two arguments. If either is not assigned, then nothing is done. We could have raised an exception instead. It's just a matter of taste.

Then there are two try/finally constructs. The first make sure Write is always called before and after the actual data write; the second is to make sure the RTTI context is properly freed.

Inside the two try/finally constructs, we find two almost identical loops. The first loop handles the properties while the second handle the fields.

Inside the loops, we fetch the custom attribute using the helper function GetPersistAs. If we found it, this mean the member (property or field) has been annotated with PersistAs custom attribute. We get the value and call Writer (This is actually similar to an event) to write the data wherever it has to be written.

The read data part is very similar:

class procedure TPersistHandler.Process(
  const Obj    : TObject;
  const Reader : TPersistReaderProc);
var
  RttiCtx    : TRttiContext;
  RttiType   : TRttiType;
  RttiField  : TRttiField;
  RttiProp   : TRttiProperty;
  AttrValue  : TValue;
  Attr       : PersistAs;
  AttrString : String;
begin
  if (not Assigned(Obj)) or (not Assigned(Reader)) then
    Exit;

  Reader(Obj, pstBeginUpdate, '', '', '', AttrString);
  try
    RttiCtx := TRttiContext.Create;
      try
        RttiType := RttiCtx.GetType(Obj.ClassInfo);
        for RttiProp in RttiType.GetProperties do begin
          Attr := GetPersistAs(RttiProp);
          if Assigned(Attr) then begin
            AttrValue := RttiProp.GetValue(Obj);
            Reader(Obj, pstReadProp,
                   Attr.Section, Attr.Key, Attr.DftValue, AttrString);
            SetValue(AttrValue, AttrString);
            RttiProp.SetValue(Obj, AttrValue);
          end;
        end;
        for RttiField in RttiType.GetFields do begin
          Attr := GetPersistAs(RttiField);
          if Assigned(Attr) then begin
            AttrValue := RttiField.GetValue(Obj);
            Reader(Obj, pstReadField,
                   Attr.Section, Attr.Key, Attr.DftValue, AttrString);
            SetValue(AttrValue, AttrString);
            RttiField.SetValue(Obj, AttrValue);
          end;
        end;
      finally
        RttiCtx.Free;
      end;
    finally
      Reader(Obj, pstEndUpdate, '', '', '', AttrString);
    end;
end;

 

I won't describe it in details because it is merely the same as the writer except of course values are read instead of written. Reader takes one more parameter: the default value defined in the custom attribute. it is intended to be used by the reader code in case no data can be found in the storage.

More interesting, let's have a look at GetPersistAs function:

class function TPersistHandler.GetPersistAs(Obj: TRttiObject): PersistAs;
var
  Attr : TCustomAttribute;
begin
  Result := nil;
  for Attr in Obj.GetAttributes do begin
    if Attr is PersistAs then begin
      Result := PersistAs(Attr);
      break;
    end;
  end;
end;

GetPersistAs receive an RTTI object as parameter. This object is either describing a field or property in our application. A for..in construct is used to iterate thru all attributes and find the PersistAs attribute. Remember each attribute is a simple class so we can use the is operator to check the kind of attribute we are looking at. Once we got it, we return it. If we don't find one, we return nil.

Finally we have two more helper functions to get or set the data value:

class function TPersistHandler.GetValue(var AValue: TValue): String;
begin
  if AValue.Kind in [tkWChar, tkLString, tkWString, tkString,
                     tkChar,  tkUString, tkInteger, tkInt64, tkFloat,
                     tkEnumeration, tkSet] then
    Result := AValue.ToString
  else
    raise EPersistAs.Create(GetEnumName(TypeInfo(TTypeKind),
                                        Ord(AValue.Kind)) +
                            ': Type not Supported');
end;

class procedure TPersistHandler.SetValue(
  var   AValue : TValue;
  const AData  : String);
var
  NValue : Integer;
begin
  case AValue.Kind of
  tkWChar, tkLString, tkWString, tkString, tkChar, tkUString :
    AValue := AData;
  tkInteger, tkInt64 :
    AValue := StrToInt(AData);
  tkFloat :
    AValue := StrToFloat(AData);
  tkEnumeration :
    AValue := TValue.FromOrdinal(AValue.TypeInfo,
                                 GetEnumValue(AValue.TypeInfo, AData));
  tkSet :
    begin
      NValue := StringToSet(AValue.TypeInfo, AData);
      TValue.Make(@NValue, AValue.TypeInfo, AValue);
    end;
  else
    raise EPersistAs.Create(GetEnumName(TypeInfo(TTypeKind),
                                        Ord(AValue.Kind)) +
                            ': Type not Supported');
  end;
end;

Those functions receive a value descriptor (TValue data type) which is used by the RTTI system to describe a value. We use the Kind member to know what the underlying data type is and take appropriate action to get/set the value. I have not mentioned it, but everything is handled as string in TPersistHandler so we have to convert to/from the actual underlying data type here.

Note that I have not handled every possible data type and just raise a custom exception if the custom attribute is applied to an unsupported data type. It is worth noting I make use of GetEnumName to build a clear exception message showing the unsupported data type by name.

Source code

The actual complete source code is available from my website here
The article is here.
Find the Internet Component Suite (ICS) here.

Suggested readings

    Writing an iterator for a container
    Original method to iterate thru the bits of an integer
    Adding properties to a set
    Internet Component Suite (ICS)
    MidWare multi-tier framework

Follow me on Twitter