OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

display file name in cells OOo.calc

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
deqwan22
General User
General User


Joined: 09 May 2007
Posts: 15

PostPosted: Thu May 10, 2007 11:54 pm    Post subject: display file name in cells OOo.calc Reply with quote

hye
i want the spreadsheet to display its file name in a cell
example:

[size=18]-->open filename: 075806_c1218.ods
cell b1will display 075806
cell b2 will display c1218


how am i suppose to do that?
i appreaciate any help
thanks[/size]
Back to top
View user's profile Send private message
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Fri May 11, 2007 2:27 am    Post subject: Reply with quote

use a Calc function (placed within a module in OOo's standard library) to get the filename, then use string functions to separate

short version of function (w/o any error checking)

Code:
Function calc_FileName()
oDoc = ThisComponent
GlobalScope.BasicLibraries.LoadLibrary( "Tools" )
' filename separator for Windows Systems !
calc_DateiName = FileNameoutofPath( ConvertFromURL( oDoc.URL ), "\" )
End Function


Can you work it out from here?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 11, 2007 2:31 am    Post subject: Reply with quote

=CELL("filename") returns the path-name of the document without macro.
Use some text-functions to extract the part you want.
Some named references of my default template:
cFilename =CELL("FILENAME")
FilePath =RIGHT(FileURLPath;LEN(FileURLPath)-7)
FileURL =MID(cFilename;2;SEARCH("'#";cFilename)-2)
FileURLPath =MID(cFilename;2;SEARCH("/[^/]+'#";cFilename)-1)
shSep =MID(ADDRESS(1;1;1;"Foo");4;1)
ThisSheet =MID(cFilename;FIND("#$";cFilename)+2;256)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
deqwan22
General User
General User


Joined: 09 May 2007
Posts: 15

PostPosted: Fri May 11, 2007 7:00 pm    Post subject: Reply with quote

Sub Main

CellPos(1,2).setstring('''''howto put the file name here"""")
CellPos(1,3).setstring(''''howto put the file name here""""")

'ass= calc_FileName()
End Sub

Function CellPos(lColumn As Long,lRow As Long)
CellPos= ActiveSheet.getCellByPosition (lColumn,lRow)
End Function

Function ActiveSheet
ActiveSheet=StarDesktop.CurrentComponent.CurrentController.ActiveSheet
End Function
Back to top
View user's profile Send private message
GBallarè
Newbie
Newbie


Joined: 08 Apr 2007
Posts: 2

PostPosted: Sun May 13, 2007 8:05 pm    Post subject: Reply with quote

Hi,
horrific but very simple:
Code:

Sub InsertSplitFileName

dim i%
dim oThisDoc as object, sThisUrl$, aSplitUrl()
dim aSplitFileName(), sFileName$, sExtFileName$
dim sFileNameLeft$, sFileNameRight$, aSplitName()

oThisDoc = ThisComponent
sThisUrl = ThisComponent.getUrl

aSplitUrl = Split(sThisUrl,"/")
i = UBound(aSplitUrl)

sExtFileName = aSplitUrl(UBound(aSplitUrl))

aSplitFileName =  Split(sExtFileName,".")

sFileName = aSplitFileName( UBound(aSplitFileName) -1)

aSplitName = split(sFileName, "_")

sFileNameLeft = aSplitName(LBound(aSplitName))

sFileNameRight = aSplitName(UBound(aSplitName))

ThisComponent.CurrentController.ActiveSheet._
   getCellByPosition(1,2).setstring(sFileNameLeft)
ThisComponent.CurrentController.ActiveSheet._
   getCellByPosition(1,3).setstring(sFileNameRight)

end sub


Regards. GBallarè
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group