I need to loop over a range of cells that contain text like: (IBM) Ibm Corporation. Then I need to extract the (IBM) and then strip the ()'s. Because stock symbols can be of varying length I thought a regular expression would be the tool to use. So far I have been unable to figure it out. I've done a lot of google searching and can't find an example of using regex to search for text in a calc cell. This is what I have so far:
'test code
cellLoc = oSheet.getCellByPosition(0, 1)
stk = cellLoc.String() 'Works, contains (IBM) Ibm Corporation
myRegex = createSearchDescriptor()
myRegex.SearchRegularExpression = True
myRegex.SearchString = "/([A-Z]/)"
found = oSheet.findFirst( myRegex)
MsgBox found
I had hoped that the variable found would contain (IBM) but the Msg Box line produces an Object variable not set error. I think my biggest problem is understanding how to apply the regex to the text in the cell. I wonder if someone could get my code working or point me at an example that will help me understand how to do it.
Tnanks, Jim
Last edited by cleanman2 on Wed Sep 03, 2014 7:51 pm, edited 1 time in total.
Try this thread: viewtopic.php?f=25&t=16132#p74995 - you need to initialise more than you did, and you need to link createSearchDescriptor to a document object.
Please note that you need \ instead of / to escape regular expression characters.
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar. Nederlandstalig forum
Thanks to you both for catching the / vs \ mistake. I actullly have linked the search descriptor to the document, its just I have been trying many different things to get this to work and I poseted the wrong code by mistake.
@floris v - thanks for the link, it looks like it will be helpful.
OK, I had a chance to look more closely at that thread. It dosn't use regex. I think I could solve my problem with out regex by looping through the contents of the cell and detecting the ( and then building a string character by character until I reached the ). But that seemed so messey. I thought I could simply extract (IBM) with regex and strip the ()'s without the looping and concantinating of the string.
It doesn't use regex, but it does show how to init the objects that you need to execute a search - because your code references an uninitialised object, that's what the error message was about.
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar. Nederlandstalig forum