Ask Your Question
0

Just a column on a worksheet

asked 2017-11-12 05:20:09 +0200

datum gravatar image

updated 2017-11-12 05:56:05 +0200

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2017-11-13 03:38:28 +0200

Ratslinger gravatar image

updated 2017-11-13 18:58:49 +0200

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).

edit flag offensive delete link more

Comments

... 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")
Mike Kaganski gravatar imageMike Kaganski ( 2017-11-13 08:14:20 +0200 )edit

@Mike Kaganski 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.

Ratslinger gravatar imageRatslinger ( 2017-11-13 15:22:21 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-11-12 05:20:09 +0200

Seen: 58 times

Last updated: Nov 13 '17