RIAS
asked on
Excel error when selected large files
FlgExportExcelSuccess = ClsFrmMain.ExportToExcel(i ntTabnumbe r, 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(FileP ath)
xlWorkBook.Worksheets.Add( After:=xlW orkBook.Wo rksheets(x lWorkBook. Worksheets .Count))
xlWorkSheet = xlWorkBook.Worksheets(xlWo rkBook.Wor ksheets.Co unt)
xlWorkSheet.Name = StrSheetname
If System.IO.File.Exists(File Path) Then
System.IO.File.Delete(File Path)
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).Colu mnName
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(i Row).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(xlWorkSh eet.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.Ac tiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
xlWorkSheet.Application.Ac tiveWindow .FreezePan es = 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
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(
End If
xlWorkBook = xlApp.Workbooks.Open(FileP
xlWorkBook.Worksheets.Add(
xlWorkSheet = xlWorkBook.Worksheets(xlWo
xlWorkSheet.Name = StrSheetname
If System.IO.File.Exists(File
System.IO.File.Delete(File
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).Colu
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(i
objXlData(iRow, iCol) = dtGridData.Rows(iRow).Item
Else
objXlData(iRow, iCol) = ""
End If
Next
Next
Dim xlRange As Excel.Range = xlWorkSheet.Range("A1")
xlRange = xlRange.Resize(dtRowCount,
xlRange.Value = objXlColHeaderData
xlWorkSheet.Range(xlWorkSh
xlRange = xlWorkSheet.Range("A2")
xlRange = xlRange.Resize(dtRowCount,
xlRange.Value = objXlData -----error here
With xlWorkSheet
.Range(.Cells(1, 1), .Cells(1, 1)).Select()
End With
With xlWorkSheet.Application.Ac
.SplitColumn = 0
.SplitRow = 1
End With
xlWorkSheet.Application.Ac
Application.DoEvents()
xlWorkBook.SaveAs(FilePath
FrmMain.Cursor = Cursors.Default
Return True
Catch ex As Exception
MessageBox.Show(ex.Message
xlWorkSheet = Nothing
'xlWorkBook.Close(0)
xlWorkBook = Nothing
'xlApp.Quit()
xlApp = Nothing
FrmMain.Cursor = Cursors.Default
End Try
End Function
Error:
exception from hresult 0x800a03ec
ASKER
No luck mate!
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
Excel counting starts at 1.
Try changing that
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...can't think of anything else (for now) :)
ASKER
Cheers mate !Will try and get back
ASKER
Mate you are Genius! It worked!
Cool! So glad I could help :)
Take care
Take care
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.