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

How to Find & Replace in Calc

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Sat Apr 17, 2004 4:25 am    Post subject: How to Find & Replace in Calc Reply with quote

Until now I have been unable to get Find & Replace working in Calc macros (OOo1.1.1). I've finally got it to work, so thought I would share the method here, as my previous searches didn't find a solution that worked.

Initially, I tried the Macro Recorder, but the Dispatcher method caused a dialog box to pop up when no match was found, for which I could find no remedy. So I began to explore a method using the APIs. Based on lots of sources (Andrew Pitonyak, OOo docs, this forum, Google general) I have been trying this:
Code:

Dim oDoc As Object
Dim oSheet As Object
Dim oReplace As Object         'Replace Descriptor (ScCellSearchObj)
Dim lNumReplace As Long

oDoc = ThisComponent
oSheet = oDoc.Sheets.getByIndex(0)

oReplace = oSheet.createReplaceDescriptor()
oReplace.SearchCaseSensitive = True
oReplace.SearchWords = False
oReplace.SearchRegularExpression = False
oReplace.SearchBackwards = False
oReplace.SearchSimilarity = False
oReplace.SearchSimilarityRelax = False
oReplace.SearchStyles = False
oReplace.SearchByRow = True

oReplace.SearchString = "Find"
oReplace.ReplaceString = "Replace"
lNumReplace = oSheet.replaceAll(oReplace)


Stepping through the code, using the debugger and the XRay tool, I could see that both oReplace.SearchString and oReplace.ReplaceString remained at "" (ie, NULL) through all this; thus no Find & Replace was happening.

Using the XRay tool, I saw two methods of ScCellSearchObj, setSearchString() and setReplaceSting(). Putting two and two together, this is how I got it to work:
Code:


oReplace.setSearchString("Find")
oReplace.setReplaceString("Replace")
lNumReplace = oSheet.replaceAll(oReplace)

Following each setSearchString() and setReplaceString() instruction, the debugger showed that both oReplace.SearchString and oReplace.ReplaceString were getting their respective strings, and the Find & Replace then worked. Incidentally, the APIs mention these methods in com.sun.star.util under XSearchDescriptor and XReplaceDescriptor. I hope this helps others who are trying to Find & Replace in Calc macros, and saves them a bit of their time.
Back to top
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Sun Apr 18, 2004 7:15 pm    Post subject: Reply with quote

Moderators --

Perhaps this should be moved to Code Snippets?

Cheers,

Erik
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