Knowledge Management

Question:
We regularly import data into Excel through a function module and all is well until the user insists that there should always be two decimal places in the number even if it's 100.00. Excel interprets that as a number and drops the decimals - unless there is a thousandth position, in which case the comma apparently prompts Excel to leave the formatting alone.
Formatting "dollar" and "fixed" commands don't work because they are then interpreted as text, left justified and unavailable for mathematical operations in Excel.
The users are dismayed that they have to highlight the column and click on the $ icon in Excel and I'm tasked with addressing this inconvenience.
Does anyone know a solution?
Answer:
Put a single quote (') in front of it and Excel will interpret it as text.
Answer:
That's the big problem. How to have it in numeric format and still import with all provided decimals.
Answer:
Most of the time when exporting to excel I declare the decimal fields as floating point. the output is with EXP nn. Excel interprets that as Number & formats as per column spec.
Try & see if it worls for you.
Answer:
Robert,
I put '100,00 in a cell.
Then it appers as 100,00
In another cell I put 2*A1, which results in 200.
That seems to be the answer to your problem, I think.
Oscar
Answer:
Still, alas, it's not the answer. The items format into text left justified and if you higlight and hit the sum, it won't sum automatically and that's what is desired. A formula can be applied, of course, but that's certainly requires a lot more effort than simply highlighting the column and choosing the $ Icon in Excel. Currently, the values are numeric and being imported properly - they're simply getting decimals dropped if the trailing decimals are zero.
Answer:
Could you explain how the situation differs from if they were to type the numbers into Excel manually. What I mean is, why should an SAP download trip up Excel if typing in the numbers manually does not?
Also, are data drawn from another source besides SAP for this spread sheet?
Answer:
Good luck Robrert. I have the same problem, and although not very elegant, but it does get rid of the complaints, after the downloads I open Excel, load the extract file, highlight the offending columns and use format number 2 decimals. then save the file as and xls file. As I said, not elegant, but it gets rid of the grips until I can find a true solution.
Answer:
Robert, I can't explain that to you since it's the same question I keep asking myself. If they type the numbers, they'll get trailing zero decimal positions deleted, too. It's not a problem of unusuable data; on the contrary, the data is exactly right and in a format ready to be used. It just isn't as pretty as if I developed an Excel Macro; loaded it into every desktop that will use this application and then execute the download with the Macro.
In this case, I'm actually attaching the spreadsheet to an e-mail and sending it to them via SAPSERV automatically when they run it in background so I don't get a chance to make the two clicks that will straighten up their "issue" before they get it.
Answer:
I was also going to suggest that you dispense with Excel and make a custom Abap program. That would depend on the contents of the spreadsheet, of course, and whether or not Exel functions are necessary.
Answer:
Dear Robert W.:
Even if Excel does not display it, 100 is correct, since the two decimals are zero. Likewise, 99.10 would come across as 99.1, still a correct value. If you want to display two decimals, you could use OLE (or manually perform the task) to go to Cell -> Format -> Number.
I don't see this as a problem of accuracy, only of the display to the user. If you use OLE, you can probably manipulate the appearance, but not if you upload a flat file.
Regards,
Wolfgang
Answer:
I am researching the same problem. We recently upgraded from 4.5B to 4.6C. After this upgrade, we started noticing funny things happening with Ad Hoc Query and our custom version of Flex Employee Data. Users loved being able to just click the little Excel button and have the grid changed into a spreadsheet before their eyes. But the users don't like how the data is formatted.
I can't explain it. I can only tell you the symptoms that have been reported.
Some users report that all numbers come over to Excel as text. There isn't any problem with losing positions to the right of the decimal place, because it is just a string of text.
Some users report that dates come over to Excel as text. They are generally unsortable because users keep their default date format in the standard US MM/DD/YYYY. The dates are therefore sorted month-first.
What is even wierder, the results are different on my PC and that of one of our users reporting the problem. We run the same variant of the same query and when we click on the Excel button, some of my date columns are real Excel dates, and some of her date columns are garbage (numbers and symbols). The rest are all text.
We have tried to highlight a column in Excel, right-click to Format Cells and change the format. As crazy as this sounds, it does absolutely nothing. If I then edit a cell in that column (just hit f2 and enter, making no changes), the new cell format takes effect in that cell only. I have to do this for every row in spreadsheet.
We use Excel 97 SR-2. Everyone is supposedly on the same version of the SAP GUI client although some run the "slow" option and some run the "fast" option.
Thanks for any clues,
billb
p.s. I feel your pain!
Answer:
I know that im not replying a solution for your problem but Robert Williams is the only one that can help me, i need a example in how to send a excel file as attachment using the function SO_NEW_DOCUMENT_ATT_SEND_API1.
Thanks
Mike Garza