XML to XLS (Excel)

[ludolf]

Hi,
how do i create a spreadsheet trought VBA macros, containing the data from a XML file? i´m using Excel 2003

I have a program that do this for me, but i have to do it one by one, and i´m losing several hours each day.

this would be a simple XML to XLS.

i´m so lost in this subject for several days...

thanks for the help

and sorry about my crap english.

cya

 

[Colo]

Hi ludolf, so what you'd like to do is calling the existing procedure using a loop?

Let us know what does your code do.

 

[ludolf]

yeah i wasen´t very clear...

the problem is that the program that i use to execute each of the XML files, is a stand alone program. It´s not VBA.

so i need to start from zero, i can handle the file system with VBA, but i don´t know how to process the XML to XLS in VBA... in fact it´s a simple transformation. Just separate in diferent sheets, the fields.

if you guys want to i can post the XML that i use, and the XLS that is genaretade from this stand alone program.

I really don´t have a clue about how to proceed...

Best Regards

 

[Colo]

ludolf, if you are going to do all process with Excel VBA alone, we need to know what is the stand alone application doing for generating an XML file. We may know the structure of an XML file if you will post the XML data here, but more information is necessary about the source data for generating XML. Such as ...where the data is pulled from? If the source data is placed on a data server(Windows/SQL/UNIX/LINUX...), how can you access to the server and so on.
So if it's difficult to explain about the environment, I would suggest you use existing the stand alone program only for generating a XML file.
I think you may be able to execute that program using a Shell command from VBA. Using a loop, XML files may be generated.
After XML files are generated, all you have to do is import the files to Excel from File > Open (File Type: xml) menu.

Anyway, please let us have more details.

 

[ludolf]

Colo Thanks For your time...
i´ll try to make it simple:

What i want genarated is a EXCEL file from the XMLs i recieve every day in a ZIP pack with lot´s of XMLs.

I´m trying to find a place to post the files.

i´ll post the XML, the XLS created and the program i use to genarate the files.

by the way i don´t think this programs is shellable.

thanks

 

[Colo]

I'm still not sure about the program you are using so far. It seems the program loads the XML files from a specific folder then converts the files into XLS format. Besides, it sounds possible to be executed by Shell command from Excel VBA.
So the step what I'm thinking is as follows.

1.unzip xml files
2.Select the unzipped folder from Excel VBA.
3.Execute the stand alone program from Excel VBA by Shell function.
(An argument for unzipped xml files may necessary.)

 

[mrchonginhk]

Sorry to jump into like this but seems Colo is real expert. Cole could you please help me at the following post ? No one can help so fatr... pls....


http://www.mrexcel.com/board2/viewtopic.php?t=128412

Thanks in advance...

 

[Colo]

mrchonginhk, HalfAce has replied just now.

 

[ludolf]

yeah colo it´s just like that...

maybe i´m underestimating the power of the shell feature...
i can only use simple programs like pkzip25.exe that are made in DOS and uses command prompt...

how would i use shell to control this windows based program that i use?
the process i use inside the program is:

open the XML, Click READ XML file, and click GENERATE file, and then choose the place so save the file.

i lose hours everyday, formating dates, and associating codes to the filenames. not to mention the humman errors in the process, and the headaches figuring out where is the error.

 

[Colo]

Hi ludolf,

how would i use shell to control this windows based program that i use?
the process i use inside the program is:

A Shell function can execute Widows application similarly.
For example, this code execute WindowsNotePad.
http://puremis.net/excel/cgi-bin/click.cgi?cnt=c084&url=http://puremis.net/excel/code/084.shtml

open the XML, Click READ XML file, and click GENERATE file, and then choose the place so save the file.

It depends on the speciation of the application. Generally, we can use an argument when we call an application by Shell function.
In Windows NotePad, the first code only opens an instance of NotePad application.
When you give the path of the text file to Shell function as an argument, the specified text would be opened in the NotePad Application.
Please have a look at the second code. (In this case, you need Sample.txt in the C drive.)

Shell "NotePad.exe"

Shell "NotePad.exe C:\Sample.txt"

As I wrote, the arguments depends on the application. So for now, I have no idea if the stand alone application can have arguments.
But I think it may can be done using Excel VBA alone. If possible, let me have some XML(source) files and XLS(output) files.
I'd like to try it when I have time.