LibreOffice宏插入新工作表报错：已创建表但无法复制数据求助

你的宏代码在创建新工作表后报错，核心问题出在新工作表的索引获取错误，加上未声明变量、错误处理变量缺失等细节问题，导致后续流程中断。以下是具体修复步骤和修正后的代码：

关键问题点 #

• positions变量未显式声明，隐式声明可能引发类型不匹配
• 新工作表插入位置为positions（原工作表总数），因此新表的索引应为positions，而非代码中的positions-1（这指向的是原最后一张表）
• 错误处理块中使用了未声明的ErrMsg变量，导致报错时弹出提示框也会出错
• 获取新表时混用ThisComponent和oTargetDoc，建议统一使用已定义的oTargetDoc对象

修正后的完整代码 #

Sub ImportSheetFromAnotherWorkbook2
    ' Declare variables
    Dim oSourceDoc As Object
    Dim oTargetDoc As Object
    Dim oSourceSheet As Object
    Dim oTargetSheet As Object
    Dim sourceFilePath As String
    Dim newSheetName As String
    Dim dataArray As Variant
    Dim positions As Integer ' 显式声明变量

    ' Specify the correct path to the source workbook
    sourceFilePath = "file:///C:/Users/chakri/Desktop/New Folder/SPI.ods" ' Update this path

    ' Load the source workbook
    On Error GoTo LoadError
    oSourceDoc = StarDesktop.loadComponentFromURL(sourceFilePath, "_blank", 0, Array())
    
    MsgBox "Source Name: " & oSourceDoc.Title
    
    ' Get the first sheet from the source workbook
    oSourceSheet = oSourceDoc.Sheets(0)

    ' Get the current document (target workbook)
    oTargetDoc = ThisComponent
    MsgBox "Current Workbook Name: " & oTargetDoc.Title

    ' Set the desired name for the new sheet
    newSheetName = "SPI_" & Format(Now(), "YYYYMMDD_HHMMSS") ' Add a timestamp
    
    ' Insert a new sheet at the end of the workbook
    positions = oTargetDoc.Sheets.getCount()
    On Error GoTo SheetError
    oTargetDoc.Sheets.insertNewByName(newSheetName, positions)
    ' 获取新创建的工作表（索引为positions，即插入的位置）
    oTargetSheet = oTargetDoc.Sheets(positions)
    MsgBox "Loaded fine"
    ' Copy data from the source sheet to the target sheet
    dataArray = oSourceSheet.getDataArray() ' Get the data array from the source sheet
    oTargetSheet.setDataArray(dataArray)     ' Set the data in the target sheet
    
    ' Close the source workbook without saving changes
    oSourceDoc.close(True)

    MsgBox "Data imported successfully!"

    Exit Sub

LoadError:
    MsgBox "Error loading source document: " & Err & " - " & Error$ ' 使用Error$获取错误描述
    Exit Sub

SheetError:
    MsgBox "Error inserting new sheet or accessing it: " & Err & " - " & Error$ ' 使用Error$替代未定义的ErrMsg
    If Not IsNull(oSourceDoc) Then oSourceDoc.close(True) ' Ensure source is closed on error
    Exit Sub
End Sub

主要修改说明 #

1. 显式声明positions变量：避免隐式声明带来的类型问题
2. 修正新工作表索引：将oTargetSheet = ThisComponent.Sheets(positions - 1)改为oTargetSheet = oTargetDoc.Sheets(positions)，确保获取到刚创建的新表
3. 修复错误处理中的描述变量：用Error$函数替代未定义的ErrMsg，正确获取错误信息
4. 统一对象引用：获取新表时使用已声明的oTargetDoc而非ThisComponent，保持上下文一致

内容的提问来源于stack exchange，提问作者bigstreet