MS Excel has been one of my favourite applications for a long time.
Over time I have had the opportunity to use it in many different ways:
- Accounting tasks
- Creating Invoices
- Teaching Excel to clients
- Creating macros in VBA
- Controlling Excel with VB or Applescript
In 2011, we decided to mix the AS and VB scripts we already created with the simplicity of LiveCode to create a Library capable of controlling Excel and usable by the LiveCode community. LiveCode is ideal for this task:
- with the “start using” (library) command it is easy to create a library usable by all, in their own projects
- the LiveCode “do… as” command allows to execute script in alternate languages such as AppleScript or Visual Basic. That way, such a library is usable on Macintosh and Windows
- custom properties can contain everything. Here, we are using custom properties for storing VB and AS code.
There were three steps involved in creating the library.
Step 1: Creating the Library
Our main goal was to create easy to use functions, hiding the language tricks required for developing AS or VB scripts for MS Excel, using the same parameters, and returning the same values, whatever the platform (Windows or Macintosh).
To keep the commands reliable, and despite the difference between AS and VB, we paid particular attention to ensuring each command returns an appropriate error depending on the situation.
Step 2: Interactive Documentation
Creating such a Library for use by developers requires as detailed documentation as possible. Our goal in this step was to give developers reading the documentation an immediate understanding of what they can do with the command.
The Documentation Window
We decided that a simple PDF was not enough. We opted for a LiveCode stack, giving users a way to see the required script and experiment with it in action. Several commands have the “Experiment” button available such as:
- saving a workbook in different format: text, csv, pdf, etc
- formatting range
- adding a shape
- adding a chart
Experimenting with the XCEL_Range_Values_Get / XCEL_Range_Values_Set commands
Experimenting with the XCEL_Workbook_SaveAsFile command
Step 3: Beta Tests
We always strive to provide the best quality we can, for any tool we are creating. There are so many Excel versions and the behaviour of MS Excel can be so different between Windows and Macintosh that we took the time to test our library thoroughly. As a final test, we offered it to users to test in various different environments via an open beta. Thanks again to all the Testers of the LiveCode community who helped in this task!
Representing data with the XCEL_Range’s keywords
Creating Charts
The Excel Library provides more than 170 commands for controlling:
- Workbooks
- Worksheets
- Ranges
- Columns
- Rows
- Cells
- Charts
- Shapes
- Print Setups
- Windows
- Excel Application
By combining these commands it is possible to create tools for personal needs, in-house applications or for providing services to clients.
On our website, we offer a variety of examples demonstrating the possibilities of the Excel Library:
- importing worksheet in a LiveCode datagrid.
- Consolidating data from several workbooks
- Creating an import tool with selectable columns
The library is distributed in GPL license and offered in 2 versions,Community and Commercial, for use with the Community Edition of LiveCode, or the Commercial Edition.
About the Commercial Version
For owners of an Excel Library commercial license, we provide:
- free support for one Year
- free updates for one Year
- ability to request new commands or functionalities
- commands and functions only available in the Commercial version, such as 8 commands providing RGB colors for controlling ranges borders, fonts and patterns.
Supporting Pivot Tables is planned for the coming months.
Compatibility
- LiveCode Community or Commercial version
- The Library is ready for Windows and Macintosh.
- MS Excel versions from 2001 through 2011 (Macintosh) and 2013 (Windows) are supported
You can get the Excel Library in the LiveCode Extensions store. |
