pavilion pavilion - 3 months ago 32
Vb.net Question

Excel Interop worksheet HRESULT: 0x800A03EC using VB.net

so in my vb.net application, I am pulling data (about 1046 rows in the table) from database to pre-load the contents in excel spreadsheet. This works fine until 999 rows, but if it exceeds more than 999 rows it gives me that particular error.
Just wondering if there is a restriction. Any ideas? I am using windows 10, management studio 2012 and excel 2007.

Code:

Dim excelApp As New Excel.Application
Dim excelWorkBook As Excel.Workbook
Dim excelWorkSheet As Excel.Worksheet

Private Sub readEmployee(ByVal from As Date, ByVal to As Date, ByVal employee As data.employeeList)

excelApp.Application.Interactive = False

Dim filePath As String = 'c:.....'
excelWorkBook = excelApp.Workbooks.Open(filePath)
excelWorkSheet = CType(excelWorkBook.Sheets().Item(3), Excel.Worksheet)

Dim startRow As Integer = 9
Dim firstNames As String = ""
Dim lastNames As String = ""

With excelWorkSheet
startRow = 9
Dim row As data.employeeList.employeeListRow
For Each row In employee.employeeList
If row.RowState <> DataRowState.Deleted Then

firstNames = CStr(IIf(row.FirstName.Trim() = "", "", row.FirstName.Trim()))
lastNames = CStr(IIf(row.LastName.Trim() = "", "", row.LastName.Trim()))

.Range("A" + startRow.ToString("n0")).Value = lastNames
.Range("B" + startRow.ToString("n0")).Value = firstNames
startRow += 1
End If
Next
End With

excelApp.Application.Interactive = True
excelApp.Visible = True
End Sub

Answer

when startRow=1000,

startRow.ToString("n0") returns 1,000. That's incorrect format for Range parameter.

You don't need to use, FormatProvider for ToString here. Just use the default overload.

startRow.ToString()

is all you need.