Page 1 of 1
[Solved] Help with regex in a basic macro
Posted: Sat Aug 30, 2014 10:50 pm
by cleanman2
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:
Code: Select all
'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
Re: Help with regex in a basic macro
Posted: Sat Aug 30, 2014 11:21 pm
by acknak
Try:
...
myRegex.SearchString = "\([A-Z]+\)"
Note the needed slash characters are backslashes where your code uses forward slashes.
You may also need to set the "Match Case" option if you intend to only match upper-case strings.
Re: Help with regex in a basic macro
Posted: Sat Aug 30, 2014 11:26 pm
by floris v
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.
Re: Help with regex in a basic macro
Posted: Sun Aug 31, 2014 3:29 am
by cleanman2
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.
Regards, Jim
Re: Help with regex in a basic macro
Posted: Sun Aug 31, 2014 12:28 pm
by floris v
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.
Re: Help with regex in a basic macro
Posted: Wed Sep 03, 2014 7:51 pm
by cleanman2
If anyone is interested in the solution to this problem look at this linK
http://stackoverflow.com/questions/2561 ... es-in-a-ce
Regards, Jim