Solved

Excel error when selected large files

Posted on 2016-08-19
9
43 Views
Last Modified: 2016-08-19
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
0
Comment
Question by:RIAS
  • 6
  • 3
9 Comments
 
LVL 17

Expert Comment

by:xtermie
ID: 41762334
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
 

Author Comment

by:RIAS
ID: 41762392
No luck mate!
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41762405
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
 
LVL 17

Expert Comment

by:xtermie
ID: 41762410
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

Accepted Solution

by:
xtermie earned 500 total points
ID: 41762413
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
 
LVL 17

Expert Comment

by:xtermie
ID: 41762414
...can't think of anything else (for now) :)
0
 

Author Comment

by:RIAS
ID: 41762427
Cheers mate !Will try and get back
0
 

Author Closing Comment

by:RIAS
ID: 41762434
Mate you are Genius! It worked!
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41762468
Cool!  So glad I could help :)
Take care
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now