在SSIS中运行脚本以修改excel文件时出错:服务器引发异常.(HRESULT出现异常:0x80010105(RPC_

Question

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

任何想法。

excel Interop的版本为：Microsoft.Office.Interop.excel-11.0.0.0

C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Microsoft.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 PathTesting"
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

异常

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)