Need help with regex in a calc macro

users
#1

I have some cells that contain data like: (IBM) Ibm corporation.

I need to extract the stock symbol ie IBM. I thought I could use regex to get (IBM) and then strip the parens. So far I have been uable to do so. Usually I can search and find examples of code I can study and modify to suit my needs, but regex examples for calc seem to be few and far between.

I wonder if someone could point me to some good calc regex examples or take a look at the test code below that I have been experimenting with and tell me what I am doing wrong.

Sub getMktValue()
   'Main sub
   Dim oDoc as Object
   Dim oSheet as Object
   Dim oCalcCtrl as Object
   Dim oCell as Object
   Dim oCursor as Object
   Dim oRange as Object
   Dim myRegex as Object
   Dim found as Object
   Dim cellLoc as Object

   oDoc = ThisComponent
   'oSheet = ThisComponent.Sheets.getByName("Income")
   oSheet = oDoc.Sheets.getByName("Income")
   oRange = oSheet.getCellRangeByName("Stocks")
   numRows = oRange.Rows.getCount()

   'regex test code
   cellLoc = oSheet.getCellByPosition(0, 1)
   stk = cellLoc.String()
   myRegex =cellLoc.createSearchDescriptor()
   myRegex.SearchString = "\([A-Z]\)"
   myRegex.SearchRegularExpression = True
   found = cellLoc.findFirst(myRegex)

   'MsgBox found

End Sub

This code as is runs with out error, but if I uncomment MsgBox found I get a "object variable not set" error.

Thanks, Jim

#2

The pattern [A-Z] will match any single capital letter, so your string will match (I) but not (IBM). You need something like "\([A-Z]+\)" - where the "+" after the "[A-Z]" makes the combination match one or more consecutive capital letters.

I trust this helps.

Brian Barker