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

Creating UTF-8 XML files in VBA for use in Adobe Flash

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.


No comments yet.

Leave a Reply