Excel error when selected large files

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
RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xtermieCommented:
Try to:
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.
0
RIASAuthor Commented:
No luck mate!
0
xtermieCommented:
In your code you have a count starting from 0 (as you would in a C# code).
Excel counting starts at 1.
Try changing that
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

xtermieCommented:
Also the maximum capacity of excel is Worksheet size 1,048,576 rows by 16,384 columns
This could be the issue in your case, if you are trying to export more than that rows.
Refer below link for details:
http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/with-excel-2013how-many-rows-will-this-contain/271264fb-3ab8-4c5b-aa0d-7095c5ac6108
0
xtermieCommented:
Also if you are using Range.Value to copy or move data, in some cases to it may be useful to use Range.Value2 instead as this causes Excel to bypass formatting the cells , which may cause this exception you are having
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xtermieCommented:
...can't think of anything else (for now) :)
0
RIASAuthor Commented:
Cheers mate !Will try and get back
0
RIASAuthor Commented:
Mate you are Genius! It worked!
0
xtermieCommented:
Cool!  So glad I could help :)
Take care
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.