FlgExportExcelSuccess = ClsFrmMain.ExportToExcel(intTabnumber, dtSelectedRows, StrExcelLocation, "Diary" & intTabnumber, xlapp) = True
Function ExportToExcel(ByVal intCreateNew As Integer, ByVal dtGridData As DataTable, ByVal FilePath As String, ByVal StrSheetname As String, Optional ByVal xlApp As Excel.Application = Nothing) As Boolean
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Application.DoEvents()
Try
FrmMain.Cursor = Cursors.WaitCursor
xlApp.DisplayAlerts = False
If intCreateNew = 0 Then
xlApp.Workbooks.Add()
xlApp.Workbooks(1).SaveAs(FilePath)
End If
xlWorkBook = xlApp.Workbooks.Open(FilePath)
xlWorkBook.Worksheets.Add(After:=xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count))
xlWorkSheet = xlWorkBook.Worksheets(xlWorkBook.Worksheets.Count)
xlWorkSheet.Name = StrSheetname
If System.IO.File.Exists(FilePath) Then
System.IO.File.Delete(FilePath)
End If
Dim dtRowCount As Integer = dtGridData.Rows.Count
Dim dtColCount As Integer = dtGridData.Columns.Count
Dim objXlColHeaderData(1, dtGridData.Columns.Count) As Object
Application.DoEvents()
For i As Integer = 0 To dtColCount - 1
objXlColHeaderData(0, i) = dtGridData.Columns(i).ColumnName
Next
Dim objXlData(dtRowCount, dtColCount) As Object
For iRow As Integer = 0 To dtRowCount - 1
Application.DoEvents()
For iCol As Integer = 0 To dtColCount - 1
Application.DoEvents()
If Not IsDBNull(dtGridData.Rows(iRow).Item(iCol)) Then
objXlData(iRow, iCol) = dtGridData.Rows(iRow).Item(iCol)
Else
objXlData(iRow, iCol) = ""
End If
Next
Next
Dim xlRange As Excel.Range = xlWorkSheet.Range("A1")
xlRange = xlRange.Resize(dtRowCount, dtColCount)
xlRange.Value = objXlColHeaderData
xlWorkSheet.Range(xlWorkSheet.Cells(1, 1), xlWorkSheet.Cells(1, dtColCount)).Font.Bold = True
xlRange = xlWorkSheet.Range("A2")
xlRange = xlRange.Resize(dtRowCount, dtColCount)
xlRange.Value = objXlData -----error here
With xlWorkSheet
.Range(.Cells(1, 1), .Cells(1, 1)).Select()
End With
With xlWorkSheet.Application.ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
xlWorkSheet.Application.ActiveWindow.FreezePanes = True
Application.DoEvents()
xlWorkBook.SaveAs(FilePath)
FrmMain.Cursor = Cursors.Default
Return True
Catch ex As Exception
MessageBox.Show(ex.Message, "ErrorIn ExportToExcel", MessageBoxButtons.OK, MessageBoxIcon.Error)
xlWorkSheet = Nothing
'xlWorkBook.Close(0)
xlWorkBook = Nothing
'xlApp.Quit()
xlApp = Nothing
FrmMain.Cursor = Cursors.Default
End Try
End Function
Error:
exception from hresult 0x800a03ec
1) Dim objXlData(dtRowCount, dtColCount)
2) Check if you are using a backwards compatible sheet (a .xls) instead of a .xlsx and the row count is more than 65530
Also, on a similar case of hresult 0x800a03ec error the following steps provided a solution.
1. Login to the server as a administrator.
2.Go to "Start" -> "Run" and enter "taskmgr"
3.Go to the process tab in task manager and check "Show Processes from all users"
4.If there are any "Excel.exe" entries on the list, right click on the entry and select "End Process"
5.Close task manager.
6.Go to "Start" -> "Run" and enter "services.msc"
7.Stop the service automating Excel if it is running.
8.Go to "Start" -> "Run" and enter "dcomcnfg"
9.This will bring up the component services window, expand out "Console Root" -> "Computers" -> "DCOM Config"
10.Find "Microsoft Excel Application" in the list of components.
11.Right click on the entry and select "Properties"
12.Go to the "Identity" tab on the properties dialog.
13.Select "The interactive user."
14.Click the "OK" button.
15.Switch to the services console
16.Start the service automating Excel
17.Test you application again.