Page 1 of 1

[Solved] Help with regex in a basic macro

PostPosted: 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   Expand viewCollapse view
  '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

PostPosted: 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

PostPosted: Sat Aug 30, 2014 11:26 pm
by floris v
Try this thread: https://forum.openoffice.org/en/forum/v ... 132#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

PostPosted: 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

PostPosted: 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

PostPosted: 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