Convert xml files to xls without open them?

Hi to all,

Maybe someone helps me over here.

I have many xml files (in a format that Excel can read). Even when Excel can open those xml files, I would like to convert them from xml to xls or xlsx.

I've recorded the following macro that open an xml file and convert it to xlsx.

Please Login or Register  to view this content.

Is there a way to say to Excel that convert all xml files in folder to xlsx without open them?

This because there are 5000 xml files to convert and open and saving files will take so long I think.

Many thanks in advance.

Regards.

Can't you just change their extensions to xls?

Please Login or Register  to view this content.

Somebody knows if this is possible?

Thanks for any help.

it's possible in MS Access If all workbook has a same worksheets name.
For example, if all worksheets has name "Sheet1" I can help you.

Many thanks egonomist, the xml files contain 2 sheets, but only I need to do it in MS Excel.

Many thanks for help so far.

Hello Leith,

Thanks for your reply.

I've seen the link you provided me, even I installed the fileconverter, but
I think it wasn't needed for me, because I'm using Excel 2010.

I can open and convert (SaveAs) the xml files into xlsx or xls files, but I have a few thousand of xml files and I have to do that conversions one by one and open and saving, open and saving.

I recorded the macro I show above in my first post, but I was wondering if it is possible to do the same thing without open the xml files, only convert all the xml files in folder to xls or xlsx.

Maybe with ADO or with other VBA code.

I would like to avoid open and save, open and save, to not turn in a slow execution and to save resources, because there are thousands of files.

Many thanks for any help.

Regards.

Leith,

Yes, the xml files are all in the same folder.

How would be some example of save them without open as original binary xlsx or xlx files?

Thanks for help so far

Leith,

Thanks for your help, it work almost as need. Convert extension without open the files, the only issue is that when
I open the new files in Excel, I receive a window saying the file doesn't have the format especified by the file extension.

That problem is avoided if I do "SaveAs". I think the solution should be using "SaveCopyAs" as you said before.

The only thing I think I need is to put the files that is currently reading by Dir(), as Workbook object to do "Expression.SaveCopyAs", where Expression is a workbook object.

May you help to finish this, saying me how to set the file that is currently read with Dir () as workbook object?

Something like:

Please Login or Register  to view this content.

I know that is wrong, only I don't know how to set the expression.

Thanks again for your help.

That code works Leith, if it is possible only Save opening the files I'll have to do it in that way.

Many thanks for your kindly help Leith. Great support.

Best regards

hi, i have the same situation and found this post and therefore this forum. I do not have any experience on excel vba or macro. I used SAS ods tagset.excelXP and 5000 xml files were created, and need to convert them in xls or xlxs. they have the different names for example sample1.xml, test3.xml, and control4.xml, each one has two work sheets. i need to save as the same name but with excel suffix.

Looks like the problem are solved, but can somebody tell me where/how do i put these code and how it work? many many thanks. Please treat me as a dummy, the more detail the better. Thanks.

Originally Posted by Leith Ross

Hello cgkmal,

Well, it was worth a try. Unfortunately, the SaveCopyAs doesn't allow you specify the file format, just the file name. So, the results using SaveCopyAs would be the same. There doesn't seem to be a way around having to open the workbook to convert it. This macro will do it for you but it make take some time to do 1,000 or more workbooks.

Please Login or Register  to view this content.

GOt it . Thanks.