0

Still quiet new to VBA and no native english speaker, so hope i didn't just miss something...

I need to connect a long list of XML's together in 1 CSV. This is done by opening the XML's one by one into a seperate workbook and then writing the needed values away to a textfile. I know this is not optimal, but it was a inherited from a former colleague:

For i_path = 0 to 9
    myPath(i_path) = "C:\Base databases\NUM08072016\ReadingFolder0" & (i_path) & "\"

    Dim myFile   
    myFile = Dir(myPath(i_path) & "*.xml")

    Do While myFile <> ""
        Set WB = Workbooks.OpenXML(Filename:=myPath(i_path) & myFile)

        ----Write certain columns to textfile-----

        WB.Close False
        myFile = Dir()
    Loop
Next i_path

Now one of the columns I need has an Preset ID with all digits. Unfortunatly this preset ID has a length of 16. As you might guess, when it doesn't start with 0, it loses the 16th digit (and replaces it with a 0).

Question: Is there a sollution for this (for example forcing OpenXML to put everything in strings?). For short term an answer would be nice.

For long term I do realize the limitations of excel pushes towards a sollution in for example java.

Community
  • 1
  • 1
Bert-Jan Stroop
  • 357
  • 1
  • 10
  • 1
    You could read the XML files using MSXML and write the values directly to the text file. For some examples: http://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba or http://analystcave.com/vba-xml-working-xml-files/ – Tim Williams Feb 27 '17 at 19:51
  • Took some time to dive into (complicated XML structure) but seems to be doing the trick. Thanx – Bert-Jan Stroop Feb 28 '17 at 11:43
  • If you're dealing with XML it's worth the time to figure this stuff out. – Tim Williams Feb 28 '17 at 15:38
  • `As you might guess, when it doesn't start with 0, it loses the 16th digit (and replaces it with a 0).` why it does loose it? I didn't guess – Suncatcher Jul 13 '20 at 10:23
  • Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes. – Bert-Jan Stroop Jul 14 '20 at 12:01

0 Answers0