主页 / user-1187453

JCB's questions

Martin Hope
JCB
Asked: 2023-11-22 14:58:26 +0800 CST
  • 5

我正在尝试创建一个在 Calc 中使用的 LibreOffice Basic 宏，当在打开的电子表格文件中调用该宏时，将打开包含两个范围的第二个电子表格文件（第一个范围包含带有要搜索的正则表达式的单元格，第二个范围包含替换内容） text）然后将在调用它的工作表中搜索搜索范围内的所有项目，并将工作表中找到的任何项目替换为相应的替换单元格（包括替换单元格的格式）。我向 ChatGPT 寻求帮助，以下是它提供的一般内容，以及我对确定范围大小的逻辑所做的一些更正。

Sub SearchAndReplaceItems

    Dim oDoc As Object
    Dim oSearchReplaceDoc As Object
    Dim oSheet As Object
    Dim oSearchReplaceSheet As Object
    Dim oSearchDescriptor As Object
    Dim oSearchRange As Object
    Dim oReplaceDescriptor As Object
    Dim oCell As Object
    Dim oFound As Object
    Dim nSearchColumn As Integer
    Dim nReplaceColumn As Integer
    Dim nLastRow As Integer

    ' Get the current document
    oDoc = ThisComponent

    ' Specify the path to the Corrections spreadsheet
    Dim sSearchReplaceFilePath As String
    sSearchReplaceFilePath = "C:\Users\One\Info\Corrections.ods"

    ' Open the Corrections spreadsheet
    oSearchReplaceDoc = StarDesktop.loadComponentFromURL(ConvertToURL(sSearchReplaceFilePath), "_blank", 0, Array())
    oSearchReplaceSheet = oSearchReplaceDoc.Sheets(0)

    ' Specify the column numbers for search and replace text
    nSearchColumn = 1 ' Assuming search text is in column A
    nReplaceColumn = 2 ' Assuming replace text is in column B

    ' Get the range for the search regular expressions
    oSearchRange = oSearchReplaceDoc.Sheets(0).getCellRangeByName("SearchRegExps")

    ' Get the last row with data in the SearchRegExps range
    nLastRow = oSearchRange.Rows.getCount()

    ' Loop through each row History Item Corrections sheet
    For i = 1 To nLastRow
        ' Get search and replace values from the current row
        Dim sSearchText As String
        Dim sReplaceText As String
        sSearchText = oSearchReplaceSheet.getCellByPosition(nSearchColumn - 1, i).getString()
        sReplaceText = oSearchReplaceSheet.getCellByPosition(nReplaceColumn - 1, i).getString()

        ' Create search and replace descriptors
        oSearchDescriptor = oDoc.createSearchDescriptor()
        oSearchDescriptor.SearchString = sSearchText

        oReplaceDescriptor = oDoc.createReplaceDescriptor()
        oReplaceDescriptor.ReplaceString = sReplaceText

        ' Execute the search
        oFound = oDoc.createReplaceDescriptor()
        oFound = oDoc.findFirst(oSearchDescriptor)

        ' Replace each occurrence found
        Do While Not IsNull(oFound)
            oCell = oFound.Cell
            oCell.setString(sReplaceText)

            ' Copy formatting from replace cell to the found cell
            CopyCellFormatting(oCell, oFound.Cell)

            ' Find the next occurrence
            oFound = oDoc.findNext(oFound)
        Loop
    Next i

    ' Close the search and replace document
    oSearchReplaceDoc.Close(True)
End Sub

Sub CopyCellFormatting(oSourceCell, oTargetCell)
    ' Copy character formatting
    oTargetCell.CharFontName = oSourceCell.CharFontName
    oTargetCell.CharHeight = oSourceCell.CharHeight
    oTargetCell.CharWeight = oSourceCell.CharWeight
    oFound.Cell.CharAutoKerning = oCell.CharAutoKerning
    oFound.Cell.CharKerning = oCell.CharKerning
    oFound.Cell.CharScaleWidth = oCell.CharScaleWidth

    ' Copy border formatting
    oTargetCell.BottomBorder = oSourceCell.BottomBorder
    oTargetCell.TopBorder = oSourceCell.TopBorder
    oTargetCell.LeftBorder = oSourceCell.LeftBorder
    oTargetCell.RightBorder = oSourceCell.RightBorder

End Sub

当我运行宏时出现错误

基本运行时错误。未找到属性或方法：createSearchDescriptor。

发生在宏的第 50 行，即：

oSearchDescriptor = oDoc.createSearchDescriptor()

我理解对象 oDoc（调用宏的文档）没有名为 createSearchDescriptor 的属性或方法，但我不知道足够的 LibreOffice Basic 来确定它应该在这里做什么，以便它能够工作。我浏览了 Andrew Pitonyak 的书“OpenOffice.org 的有用宏信息”和他的“OpenOffice.org 宏解释”书，但没有在其中找到任何有关使用 SearchDescriptors 的内容。

我怀疑如果第 53 行的语句会发生类似的错误

oReplaceDescriptor = oDoc.createReplaceDescriptor()

被处决。

如果此代码中存在其他错误导致其无法成功运行，我不会感到惊讶。

如果您能提供有关如何通过此宏实现我想要实现的目标的任何见解，我将不胜感激。

macros