* Mark Knecht <[EMAIL PROTECTED]> [050207, 18:39]:
> On 2/5/07, Ennio-Sr <[EMAIL PROTECTED]> wrote:
> > [ snip ]
>
> Hi Ennio,
> First, and again, thanks for your help on this.
>
> OK, I've installed your macro and figured out how to bind it to a
> key. CTRL+A seems to be a default assignment to select all cells in
> the spreadsheet so I bound it to CTRL+SHIFT+A and it seems to
> function. However I have two problems:
>
> 1) The macro moves the current location of the active cell to the cell
> being written. The defeats the main thing I wanted which was to not
> move away from the location I'm getting the value from. Can you think
> of how to modify the macro so that it pastes the contents of the cell
> I'm in into $A$1 but remains at the cell I'm in and doesn't move?
>
Hi Mark,
this 'revised' macro would give you the possibility to stick to the cell
you are into, but it lacks a function I've not been able to figure out,
as I explain in the macro itself (see bottom)
> 2) Since my main spreadsheet page is a roll-up of a spreadsheet that
I'll examine this point later to see if I can help: be it clear that I'm
no expert but just curious and have some time to spare ... ;-)
Perahps somebody will help update Andrew Pitonyak function to get the
readable cell address to return to.
Regards,
Ennio
------------------------
REM ***** BASIC *****
sub cptoa1
rem get the active cell (the one you click at) and its address:
oActiveCell = ThisComponent.getCurrentSelection()
oCellAddress=oActiveCell.getCellAddress()
sh=oCellAddress.sheet
col=oCellAddress.column
row=oCellAddress.row
rem --------------------
oDoc = ThisComponent.CurrentController.Frame
dim dispatcher as object
dim Selection as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
'
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
rem *****************************************************************
rem As the cell address is expressed numerically, we need convert them in
rem readable address: unfortunately, Andrew Pitonyak functions
rem (see bottom) do not seem to work: this is a mere 'very poor'
rem workaround just to test the macro
if col=0 then
col="A"
endif
if col=1 then
col=B
endif
row=row+1
s$=col+row
'print s$ 'ok it works!
rem *******************************************************************
args2(0).Value = s$
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
end sub
rem ================================================================
rem AND THESE ARE THE OLD A.PITONYAK FUNCTIONS THAT NEED UPDATING
rem Listing 6.8: Cell address in a readable form using CellAddressConversion.
rem oActiveCell = ThisComponent.getCurrentSelection()
rem oConv =
ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
rem oConv.Address = oActiveCell.getCellAddress
rem Print oConv.UserInterfaceRepresentation
rem Print oConv.PersistentRepresentation
'sub andrew1
'Given a cell, extract the normal looking address of a cell
'First, the name of the containing sheet is extracted.
'Second, the column number is obtained and turned into a letter
'Lastly, the row is obtained. Rows start at 0 but are displayed as 1
rem dim the_cell as object
rem Function PrintableAddressOfCell(the_cell As Object) As String
rem PrintableAddressOfCell = "Unknown"
rem If Not IsNull(the_cell) Then
rem PrintableAddressOfCell = the_cell.getSpreadSheet().getName + ":" + _
rem ColumnNumberToString(the_cell.CellAddress.Column) +
(the_cell.CellAddress.Row+1)
rem End If
rem End Function
' Columns are numbered starting at 0 where 0 corresponds to A
' They run as A-Z,AA-AZ,BA-BZ,...,IV
' This is esentially a question of how do you convert a Base 10 number to
' a base 26 number.
' Note that the_column is passed by value!
' Function ColumnNumberToString(ByVal the_column As Long) As String
' Dim s$
'Save this so I do NOT modify the parameter.
'This was an icky bug that took me a while to find
'Do while the_column >= 0
' s$ = Chr(65 + the_column MOD 26) + s$
' the_column = the_column \ 26 - 1
'Loop
'ColumnNumberToString = s$
' End Function
'end sub
rem ==================================================
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
