[Solved] Help with regex in a basic macro

Keyboard macros or custom scripts

[Solved] Help with regex in a basic macro

Postby cleanman2 » Sat Aug 30, 2014 10:50 pm

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
Last edited by cleanman2 on Wed Sep 03, 2014 7:51 pm, edited 1 time in total.
OpenOffice 3.2 on Ubuntu
cleanman2
 
Posts: 36
Joined: Tue Jan 25, 2011 6:21 pm

Re: Help with regex in a basic macro

Postby acknak » Sat Aug 30, 2014 11:21 pm

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
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Help with regex in a basic macro

Postby floris v » Sat Aug 30, 2014 11:26 pm

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.
AOO 4.1.6 op Linux Mint
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
User avatar
floris v
Moderator
 
Posts: 4139
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Help with regex in a basic macro

Postby cleanman2 » Sun Aug 31, 2014 3:29 am

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

Re: Help with regex in a basic macro

Postby floris v » Sun Aug 31, 2014 12:28 pm

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.
AOO 4.1.6 op Linux Mint
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
User avatar
floris v
Moderator
 
Posts: 4139
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Help with regex in a basic macro

Postby cleanman2 » Wed Sep 03, 2014 7:51 pm

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


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 1 guest