Just a column on a worksheet

[datum]

I have a routine that searches and replaces text strings throughout a whole workbook.

sub MyFindReplace(strSearch, strReplace)
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args(7) as new com.sun.star.beans.PropertyValue
args(0).Name = "SearchItem.AllTables"
args(0).Value = true
args(1).Name = "SearchItem.SearchFiltered"
args(1).Value = false
args(2).Name = "SearchItem.Pattern"
args(2).Value = false
args(3).Name = "SearchItem.Content"
args(3).Value = false
args(4).Name = "SearchItem.AlgorithmType"
args(4).Value = 0
args(5).Name = "SearchItem.SearchString"
args(5).Value = strSearch
args(6).Name = "SearchItem.ReplaceString"
args(6).Value = strReplace
args(7).Name = "SearchItem.Command"
args(7).Value = 3

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args())

end sub

How do I get it to operate only on a specific column on a specific worksheet?

If that’s not possible, then searching in a selection would be okay too.

Thanks.

[Ratslinger]

Hello,

The method you have posted is for entire documents and can be used in other modules such as Writer. To do what you ask in Calc a different approach is taken (although similar). It really can be adjusted to perform the operation on a portion of the sheet or an entire sheet.

Sub MyFindReplace2(strSheetName, strCellRange, strSearch, strReplace)
  oSheet=ThisComponent.Sheets.getByName(strSheetName)
  oData = oSheet.getCellRangeByName( strCellRange )
  oCellFormatRanges = oData.getCellFormatRanges()
  oRangeSelected = oCellFormatRanges.getByIndex(0)
  xSearchDesc = oRangeSelected.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  oRangeSelected.replaceAll(xSearchDesc)
End Sub

Edit:

A sample call for specific cells:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A2:A15", "Good", "Good-Bye")
End Sub

A sample call for entire column:

Sub ReplaceIt
  MyFindReplace2("Sheet1", "A:A", "Good", "Good-Bye")
End Sub

To search entire sheet, change last line of macro to oSheet.replaceAll(xSearchDesc)

Edit 11/13/2017 (Recent change adds error checking for Sheet name & Cell range):

In light of the comments below, here is a second version with added capabilities:

Sub MyFindReplace3(strSheetName, Optional strCellRange, strSearch, strReplace)
  on error goto SheetError
  oSheet=ThisComponent.Sheets.getByIndex(0)
  xSearchDesc = oSheet.createSearchDescriptor()
  xSearchDesc.SearchString = strSearch
  xSearchDesc.SearchCaseSensitive = true
  xSearchDesc.SearchWords = true
  xSearchDesc.ReplaceString = strReplace
  If IsMissing(strCellRange) Then
	strCellRange = "*"
  Else
	iResult = InStr(strCellRange, ",")
	iRangeCount = 0
    while iResult > 0
    	iRangeCount = iRangeCount + 1
    	iResult = InStr(iResult + 1,strCellRange, ",")
    wend
  EndIf
  If strSheetName = "*" Then
  	iSheetCount = ThisComponent.Sheets.getCount()
  	For x = 0 to iSheetCount -1
  		oSheet1 = ThisComponent.Sheets.getByIndex(x)
		oSheet1.replaceAll(xSearchDesc)
	Next
  Else
  	If strCellRange = "*" Then
		oSheet1 = ThisComponent.Sheets.getByName(strSheetName)
		oSheet1.replaceAll(xSearchDesc)
	Else
		iResult = InStr(strCellRange, ",")
		oSheet1 = ThisComponent.Sheets.getByName(strSheetName)
        on error goto RangeError
		If  iResult = 0 Then
			oData = oSheet1.getCellRangeByName( strCellRange )
			oCellFormatRanges = oData.getCellFormatRanges()
			oRangeSelected = oCellFormatRanges.getByIndex(0)
			oRangeSelected.replaceAll(xSearchDesc)
		Else
			Dim sCellSplit(iRangeCount) As String
			sCellSplit = Split(strCellRange,",")
			For x = 0 to iRangeCount
				oData = oSheet1.getCellRangeByName( sCellSplit(x) )
				oCellFormatRanges = oData.getCellFormatRanges()
				oRangeSelected = oCellFormatRanges.getByIndex(0)
				oRangeSelected.replaceAll(xSearchDesc)
			Next
		EndIf
	  EndIf
  EndIf
  Exit sub
  SheetError:
    MsgBox "Specified Sheet name not found."
    Exit sub
  RangeError:
    MsgBox "Error in Cell Range"
End Sub

Parameter usage:

• strSheetName - “SPECIFIC_SHEET” will only look at named sheet; “*” will replace in ALL sheets dis-regarding strCellRange.

• strCellRange - Now Optional. If missing, will replace all as specified in strSheetName; If “*” will replace all as specified in strSheetName; will accept “A:A” for entire column, “A1:A15” for specifics in column, “A:B” for contiguous columns, or “A:A,C:C” for non-contiguous columns. Note this is sample & other combinations apply.

• strSearch is the string to search for

• strReplace is the replacement string

If this answers your question please click on the (upper left area of answer).

[mikekaganski]

… or make first lines of replacement sub like

Sub MyFindReplace2(oCellRange, strSearch, strReplace)
  oCellFormatRanges = oCellRange.getCellFormatRanges()

and call it like

MyFindReplace2(ThisComponent.Sheets.getByName("Sheet1").getCellRangeByName("A2:A15"), "Good", "Good-Bye")
MyFindReplace2(ThisComponent.Sheets.getByName("Sheet1").getCellRangeByName("A:A"), "Good", "Good-Bye")
MyFindReplace2(ThisComponent.Sheets.getByName("Sheet1"), "Good", "Good-Bye")

[Ratslinger]

@mikekaganski Thanks for the input - always welcome. However this would take away some potential flexibility in the routine. For example, by sending “*” as the sheet name, have routine check & if found, loop through all the sheets to react on entire document. Or for Cells, you could accommodate for non-contiguous columns.