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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.