Avatar of RIAS
RIAS
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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
.NET ProgrammingMicrosoft ExcelVisual Basic.NET

Avatar of undefined
Last Comment
Anastasia D. Gavanas

8/22/2022 - Mon
Anastasia D. Gavanas

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.
RIAS

ASKER
No luck mate!
Anastasia D. Gavanas

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Anastasia D. Gavanas

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
ASKER CERTIFIED SOLUTION
Anastasia D. Gavanas

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Anastasia D. Gavanas

...can't think of anything else (for now) :)
RIAS

ASKER
Cheers mate !Will try and get back
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RIAS

ASKER
Mate you are Genius! It worked!
Anastasia D. Gavanas

Cool!  So glad I could help :)
Take care