Solved

Excel error when selected large files

Posted on 2016-08-19
9
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 18

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 18

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

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 18

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 18

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 18

Expert Comment

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

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

739 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