[Solved] Help with regex in a basic macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
cleanman2
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

[Solved] Help with regex in a basic macro

Post 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
Last edited by cleanman2 on Wed Sep 03, 2014 7:51 pm, edited 1 time in total.
OpenOffice 3.2 on Ubuntu
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Help with regex in a basic macro

Post 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.
AOO4/LO5 • Linux • Fedora 23
User avatar
floris v
Volunteer
Posts: 4423
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Help with regex in a basic macro

Post 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.
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
cleanman2
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: Help with regex in a basic macro

Post 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
OpenOffice 3.2 on Ubuntu
User avatar
floris v
Volunteer
Posts: 4423
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Help with regex in a basic macro

Post 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.
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
cleanman2
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: Help with regex in a basic macro

Post 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
OpenOffice 3.2 on Ubuntu
Post Reply