Using VBA to construct XML and call an application

[matpj]

Hi all,

Is it possible to have VBA construct an XML file with data on a spreadsheet, and also to call an application which will run it?

The application is called XML Open Gateway (XOG. It comes with Clarity - if anyone has heard of that).
I need to query a database, manipulated data, construct an XML file, save it in a location, then run the XOG app (which is done via a dos command line)

If anyone can tell me how feasible it is to do things like that, I would appreciate it.

regards,
Matt

[[email protected]]

Hi matpj:
The short answer is yes it can be done.

The long answer is that the way you described the job you want, it is complex and takes an expert, so you will need to learn a lot.
I propose some short cuts:

First let me mention that CA Clarity has a web portlet to paste xml in. It is a lot easier than getting all the files needed for using the command line set up. This is the easiest place to test a good xml xog file. Next, XMLSpear is a free tool that understands xog and can make this easier, including converting csv files to xml Xog files. The author of XMLSpear did a good job of documentation, so it is an easy tool to learn. Try it out when you get a chance. Google "XMLSpear" to find it.

Your question involved using the command line: In order to make xogging by command line easier, write a batch file that calls xog with all the parameters and a standard xog name like My_Xog_Input.xml. This will make automation easier, as all you need to do is create a file by that name and call the batch file. You won't have to pass parameters.

Your biggest task is in converting the Excel to xml. XOG is the XML Open Gateway standard. It uses, as the name implies, standard xml.

Aside from saving the file in csv format and using XMLSpear to convert it to xml, there are also a couple of csv to xml tools available, including some that are online. OR, you could use any wordprocessor that has mail-merge, and set up a template and merge the csv into it using mail-merge. OR you can use an Excel xlsx template to do the conversion to an XMl Data file (NOT an xml spreadsheet.) I use Excel 2010. I think the capability goes all the way back to 2003.

Creation of an xlsx template for converting Excel to xml:
From your description you already have a good xml file that you have used for a successful manual xog.
Use your successful xog file and open it in Excel as an xml table. Go to Developer and click Source. Reselect the same xml file as the source if it doesn't open automatically. Map the columns by selecting each column header and then selecting the appropriate element attribute in the xml and double clicking. (As this is a perfect match you can select the first column heading and then double click the main element name, and each of the attributes will go to each column.)
Now save the sheet as an xlsx xog template. (Excel_to_xog_template.xlsx)

To convert, copy the data into this sheet and save as xml data, NOT as xml spreadsheet. (Name it something like Converted-mm-dd-yyyy.xml) Now open the file and your good Xog xml in your editor (I like notepad++, if you haven't tried it, it is free.) Paste the data.
Save it with your standard xog name. Run your batch file.

Now your manual task is easier, and automation is a matter of getting your data into the template, saving it, copying it into your Xog file, and running the batch file.

Method 2
The main task of creating xml from a spreadsheet can be handled by using an xml xog template hard coded into your VBA or residing on a second spreadsheet. I like the idea of having it where it can be modified easily, so putting it on a second spreadsheet seems like a good idea. To do this, take a XOG that has been successful and replace the values with variables. Put it on a separate sheet, one line per cell.
In your VBA open a file for output with a standard name like My_Xog_Input.xml. Write it to the xml folder that you use for xogging, overwriting the last file. (If you don't want to overwite any older xog file, rename the old one first, or always rename after successfully xogging.)
Write the first (standard xog header) lines of the xml out.
Then use a for loop to go through each row in the data spreadsheet, read the value of a field, and stuff it in the corresponding variable.
Write the line out to the file. When all rows are read, and all lines written, write the ending lines of the xog xml and close the file.
Call the batch file from the vba. By using a fixed name for the xog.xml file and the batch file, you can use your previously chosen xog file name in it, so you don't have to get fancy and send a parameter.

Ok, I've broken down the steps. Writting the actual vba should be easier.
Now all you have to do is know how to find the total rows in a spreadsheet, how to read the cells, how to use a for loop, how to use the standard file open and file write commands, and how to use the call command.
Piece of cake.

-George