This blog builds on the concepts from the last OLE blog, if you haven’t read it you can find it here. This time we are going to be controlling Microsoft Office through OLE. If you do not have Office installed, this script will not function properly. Fear not, though, as the concepts in the blog can be used for other applications. For example, Open Office, which is freely available, offers OLE automation as well.

Before I dig into the script itself we should take a look at the Legato functions used in it. Like the last blog we will be using the OLEGetObject function to start the process. Unlike the last blog, though, we will not be using a string predefined by Legato but rather an OLE Object string. Behind the scenes this function uses the CLSIDFromString function. This means if you are using an actual CLSID string it must have the curly braces. For example, the CLSID of Internet Explorer is “{0002DF01-0000-0000-C000-000000000046}”. You can also use standard names from Microsoft. In our example we are going to be using “Word.Application” to get an instance of a Word Application object.

Now that we have an OLE object, we need to interact with it. For generic OLE Objects there are several functions we can use:

var = OLERunMethod ( handle hOLE, string method, ... );

var = OLEGetProperty ( handle hOLE, string property, ... );

int = OLEPutProperty ( handle hOLE, string property, ... );

These functions all use the Dispatch method of the OLE COM object to run a method, get a property, or put a property. Think of these functions as a way to interact with an object like you would in any object oriented language. You can get and put the properties of the object and evoke the methods of the object. This does mean you need to know the names of these properties and methods (which are available in the SDK for the OLE object) in order to interact with them. The OLERunMethod and OLEGetProperty functions return different data types based on the property retrieved or function that is being invoked. Because Legato has type checking, it does mean that you will get a runtime error if you try to store a result in an invalid data type. Generally, the result from these functions will be a string, wstring, integer, float or handle. When a handle is returned, it is a handle to another OLE object and can be used with these OLE functions.

Also please note that because these functions all use the Dispatch method of the OLE COM object, the OLEGetProperty function takes many parameters but they are not generally used. Likewise the OLEPutProperty function will generally only have one additional parameter, which is the value. 

Now that we’ve discussed these new Legato OLE functions, I’ve taken the script from last blog and redone it to use only the generic OLE functions instead:

string get_report_div(string html);

void main() {
    handle hIE, hDoc, hBody, hHtml;
    handle hMap;
    wstring wres;
    string page;
    string res;

    page = "https://www.sec.gov/cgi-bin/viewer?action=view&cik=1518520&accession_number=0001213900-18-004605&xbrl_type=v#";

    // Get Page using HTTPGetFile
    res = HTTPGetString(page);
    res = get_report_div(res);
    AddMessage("Direct Download: %d bytes, %s", GetStringLength(res), TrailStringAfter(res, 64));
    
    // Get Page Using IE
    hIE = OLEGetObject("Internet Explorer");
    OLERunMethod(hIE, "Navigate", page);
    while (OLEGetProperty(hIE, "Busy")) {
      Sleep(5000);
      }
    hDoc = OLEGetProperty(hIE, "Document");
    hBody = OLEGetProperty(hDoc, "body");
    hHtml = OLEGetProperty(hBody, "parentElement");
    wres = OLEGetProperty(hHtml, "outerHTML");
    wres = "\377\376" + wres
    StringToFile(wres, AddPaths(GetTempFileFolder(), "ldc_89_test.htm"));
    hMap = OpenMappedTextFile(AddPaths(GetTempFileFolder(), "ldc_89_test.htm"));
    res = MappedTextToString(hMap);
    res = get_report_div(res);
    AddMessage("Internet Explorer: %d bytes, %s", GetStringLength(res), TrailStringAfter(res, 64));
    }


string get_report_div(string html) {
    handle hSGML;
    string res;
    string tag;
    
    hSGML = SGMLCreate();
    SGMLSetString(hSGML, html);
    tag = SGMLNextElement(hSGML);
    while (tag != "") {
      if (FindInString(tag, "<body") == 0) {
        break;
        }
      tag = SGMLNextElement(hSGML);
      }

    while (tag != "") {
      if (tag == "<div id=\"reportDiv\">") {
        return SGMLFindClosingElement(hSGML, SP_FCE_CODE_AS_IS);
        }
      tag = SGMLNextElement(hSGML);
      }
    return "";
    }

The output from the script looks exactly the same:

