excel - 在 SSIS 中运行脚本以修改 excel 文件时出现错误 :The server threw an exception.(来自 HRESULT 的异常:0x80010105 (RPC_E_SERVERFAULT))

Question

当我尝试运行 SSIS 脚本来更新 excel 文件时，它总是在任何修改时出错或保存读取文件是可以的，这是在 Visual Studio 2005 中完成并在服务器上运行。

有任何想法吗。

excel Interop 的版本是:Microsoft.Office.Interop.Excel - 11.0.0.0

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Microsoft.office.interop.excel.dll

脚本代码:

    Dim oExcel As Excel.Application
    Dim oWorkBook As Excel.Workbook
    Dim oWorkSheets As Excel.Sheets
    Dim oWSheet As Excel.Worksheet
    Dim oRng As Excel.Range
    Dim oCell As Excel.Range

    Dim Temp As String
    Dim startCol As String
    Dim startRow As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim i As Long

    Dim myfile As String
    Dim myPath As String


    'SET oExcel As Excel.Application
    oExcel = CreateObject("Excel.Application")

    'DISABLE EXCEL WARNINGS
    oExcel.Visible = False
    oExcel.DisplayAlerts = False
    oExcel.AskToUpdateLinks = False
    oExcel.AlertBeforeOverwriting = False

    myPath = "\\Network Path\Testing\"
    myfile = "EIV Temp File - Small.xls"

    oWorkBook = oExcel.Workbooks.Open(myPath & myfile)

    With oWorkbook
        oWSheet = CType(oWorkBook.Sheets(1), Excel.Worksheet)

        startCol = "A"
        startRow = 1
        lastRow = oWSheet.Range(startCol & oWSheet.Rows.Count).End(XlDirection.xlUp).Row
        lastCol = oWSheet.Cells(2, oWSheet.Columns.Count).End(XlDirection.xlToLeft).Column + 1

        '  oRng = oWSheet.Range("I2" & ":" & "I" & lastRow)

        ' For Each oCell In oRng
        ' Temp = "'" + oCell.Value
        ' oCell.Value = Temp
        ' Next oCell

        Try
            With oWSheet
                For i = 2 To lastRow
                    Temp = "'" + .Cells(i, 9).value
                    .Cells(i, lastCol).value = Temp ' Errors at this line
                Next i
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        .Save() ' Errors at this line
        .Close(SaveChanges:=False)
    End With

异常(exception)

Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)

Answer 1

我尝试了您的代码，以下行引发了异常:

Temp = "'" + .Cells(i, 9).value

因为您使用的是 +运算符连接字符串，而如果单元格的值不是字符串，则会抛出异常。尝试以下语法。

Temp = "'" & .Cells(i, 9).value

您也可以删除 .Save行和使用 .Close(SaveChanges:=True)
边注:

脚本执行后，excel将在后台保持打开状态，您需要添加以下行:

Marshal.ReleaseComObject(owsheet)
Marshal.ReleaseComObject(oWorkbook)
Marshal.ReleaseComObject(oExcel)