Thread: [Solved] Excel VBA interprets numeric values from worksheet formatted as General in several ways
View Single Post
 
Old 03-08-2018, 01:58 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
  
Join Date: May 2017
Posts: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default
I did find out what happened!

Client created a tab-delimited text file. As client used comma as decimals delimiter, in some columns integer values were saved with decimals, like 0,000 or 1500,000 or 1,0 etc.
Then client RENAMED the text file as xls-file! Renamed instead of opening it with Excel, and then saving in Excel format.

As result, the file really remained a text file - no Excel header, no formatting codes, etc.

When I opened the file with Excel, Excel complained a bit, but was able to display it correctly, as my regional settings were same as client had. I think for my college with US settings some columns were interpreted as text.

When VBA opened the file, then it ignored local settings and used US settings in Excel instance where the file was opened. The result was a mess I described in first post.

I renamed the client file back to *.txt, opened it with Excel, and saved as *.xls. The new file was converted properly.
Reply With Quote