Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel error when selected large files

Posted on 2016-08-19
9
Medium Priority
?
82 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 19

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 19

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 19

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
 
LVL 19

Accepted Solution

by:
xtermie earned 2000 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 19

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 19

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

606 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