Hello,
I have a very large XML files produced daily and they sometimes have 10 or so instances of an invalid character, it's like a musical symbol, i can't find the character code for it.
I am writing a macro to import these xml files, delete most of the unwated columns, filter by certain criteria, perform calculations on the filtered rows and then average the results of these calculations in order to obtain statistics from the massive datasets.
I have had (Limited) success with most of the steps but I have still not come up with a way of sanitizing the XML sheets before importing them into excel. For the xml files that do not contain the invalid character the file imports without a hitch. As I mentioned I can't find the character code for the offending symbol so I can't do a find and replace on it, besides this would make the operation a 2 step process rather than an elegant one button macro
Can anyone lend any suggestion on how to get around the problem of being unable to import the XML files that do contain the invalid character? My programming experience is very limited and I make most of my macros by recording basic examples and seeing how they work or tweaking other peoples brilliant solutions to suit my own needs.
Thanks in advance for all your help.