Direct Download: 0 bytes, 
Internet Explorer: 29282 bytes, <title></title><link href="report.css" rel="stylesheet" ...

The blue highlighting shows how the script has changed in order to use the generic OLE functions. Instead of the OLEIEGet function, we use the OLERunMethod function to run the “Navigate” method. Instead of the OLEIEIsBusy function, we use the OLEGetProperty function to get the “Busy” property. These are pretty straightforward and not that different from the IE versions. However, as you can see there is a lot of new code to get the content of the Document Object Model (DOM). This is because, in the old version of the script, the OLEIERead function did all of these operations behind the scenes. In our new script, we need to get the “Document” OLE object and then use that object to get an OLE object that represents the “body” DOM object and so on. We can even use these objects to interact with the DOM, as we would in Javascript.

I also have some extra code to deal with the fact that the output is now in Unicode. Legato offers the UnicodeToAnsi and UnicodeToUTF functions to deal with this situation, but they are currently limited to 65KB. Because our data is larger than that, we write the contents to a file and use a Mapped Data object to convert the file to an ANSI string. In future versions of Legato there will be more functions that can directly use Unicode strings, but for now this is a good way to translate large Unicode strings. For smaller strings, I’d stick to theUnicodeToAnsi or UnicodeToUTF functions.

As one last note about the script, since we used the “Internet Explorer” string to create the original OLE object, Internet Explorer is automatically shutdown when our script ends. If we used the CLSID above, a copy of Internet Explorer would still be running and hidden from the user unless we invoked the “Quit” method.

As you can see, we can accomplish the same thing as the OLE Internet Explorer functions using our new generic functions. Does this all just boil down to extra code to do the same thing? The answer is potentially, but nevertheless this is a good example of the utility of generic OLE functions. Now that we have seen a basic example of generic OLE objects, let’s get into a practical use.

As stated above, this script requires Microsoft Word to be installed. If you don’t have Microsoft Word, the script will stop with an error.

    handle hWord, hDocuments, hDocument,
           hParagraphs, hParagraph,
           hRange, hFormat, hFont;
    int    rc;

    hWord = OLEGetObject("Word.Application");
    if (IsError(hWord)) {
      rc = GetLastError();
      if (rc == 0x800401f3) {
        AddMessage("Microsoft Word not detected.");
        }
      else {
        AddMessage("Failed to get Word object (0x%08x)", rc);
        }
      return;    
      }

    // Show Word
    OLEPutProperty(hWord, "Visible", true);
    
    // Get Documents Container
    hDocuments = OLEGetProperty(hWord, "Documents");
    if (IsError(hDocuments)) {
      AddMessage("Failed to get Documents (0x%08x)", GetLastError());
      return;    
      }
    
    // Add a document
    hDocument = OLERunMethod(hDocuments, "Add");
    if (IsError(hDocument)) {
      AddMessage("Failed to create new document (0x%08x)", GetLastError());
      return;    
      }
    
    // Get Paragraphs
    hParagraphs = OLEGetProperty(hDocument, "Paragraphs");
    if (IsError(hParagraphs)) {
      AddMessage("Failed to get paragraphs (0x%08x)", GetLastError());
      return;    
      }
    
    // Add A Para
    hParagraph = OLERunMethod(hParagraphs, "Add");
    hRange = OLEGetProperty(hParagraph, "Range");
    hFormat = OLEGetProperty(hParagraph, "Format");
    hFont = OLEGetProperty(hRange, "Font");
    OLEPutProperty(hRange, "Text", "My Heading");
    OLEPutProperty(hFormat, "SpaceAfter", 12);
    OLEPutProperty(hFormat, "Alignment", 1);    // wdAlignParagraphCenter = 1
    OLEPutProperty(hFont, "Name", "Times New Roman");
    OLEPutProperty(hFont, "Size", 16);
    OLEPutProperty(hFont, "Bold", true);
    OLERunMethod(hRange, "InsertParagraphAfter");
    CloseHandle(hFormat);
    CloseHandle(hRange);
    CloseHandle(hParagraph);
    
    hParagraph = OLERunMethod(hParagraphs, "Add");
    hRange = OLEGetProperty(hParagraph, "Range");
    hFormat = OLEGetProperty(hParagraph, "Format");
    hFont = OLEGetProperty(hRange, "Font");
    OLEPutProperty(hRange, "Text", "The quick brown fox jumped over the lazy dog!");
    OLEPutProperty(hFormat, "SpaceAfter", 6);
    OLEPutProperty(hFormat, "Alignment", 0);    // wdAlignParagraphLeft = 0
    OLEPutProperty(hFont, "Name", "Times New Roman");
    OLERunMethod(hRange, "InsertParagraphAfter");
    CloseHandle(hFormat);
    CloseHandle(hRange);
    CloseHandle(hParagraph);

    CloseHandle(hParagraphs);
    CloseHandle(hDocument);
    CloseHandle(hDocuments);
    CloseHandle(hWord);

We start by declaring all the handles. Then we use the OLEGetObject function to get a Application object from the Word OLE provider. We output the appropriate errors if the call fails. We can also tell if it failed because the object string is not known. Now that we have the Word object we can start by making it visible. This helps a lot with development as you can see what is happening in Word as it happens. You can omit this step depending on what you are doing.

Our script is going to create a new document, so we need to get the Documents object from Word. To do this, we will use the OLEGetProperty function. Now that we have a handle to the Documents object, we can tell it to add a document using the OLERunMethod function. If we wanted to create a new document with a template we could pass the template name here. The method returns a handle to the newly created document. Using that handle we can now interact with the document instead of with the application.

Let’s start by adding some paragraphs. There are a number of ways to do this but the most straightforward approach is to get a handle to the document’s Paragraphs object. This object is a container of all the paragraphs in the document. Once we have that, we can add paragraphs using the “Add” method. Now that we have added a paragraph we can start to interact with it using the handle returned to us.

We also get the Format object, Range object and the Range object’s Font object. The Format object controls the paragraph format. The Range object represents a range of data within the document. In our case, this is the current paragraph, but it can be anything from a single character to the whole document. Finally, the Font object represents the current font of the data Range. Using the OLEPutProperty function, we can edit the contents and then format and font of the paragraph. It is important to add content to the paragraph before applying the styling as Word will not style empty content.

The “Text” property represents the text of the Range. We also set the “SpaceAfter” property for the space after the paragraph and the “Alignment” property for the paragraph alignment. This one is interesting since we are passing just the number 1 for center. If you notice the comment on the line, it has the define from the Word automation SDK. After we go through the script I will explain how to obtain these defines and other information. We also then set the “Name”, “Size” and “Bold” properties of the font. Then we tell the Range object to insert a paragraph after ours using the OLERunMethod function on the “InsertParagraphAfter” method. With that we have added a simple centered paragraph.

The rest of the code adds a second paragraph with a different style and different text. This is probably not the ideal way to add data to the Word document as it is very code heavy. It would be better to create a function that adds information to paragraphs, but this method is good for an example. If you run the script Word will open, add a new blank document, and then the document will update to the new contents.

Now that I’ve reviewed the code let’s take a few minutes to discuss the SDK for the these objects. Some vendors like Microsoft publish the COM object documentation for their OLE objects. Using the documentation is a great way to figure out how to automate something. For vendors that don’t publish an SDK for their objects, we can use Microsoft’s Visual Studio’s object browser to inspect COM objects. This isn’t quite as useful as documentation since all you get are the names of things, but it’s better than nothing. To do this, open Visual Studio and under the View menu there is Object Browser. You can use “...” button to edit what objects are visible in the viewer. To view something like Word, you can select the COM tab and find “Microsoft Word xx Object Library”. From here we can see all the classes in the library and their methods and properties. You can also browse for any available ActiveX control or COM object.

This example is neat but it just scratches the surface. We could create a new document from a template. Then we might use the GoTo method on the document object to go to Bookmarks and insert data. This would allow us to create a computer fillable Word document. After the data is filled in, you could copy and paste it into GoFiler or save it and run the document through GoFiler’s translate module. The possibilities are endless!

David Theis has been developing software for Windows operating systems for over fifteen years. He has a Bachelor of Sciences in Computer Science from the Rochester Institute of Technology and co-founded Novaworks in 2006. He is the Vice President of Development and is one of the primary developers of GoFiler, a financial reporting software package designed to create and file EDGAR XML, HTML, and XBRL documents to the U.S. Securities and Exchange Commission.

Additional Resources

Novaworks’ Legato Resources

Legato Script Developers LinkedIn Group

Primer: An Introduction to Legato