Another suggestion would be to write a macro that removes every ' in a
specific range automatically. That macro could then be connected to a
keyboard shortcut, a new toolbar button or be available in a menu.
I made this very simple example:
REM ***** BASIC *****
Option Explicit
CONST RowMin=0
CONST RowMax=999
CONST ColMin=0
CONST ColMax=9
Sub RemoveQuotes
Dim Sheet As Object
Dim CellString As String
Dim CellFormula As String
Dim Row As Integer, Col As Integer
Sheet=ThisComponent.Sheets.getByName("Sheet1")
For Row=RowMin To RowMax
For Col=ColMin To ColMax
CellFormula=Sheet.getCellByPosition(Col,Row).Formula
If Left(CellFormula,1)="'" Then
CellString=Sheet.getCellByPosition(Col,Row).String
If IsNumeric(CellString) Then
Sheet.getCellByPosition(Col,Row).setValue(Val(CellString))
EndIf
EndIf
Next Col
Next Row
End Sub
Of course this is not very useable at all. I guess there are thousands of
more skilled macro writers here who can modify it so it will be a lot
faster and more realistic to use. The macro should probably rather work
with a cell range and array, than just single cells, and the cell range
should be defined by what's currently highlighted. However I am just a
beginner when it comes to writing macros.
--
Johnny
Den 2006-06-19 08:57:34 skrev Royce & Faye Green <[EMAIL PROTECTED]>:
I am a new-comer to the OO users forum. I posted this problem on Friday
2006-06-16 and received a very prompt response from Dave (TAS).
Thanks! Here is the clarification that Dave was seeking regarding my
problem, & also a possible solution that I have tried out for myself:
Downloading - this was a MS Excel sreadsheet of numerical values,
including some text headings & dates, provided daily by a financial
web-site to which I subscribe/pay. When I click onto the link to obtain
this data, Windows describes it as a MS Excel file & asks 'do you want
to open this file or save it?' Clicking on either box provides me very
quickly/efficiently with a MS Excel spreadsheet which I can open with
OpenOffice.
My problem has been that every numeric, $value, and date, field in this
'table' appears in its own cell in the spreadsheet, but with a single
quote mark in front of each of the values, converting it to text. If I
use the SUM or any other math function on these cells, OO Calc (&
presumably Excel) simply ignores those (text)amounts but includes any
number values I have separately input into the spreadsheet myself.
Dave's suggestion of highlighting/ selecting & reformatting a number
format for the culprit cells unfortunately has absolutely no effect on
them; they just retain their 'text' quotation mark.
The rather cumbersome alternative I have now adopted (based on Dave's
question of whether it was an HTML webpage file) is to save the
downloaded spreadsheet in OO HMTL format, re-open it in OO Writer,
Edit/select all, and copy the contents into a blank Calc spreadsheet
where I 'Paste Special' them as RTF (rich text format). OO Calc then
accepts these cells as numbers (without any format prefix) and allows me
to operate on them with SUM or other functions & to re-define their
number format individually.
This solution does not work if I open the HTML file within OO Calc, as
Calc does not provide the RTF format as an option in this situation.
My new solution appears very cumbersome, but at least I can now work on
my down-loaded data in OpenOffice Calc. Thank you again, Dave.
Any other suggestions from other subscribers?
Kind regards,
Royce G.
--
Johnny
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
