(C) 2009 - 2014 by Mourad Louha · All rights reserved

Someone asked in the German Microsoft Answers Forum, if it is possible to get access to an Excel instance and then execute code from this instance. This gave me the idea to enumerate all open Excel instances by using the windows API and then try to access them with VBA.

The article describes how to create VBA Application Objects using the Windows API. Please note, that this article is a little bit shortened translated version of the two articles I wrote in the German section of this blog. The descriptions of the code in this article require that you are familiar with VBA and the use of controls like TreeViews, ListViews and Windows API functions. A sample application can be downloaded at the end of this post. The VBA code is unprotected.

Windows Enumerator

A certainly well-known way to retrieve an object reference in VBA to another Excel Application Instance is the use of the “GetObject()” method. For example, by using the statement Set xlApp = GetObject(, “Excel.Application”), you can get a reference to the Excel instance, which was firstly opened. However, it is not possible to retrieve a reference to more Excel instances, if you do not know the name of an opened workbook in these instances.

Read more

Creating a rotating image gallery in Excel VBA

Categories: Addins and Extra Tools, Development, Excel, Office, Tools, VBA
Comments Off on Creating a rotating image gallery in Excel VBA

Some days ago, someone posted in my favourite german office forum a question how to create an image gallery with rotating images in Excel VBA. The images may rotate in a circular orbit when the user clicks on an image. “Image 1” should take the position of “Image 2”, “Image 2” the position of “Image 3” and so on. Following image shows a screenshot of the sample Excel file which you can download at the end of this article and includes also the VBA Code.

Excel VBA Animator

Read more

My partner – www.designimsinn.de (German) – and me have recently redesigned and implemented an Adobe Flash based CD for one of our customers. This multilanguage CD (German, English and Russian) contains many external documents to the CD interface and stored in several subfolders on the CD. However, the user should be able to access and open these documents directly from the CD interface application by clicking on specific buttons or by clicking on listbox items.

During the project I had the idea to develop a small Excel tool that simplifies the management and generation of document keys, INI files and XML files which may be used for accessing the documents from Flash. This article describes the framework conditions and the tool. Ok, this application is specially designed for our project. However this Excel Tool can be downloaded at the end of the article and the VBA project is not protected. Maybe someone can use the tool with a few modifications for his own projects, or perhaps the code may be useful for study purposes.

UFT-8 XML Files in VBA

First, a few explanations about framework conditions. In Adobe Flash, you can use the built in component “List” and use XML files for the data source of these lists. You can also call an external application by using the Action Script “fscommand()” function.

However, there is a little disadvantage when using the “fscommand()” function. You can only execute or open files from the folder “fscommand”, which must be located just below the calling Flash application. This seems not to be the best solution if you have to manage a lot of documents.

Fortunately, an Adobe Flash Professional developer has created a tool called “UniLauncher”, which allows to specify an absolute or relative path to a document within an INI file and so store the document in an own separate folder structure below the CD root folder. Unfortunately, UniLauncher can not resolve the second disadvantage of the “fscommand()” function; which can not pass parameters to the called executable file. This means, if we wish to use UniLauncher, that we have to create a copy of unilauncher.exe and an INI file in the “fscommand” folder for each document, we would like to open it from the CD interface application. As you can surely imagine, this may be quite time consuming, especially if you have to create hundrets of files.

UFT-8 XML Files in VBA

UFT-8 XML Files in VBA

This was the main reason for me to implement the Excel VBA Tool. The VBA application reads all files associated to the CD project (the filetypes which should be considered can be defined by the user), creates copies from the “unilauncher.exe” file and creates the associated INI files. And, as I’m using the list controls in my Flash application, the Excel application also creates the needed XML files in UFT-8 format. This is important, as we are also using russian texts for some descriptions.

Let’s have a look to one of our XML files. You can find a root element and below a variable count of child elements. Each child “Data” defines the attributes “Key”, “Title”, “Path” and “File”. The title contains the texts, which can be seen in the Flash listboxes.

UFT-8 XML Files in VBA

Just for your information, the following screenshot shows an excerpt of the code in the Adobe Flash file. The red bordered areas are the most interesting parts of the code. The XML data is read to the list and when clicking on a list item, the “fscommand()” fucnction is called.

UFT-8 XML Files in VBA

As you surely know, XML files and INI files are in the finaly analysis only text files. For the INI files, it was not necessary to store them in the UTF-8 format. So, the following code is sufficient to create these files.

