Hi again!
I've just finished a macro that I THINK might be useful. If not, you are
welcome with suggestions. I hope it will work, because I wrote it with
OpenOffice.org Calc 1.1.5, since OpenOffice.org Calc 2.0 is too slow for
my PC.
I also wrote some instructions that I hope are possible to follow. Again,
I use OpenOffice.org Calc 1.1.5, so there might be some differences. The
instructions tells you how to make the macro global, so you can use it on
any spreadsheet, without first having to open the file I send to you now.
It also tells you how to assign it to a keyboard shortcut and how to use
the macro.
I send this to your personal email address as well as to the users mail
list. Two files are attached to it:
An spreadsheet example with the macro included and the manual, described
above. As I understand it, the attachments will be removed from the users
mailing list anyway, so I don't bother to send separate messages.
In short, I can say something about the macro here too. I didn't use much
of the code I sent in a previous message, see below. The macro opens a
small dialog, that allows you to select wether you want to remove those '
characters from a highlighted cell range or from the current sheet. If you
want it to apply only to a selection, you have to highlight a cell or a
range of cells before starting the macro dialog. If you forget to do that,
there is, of course, a cancel button to click.
When you did select what you want to do, just click Go and all cell
formulas will have their first character removed if it is a '.
Here is the code, but I can not include the dialog here, as far as I know,
but that's of course included in the spreadsheet file attached to this
message:
REM ***** BASIC *****
Option Explicit
Dim Dlg As Object
Sub StartRemoveQuotesDialog
DialogLibraries.LoadLibrary("Standard")
Dlg = CreateUnoDialog(DialogLibraries.Standard.RemoveQuotesDialog)
If Dlg.Execute() Then
Call RemoveQuotesMethodSelect
EndIf
End Sub
Sub RemoveQuotesMethodSelect
Dim Ctl As Object
If Dlg.getControl("SelectionOnly").getState Then
Call RemoveQuotesFromSelection
Else
Call RemoveQuotesFromSpreadSheet
EndIf
End Sub
Sub RemoveQuotesFromSelection
Dim Selection As Object
Dim Data As Variant
Selection=ThisComponent.CurrentSelection
Data=Selection.getFormulaArray
Selection.setFormulaArray(RemoveQuotes(Data))
End Sub
Sub RemoveQuotesFromSpreadSheet
Dim Sheet As Object, Cell As Object, Cursor As Object
Dim SheetName As String, Data As Variant
SheetName=ThisComponent.CurrentSelection.Spreadsheet.getSpreadsheet.getName
Sheet=ThisComponent.Sheets.getByName(SheetName)
Cell=Sheet.getCellByPosition(0,0)
Cursor=Sheet.createCursorByRange(Cell)
Cursor.GotoEndOfUsedArea(True)
Data=Cursor.getFormulaArray
Cursor.setFormulaArray(RemoveQuotes(Data))
End Sub
Function RemoveQuotes (Data As Variant) As Variant
Dim DataRow As Variant
Dim Row As Integer, Col As Integer
Dim RowMax As Integer, ColMax As Integer
RowMax=UBound(Data)
ColMax=Ubound(Data(0))
For Row=0 To RowMax
DataRow=Data(Row)
For Col=0 To ColMax
If InStr(DataRow(Col),"'")=1 Then
DataRow(Col)=Right(DataRow(Col),Len(DataRow(Col))-1)
EndIf
Next Col
Data(Row)=DataRow
Next Row
RemoveQuotes=Data
End Function
*******************************************************************************
Johnny Andersson
Den 2006-06-21 02:13:40 skrev Royce & Faye Green <[EMAIL PROTECTED]>:
Thanks Johnny; That seems the most straightforward solution, but I have
no experience with macros. Guess I should try them or get some tutorial
help.
Royce G.
----- Original Message ----- From: "Johnny Andersson"
<[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Monday, June 19, 2006 6:17 PM
Subject: Re: [users] REFORMATTING A CELL; TEXT TO NUMBER
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]
-- No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.9.0/368 - Release Date:
16/06/2006
--
Johnny
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
