'Writing record to SharePoint using VBA numbers being converted to scientific notation
I have a macro that connects to a SharePoint using ADODB.Connection and loops through a spreadsheet adding records to the SharePoint list. I just noticed that when adding some of account number information where the numbers are longer it converts it to scientific notation when it assigns the value to rst.Fields("AccountNumber"). If I assign the number to a variable first, it looks fine when I debug, but as soon as it gets past this point in the code, the rst.Fields("AccountNumber") shows the scientific notation, which then is dropping digits off the number and replacing them with zero's(obviously not good when you need the actual account number to look stuff up later). Any thoughts on how to prevent this? I have already asked the owner of the SharePoint list if we could convert the field to text rather than it being number, but they are unable to do that because it messes up what they're using the data for after it's been uploaded, so I have to stop it on my end.
Solution 1:[1]
Most people probably know this already but for those who don't Excel has a 15 digit limit for numbers before it turns it into scientific notation. The only way around that is to display everything as text, which works great as long as you're not doing any kind of mathematical calculations. Where SharePoint is part of Microsoft Office products it has the same limitation, with the same solution. Since changing the field to be text on SharePoint wasn't an option for my end user there was nothing that could be done to correct the issue.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | DizturbdOne |