On Thursday 28 July 2005 00:34, Jonathon Coombes wrote:
> Hi Trev,
>
> You can insert the filename directly into the spreadsheet using the
> CELL function. The format will be something like:
>
> =CELL("FILENAME";A1)
>
> And it will return the filename into the cell where it is entered.
Thanks Jonathon
and for those interested here is a refinement of that output
=CELL("FILENAME") and for the filenae you can leave out the cell reference
returns
'file:///home/trev/testnameforcalc.sxc'#$Sheet1
=SUBSTITUTE(LEFT(CELL("filename");FIND("#";CELL("filename"))-2);"'file://";"")
returns
/home/trev/testnameforcalc.sxc
as i dont' want to see the sheet name or the 'file:// at the beginning.
My formula assumes that there will always be 'file:// at the beginning of the
function value - I think this is a reasoanble assumption"
And this is what I really wanted to do - be able at the click of a buton
insert the path and filename into a cell. So here is a macro (I just used the
recorder) that enters the above formula into the currently selected cell.
Then I just assigned the macro to a button on one of my toolbars. Remember to
save the macro in a library that is globally accessible.
sub InsertPathFileName
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "StringName"
args1(0).Value =
"=SUBSTITUTE(LEFT(CELL("+CHR$(34)+"filename"+CHR$(34)+");FIND("+CHR$(34)+"#"+CHR$(34)+";CELL("+CHR$(34)+"filename"+CHR$(34)+"))-2);"+CHR$(34)+"'file://"+CHR$(34)+";"+CHR$(34)+CHR$(34)+")"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())
rem ----------------------------------------------------------------------
Unfortunately this formula does not seem to update when I use save as to save
spreadsheet with a different name not even after pressing F9 (recalc).
However after closing the doc and opening it again the value has been
updated!
Thanks again
Trev
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
