Office Software Thread, [Excel/SIMS] Interpreting Reg Groups as Scientific Formulae in Technical; Before I start writing a macro to correct this (which will be a total PITA as it'll have to be ...
1st February 2011, 11:03 AM #1
[Excel/SIMS] Interpreting Reg Groups as Scientific Formulae
Before I start writing a macro to correct this (which will be a total PITA as it'll have to be applied to every report in the system!), does anybody know how to stop Excel automatically formatting cells?
The problem I'm having is that some of our Reg groups contain the letter 'E' followed by a single number (e.g. 10E1, 9E2, 8E1 etc.). When you run a report from SIMS and set the output to Excel, Excel sees this and interprets it as a scientific forula so 10E1 becomes 100, 9E2 becomes 900 etc.
1st February 2011, 05:51 PM #2
- Rep Power
Sorry LosOjos - I can't offer any help here. I spent the first 10 years of my school career dealing with this. (We are a 3 site school and from when the three sites amalgamated in the 80's the tutor groups were named 10W1 - meaning on the west site; 10E1 - meaning east site and 10C1 meaning centre site. When some wag decided we would start a house system I wrung my hands and worried for ages about having to make wholescale changes to Sims. When I had made the changes, I then realised I had lost this problem. Perhaps you could make this suggestion!!
2nd February 2011, 09:23 AM #3
It's so irritating! It stems from the way SIMS generates the CSV I believe. If each field (other than numeric fields) were encased in quotes, I think Excel would interpret them as text rather than formulae...
But nout I can do about that! So looks like I'm just going to have to live with it! Thanks for replying Anjelica, it's nice at least to know I'm not the only one!
2nd February 2011, 09:36 AM #4
Have you tried creating an Excel template with the column in question formatted as text?
2nd February 2011, 09:45 AM #5
When SIMS runs reports in to Excel, the template you create isn't the same workbook that gets used for the result - that just imports the data from CSV, manipulates it and puts it in to a new sheet.
Originally Posted by CESIL
At the moment, my only way around it is to parse the column and "hard code" the values (just precede them with ', hard code sounded more impressive lol)
By adamf in forum MIS Systems
Last Post: 11th October 2010, 04:00 PM
By saundersmatt in forum MIS Systems
Last Post: 27th March 2010, 07:27 PM
By stu in forum How do you do....it?
Last Post: 1st April 2008, 11:47 AM
By Andie in forum Wireless Networks
Last Post: 18th March 2008, 03:19 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)