Hello dear community!
I am completely new to VBA/Excel programming and need your help, because I can't figure out how to make it work from what I am finding via Google...
So I have a folder with several thousand .xml files. I can open each of those in excel and then export to csv.
But since I have several thousands of those, I obviously wanna automate the process...
Can someone help me how to do this (e.g. with VBA)?
Thanks so much!
parsimoneous
Try this and let me know how it works :
Please Login or Register to view this content.
Originally Posted by Logit
Hello Logit,
thank you so much for your help!
I tried it - at first it said 'compile error: variable not defined'. Google told me that this might be because of the 'Option Explicit' (even though all the objects appear to be defined afterwards?).
If I remove the 'Option Explicit' it does successfully open one of the xml files in the folder, but then stops with the error message "Runtime error '1004': Method 'SaveAs' of object '_Workbook' failed"
Do you have an idea of what might've gone wrong? When I try to save it as a csv manually, it gives me a message "some features might be lost, do you wanna continue?" before proceeding - could that cause the problem?
Btw, if that makes it easier in any way, an export to xls or xlsx would be just as good as csv. In the end I just need sth that I can load into R or Stata (both have routines to import xml, but they fail for some reason while excel is able to open the xmls).
Thank you again for your time!
Parsimoneous
Last edited by Parsimoneous; 03-22-2017 at 10:12 AM.
Download converter :
https://sourceforge.net/projects/convertxmltocsv/
Basic macro code :
https://www.mjtnet.com/blog/2009/10/...rt-xml-to-csv/
Larger macro code :
https://www.extendoffice.com/documen...xml-files.html
Here is the macro :
Please Login or Register to view this content.
Download converter :
https://sourceforge.net/projects/convertxmltocsv/
Basic macro code :
https://www.mjtnet.com/blog/2009/10/...rt-xml-to-csv/
Larger macro code :
https://www.extendoffice.com/documen...xml-files.html
Here is the macro :
Please Login or Register to view this content.
Thank so much, Logit! I'll only have access to the computer again tomorrow, but then I'll give that a shot.
One question before that (sorry, this probably is a dumb question, bur I really have no clue how to read this code so I have no idea what's going on): Are those 4 alternative ways of doing it? Or is it one way, so do I need to install the converter and then run all the three macros you listed?
Those are all different methods of conversion. UNTESTED ... I just went searching for resources to assist you.
I don't have any XML files here. So I can't test any of them. If you want to post 5 or 10 samples then I could experiment.
I see, thank you for your time, Logit! I'll try it tomorrow when I got access to my office computer - if it does not work, I'll try to find a few non-sensitive xmls to post!
Hi Logit,
the 'Here is the Macro' macro code that you posted seems to come very close - it is definetly looping through all the xmls, I can see that - it's just that there are no csv/xls/xlsx files appearing anywhere, i.e. the folder looks just the same as before. So something about the 'saving-part' does not yet seem to work... do you have an idea what to change?
EDIT:
Actually it does save one file as a .csv (it calls it Book2.csv) on the Desktop! And in the process of running it asked me to confirm that I wanted to save it as a non-macro enabled document.
ANOTHER EDIT:
It does put all the xmls together in one big excel file - which, in principle, would be great, the only issue is that it becomes too big - but actually there is a lot of repeated lines in each xml - so is there a way to only use the first like 10 rows maybe of each xml and otherwise keep it the same?
Thank you
Last edited by Parsimoneous; 03-23-2017 at 12:08 PM.
Post the file/s you are working with.
Is there some way I can send 2 of those (overall several 1000s) files without posting it online, e.g. via some disposable email address of your or so? Thank you for your patience...
Btw, the converter program that you mentioned works very well - except it only takes one xml at a time and I have a couple of thousand....
Sent you a private message.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks