Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-07-2018, 04:48 AM
ArviLaanemets ArviLaanemets is offline Excel VBA interprets numeric values from worksheet formatted as General in several ways Windows 8 Excel VBA interprets numeric values from worksheet formatted as General in several ways Office 2016
Expert
Excel VBA interprets numeric values from worksheet formatted as General in several ways
 
Join Date: May 2017
Posts: 869
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 Excel VBA interprets numeric values from worksheet formatted as General in several ways

We get from clients purchase forecasts info as text/Excel/HTML/etc. files.



I have created for client managers Excel applications, which open client file, read in data from there, make some calculations, and write results as table in certain format into Excel or into text file. The result table is then read into our ERP system.

Currently I have problem with xls-type Excel file from client.
When I open the file with Excel, I get the message "The file format and extension of 'ClientFileName.XLS' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway". Obviously the client had original file p.e. in xlsx-format, and simply renamed extension instead of opening the file and using Save As to convert it. Anyway I haven't control over it.

When I click Yes, the file opens OK. All cells on worksheet are formatted as General. As most entries are integer values, they are displayed as numbers (aligned to right). The only column having entries with decimals is also OK in my computer with comma as decimal delimiter, and in colleges computer with point as decimal delimiter.

When the VBA opens the same file, it is a mess!
Some columns remain OK. 1 remains 1, 0 remains 0, etc;
In other columns 1 - 3 decimal zeros are added to integers, and they are interpreted as texts (in my computer in one column 100 is shown as 100,0, in other column 0 is displayed as 0,000 - all aligned to left), etc.;
The worst columns multiply all values with 1000, except 0's. P.e. 1500 is now displayed 1 500 000 in my computer (I have space as thousands delimiter), and 1,500,000 in colleges computer (she has US settings). And 0 is replaced in my computer as text 0,000 (aligned to left).

Ha someone a clue what is going on?
Reply With Quote
  #2  
Old 03-08-2018, 01:58 AM
ArviLaanemets ArviLaanemets is offline Excel VBA interprets numeric values from worksheet formatted as General in several ways Windows 8 Excel VBA interprets numeric values from worksheet formatted as General in several ways Office 2016
Expert
Excel VBA interprets numeric values from worksheet formatted as General in several ways
 
Join Date: May 2017
Posts: 869
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
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA interprets numeric values from worksheet formatted as General in several ways Numeric Values jrt Word VBA 2 11-09-2014 01:28 PM
Convert alphabets to numeric values kjxavier Word 3 07-06-2014 05:34 AM
Excel VBA interprets numeric values from worksheet formatted as General in several ways Sorting Alphanumeric & Numeric values slovenc0417 Excel 2 06-07-2013 07:58 PM
Excel VBA interprets numeric values from worksheet formatted as General in several ways Handle Text / Numeric values in SSRS while Export To Excel achuki Excel 5 02-07-2012 02:14 PM
Excel VBA interprets numeric values from worksheet formatted as General in several ways [How To] Generate Alpha Numeric Values in Excel 2010 stnicholas81 Excel 1 07-25-2011 01:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft