XML to XLS (Excel)

ludolf

New Member
Joined
Nov 9, 2004
Messages
12
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
(y)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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

New Member
Joined
Nov 9, 2004
Messages
12
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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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

New Member
Joined
Nov 9, 2004
Messages
12

ADVERTISEMENT

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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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.)
 

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
mrchonginhk, HalfAce has replied just now. :)
 

ludolf

New Member
Joined
Nov 9, 2004
Messages
12
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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
Hi ludolf,

ludolf said:
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

ludolf said:
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.)

Code:
Shell "NotePad.exe"

Code:
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. :LOL:
 

Forum statistics

Threads
1,147,668
Messages
5,742,514
Members
423,734
Latest member
123hmMission

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top