CODEWITHSUNDEEP
htmlvbaexcel
barker
barker

Reputation: 1055

excel truncating very large integers pulled from html

I have a VBA script that successfully pulls data from html and dumps it in an excel sheet. My problem occurs when some of the data is a very large integer (30 digit+ numbers) microsoft excel stores this as scientific notation by default and I suffer data loss.
So the data is stored in html like this: 

<tr>
<td>
      11111111111111111111111111
</td>
</tr>

so my script loops through the rows (tr) and for each cell (td) pulls the value into my sheet, like so: 

Set mydata = appIE.Document.getelementsbytagname("tr")
x=1
        For Each e In mydata
            Set mytd = e.getelementsbytagname("td")
            For Each c In mytd

                    Cells(x, 2).Value2 = e.Cells(1).innerText
                    x = x + 1

            Next c
        Next e

So i believe the error is occurring because I am using .value2 to store the data in my cells, so I tried switching to .value which reproduced the error, and .text which failed to run. How can I set this data type as long or text properly without losing data?

Upvotes: 2

Views: 121

Answers (1)

Brandon Barney
Brandon Barney

Reputation: 2392

The best you're going to be able to do within Excel VBA is use a variant and explicitly declare a Decimal type conversion. This allows up to 29 digits to be stores (I only tested this using 1's. It shouldn't matter with other numbers as well, but putting it out there).

You'll want to do something like this:

Sub Test()
    Dim InputString As String
    Dim DecimalHolder As Variant

    ' Put the string representation of your number into a string variable
    ' For my purposes, this was mostly to verify the input since the editor
    ' will convert this number to Scientific Notation otherwise.
    InputString = "11111111111111199999999999999"

    ' Using the variant variable, store the number by converting the string to a decimal.
    DecimalHolder = CDec(InputString)

    ' This now works, and is accurate
    DecimalHolder = DecimalHolder + 1
End Sub

If any of your input numbers are over 29 characters long you will need to create a way of trimming them to the 29 character limit. If you don't youll get an overflow error when trying to store the number within the DecimalHolder variable.

The reason for all of this is the number of bytes that VBA grants each datatype. We use variants here since a variant has 16 bytes to work with (reduced to 12 when converted to decimal) whereas the double type has 8, long type has 4, and int type has 2.

If ultimately you dont need the number, but you need a character representation of the number (if these 'numbers' are serving the functions of Id's for example) then you can just store the number as a string instead. You wont be able to do numeric operations on strings without converting (though implicit conversion will likely just do it, and not tell you).

Upvotes: 1

Related Questions