As mentionned before, the XML files should be stored in the UTF-8 format. You can find several solutions on the net how to do this with VBA, for example by using the Scripting Runtime. However, in my opinion, the following solution is more elegant:

Here, we just instanciate an ADODB.Stream object and then we can create and write the file in a very easy way. You can find a link to the source website at the end of this article.

A tool which can not read elements which have already been setted up or created would not really make sense. So, I implemented a function for reading the XML files from the disk and extract the attribute values. This is also relatively simple, if you use the MSXML Libraries. They are providing all necessary functions for accessing XML files and parsing the document structure. You can acces the MSXML functions by adding a reference to the library in your VBA project. The following excerpt of my code shows how the found XML files are scanned in a loop. The code also scans each child of the “Root” node and stores the values in an Excel sheet.

The last thing I had to do, was to parse all folders, subfolders and files from the root folder of my project. I used some Windows API functions for performing this task. You can find the code in the MLP_Api module in the function “mlfpRead(…)”. As the code is a little bit longer, I do not include an excerpt here. The code also stores the structure in the Excel sheet.

Finally, for letting the user choose the root folder in a comfortable way, I included some code for calling the “BrowseForFolder” function. The function also stores the last location choosed by the user and uses callbacks for performing this task.

Last but not least, the link for downloading the tool. Please note, that I can not give any warranty of any kind. And I will not support this tool. You may use this tool at your own risk. However, in the case you have some suggestions or you would give me some feedback, don’t hesitate to contact me. It would be great, if you are using the tool or a modified version in your project, to place a link to my site.

» The Excel VBA Tool

And finally the link to the UniLauncher Tool, which you can find on a well known German Flash Forum. The included INI files contains an explanation in English. And the link to the code I found for creating UTF-8 files in VBA.

This article has also been published on my other blog www.software.maninweb.de
and on the German Version of the Excel Ticker blog.

Some of you have probably heard of our Excel Soccer World Cup 2010 Planner for the soccer world cup this year in South Africa; a conjoint project of my friends and me. I had recently announced an additional Excel VBA tool for the planner, which will, for example, be able to import and export predictions from the prediction game or send queries to a web server.

Now I had the idea to involve all interested people in the development of this tool by writing articles, how choosen features are implemented in this tool. I will also present parts of the code and at the the end of this series of articles I will write a post resuming the other posts.

This article starts with presenting a solution how to write custom tooltips using VBA. The following screenshot shows a sample of these tooltips from the options dialog in Word 2010. When you move the mouse cursor over the icons containing a small “i”, a text explaining the function of the setting appears.

VBA Tooltips

Read more

If you are using SkyDrive for storing your Office documents, you have probably already discovered the features for sharing your files with other people. And perhaps you have also read on some blogs how to embed an Excel file to your website. Recently, the Excel Team has created the possibility to interact with an embedded Excel file by using JavaScript.

In this blog post, I will describe how to embed and interact with an Excel file from SkyDrive. For this, I uploaded a web app compatible version of my workbook for my Excel formula translations and then I implemented some JavaScript Code to change values in the embedded file.

The workbook includes five worksheets: “Info” just includes some info’s about me, “Formulas” lists the formulas in the different languages, “Compare” can be used for comparing the formulas in two languages, “Languages” includes the list of available languages and “Resources” allows to select between the three languages German, English and French for the interface of the workbook. My goal was now to allow to the user to select the two languages for comparing the formulas and to switch from one interface language to another.

Learn and explore

Microsoft has recently launched the website “Excel Mashup”, where in addition to learning materials and tutorials you will also find some very interesting code samples. The site also lists the most important links to the MSDN documentation about the JavaScript object model, methods and properties for the Excel Services.

Read more

The Excel Formula Translator is now also available in a Greek version and extends the offer of the online tool and the website to 6 languages. The Online Tool recognizes all native Excel functions since version 2010 and translates these functions to all localized names offered by Microsoft.

Excel Formula Translator Greek

Many thanks to Petros Chatzipantazis and to Lydia Anyfantaki from the company ILYDA for supporting me in the translation to Greek. Petros is the managing director of ILYDA (UK) Ltd, the company behind the Ribbon Commander framework and a member of the practitioners board at the Council for Ubiquitous Intelligence (CUbInt), Emory Goizueta Business School. He publishes regularly free Excel tutorials and VBA Add-ins at Spreadsheet1.com. Lydia is an Excel enthusiast and a young entrepreneur, co-founder of the software development start-up company FLEXWARE Innovation Systems.