|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Sorting Alphanumeric & Numeric values | slovenc0417 | Excel | 2 | 06-07-2013 07:58 PM |
Handle Text / Numeric values in SSRS while Export To Excel | achuki | Excel | 5 | 02-07-2012 02:14 PM |
[How To] Generate Alpha Numeric Values in Excel 2010 | stnicholas81 | Excel | 1 | 07-25-2011 01:31 AM |