G
Guest
I wrote a class in VB.NET to export the contents of a datagrid to Excel. It
works perfectly on my machine, but it fails on my customers' PCs that have
identical versions of Win XP (SP1) and Excel (SP1) installed.
The error is:
System.Runtime.InteropServices.COMException(0x800A03EC): Exception from
HRESULT: 0x800A03EC.
at Microsoft.Office.Interop.Excel._Worksheet.Paste(Object Destination,
Object Link)
at myProject.ExcelWriter.ExportToExcel(DataTable& tbl)
at myProject.frmMain.btnExportToExcel_Click(Object sender, EventArgs e)
My complete code for the class is below. Does anyone know what the problem
with the Paste() statement might be?
Thank you!
Eric
'''''''''''''''''' BEGIN CODE ''''''''''''''''''
Option Explicit On
Imports System.Text
Imports Microsoft.Office.Interop
Public Class ExcelWriter
Public Shared Sub ExportToExcel(ByRef tbl As DataTable)
' This routine copies the contents of a data table, named "dt"
(declared
' Private within this Public class), to the Windows Clipboard in a
tab-
' delimited format. It then creates an Excel spreadsheet and pastes
the
' contents of the Clipboard to the spreadsheet.
If tbl Is Nothing Then Exit Sub
Dim sb As New StringBuilder
Dim row, col As Integer
' Add the title.
Dim ReportTitle As String = tbl.TableName
If ReportTitle.Length < 1 Then ReportTitle = "New Report"
sb.Append(ReportTitle & vbNewLine & vbNewLine)
' Copy column headers.
For col = 0 To tbl.Columns.Count - 1
If Not IsDBNull(tbl.Columns.Item(col).ColumnName) Then
sb.Append(tbl.Columns.Item(col).ColumnName)
End If
sb.Append(vbTab)
Application.DoEvents()
Next
sb.Append(vbNewLine)
' Copy rows.
For row = 0 To tbl.Rows.Count - 1
For col = 0 To tbl.Columns.Count - 1
If Not IsDBNull(tbl.Rows(row)(col)) Then
If TypeOf tbl.Rows(row)(col) Is DateTime Then
Dim d As DateTime = tbl.Rows(row)(col)
sb.Append(d.ToShortDateString)
Else
Dim NewLine As Char = ChrW(32)
If tbl.Rows(row)(0).ToString = "68" AndAlso _
tbl.Columns(col).ColumnName = "Comments" Then
Dim iii As Integer = 0
End If
' Account for tab and newline chars in string.
Dim rawString As String =
tbl.Rows(row)(col).ToString
rawString = rawString.Replace(vbTab, " "c)
rawString = rawString.Replace(N, " "c)
rawString = rawString.Replace(NewLine, " "c)
sb.Append(tbl.Rows(row)(col))
End If
End If
sb.Append(vbTab)
Application.DoEvents()
Next
sb.Append(vbNewLine)
Next
Clipboard.SetDataObject(sb.ToString)
' Create Excel Objects
Dim ExcelApp As Excel.Application
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
Dim Range As Excel.Range
' Start Excel and get Application object:
ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False
' Add a new workbook
Book = ExcelApp.Workbooks.Add
Sheet = Book.ActiveSheet
' NOTE: Excel worksheet name max length = 31.
Sheet.Name = Left(ReportTitle, 31)
Application.DoEvents()
'''''''''''''''''' ERROR OCCURS HERE ''''''''''''''''''
' Paste the Clipboard contents.
Sheet.Paste()
' Format column headers.
Range = Sheet.Rows(3)
Range.Font.Bold = True
' AutoFit Columns
Range = Sheet.Range("A1", "IA1")
Range.EntireColumn.AutoFit()
' Format title.
Range = Sheet.Cells(1, 1)
Range.Font.Bold = True
Range.Font.Size = 14
Application.DoEvents()
' Add date/time created.
Range = Sheet.Cells(2, tbl.Columns.Count)
Range.Value = "Report Created: " & Now.ToString
Range.Font.Size = 8
Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
' Center title across selection.
Dim cellStart As Excel.Range = ExcelApp.Range("A1")
Dim cellEnd As Excel.Range = _
DirectCast(Sheet.Cells(1, _
tbl.Columns.Count), Excel.Range)
Dim rng As Excel.Range = _
ExcelApp.Range(cellStart, cellEnd)
rng.Merge()
rng.HorizontalAlignment = _
Excel.XlHAlign.xlHAlignCenterAcrossSelection
Application.DoEvents()
ExcelApp.Visible = True
End Sub
End Class
'''''''''''''''''' END CODE ''''''''''''''''''
works perfectly on my machine, but it fails on my customers' PCs that have
identical versions of Win XP (SP1) and Excel (SP1) installed.
The error is:
System.Runtime.InteropServices.COMException(0x800A03EC): Exception from
HRESULT: 0x800A03EC.
at Microsoft.Office.Interop.Excel._Worksheet.Paste(Object Destination,
Object Link)
at myProject.ExcelWriter.ExportToExcel(DataTable& tbl)
at myProject.frmMain.btnExportToExcel_Click(Object sender, EventArgs e)
My complete code for the class is below. Does anyone know what the problem
with the Paste() statement might be?
Thank you!
Eric
'''''''''''''''''' BEGIN CODE ''''''''''''''''''
Option Explicit On
Imports System.Text
Imports Microsoft.Office.Interop
Public Class ExcelWriter
Public Shared Sub ExportToExcel(ByRef tbl As DataTable)
' This routine copies the contents of a data table, named "dt"
(declared
' Private within this Public class), to the Windows Clipboard in a
tab-
' delimited format. It then creates an Excel spreadsheet and pastes
the
' contents of the Clipboard to the spreadsheet.
If tbl Is Nothing Then Exit Sub
Dim sb As New StringBuilder
Dim row, col As Integer
' Add the title.
Dim ReportTitle As String = tbl.TableName
If ReportTitle.Length < 1 Then ReportTitle = "New Report"
sb.Append(ReportTitle & vbNewLine & vbNewLine)
' Copy column headers.
For col = 0 To tbl.Columns.Count - 1
If Not IsDBNull(tbl.Columns.Item(col).ColumnName) Then
sb.Append(tbl.Columns.Item(col).ColumnName)
End If
sb.Append(vbTab)
Application.DoEvents()
Next
sb.Append(vbNewLine)
' Copy rows.
For row = 0 To tbl.Rows.Count - 1
For col = 0 To tbl.Columns.Count - 1
If Not IsDBNull(tbl.Rows(row)(col)) Then
If TypeOf tbl.Rows(row)(col) Is DateTime Then
Dim d As DateTime = tbl.Rows(row)(col)
sb.Append(d.ToShortDateString)
Else
Dim NewLine As Char = ChrW(32)
If tbl.Rows(row)(0).ToString = "68" AndAlso _
tbl.Columns(col).ColumnName = "Comments" Then
Dim iii As Integer = 0
End If
' Account for tab and newline chars in string.
Dim rawString As String =
tbl.Rows(row)(col).ToString
rawString = rawString.Replace(vbTab, " "c)
rawString = rawString.Replace(N, " "c)
rawString = rawString.Replace(NewLine, " "c)
sb.Append(tbl.Rows(row)(col))
End If
End If
sb.Append(vbTab)
Application.DoEvents()
Next
sb.Append(vbNewLine)
Next
Clipboard.SetDataObject(sb.ToString)
' Create Excel Objects
Dim ExcelApp As Excel.Application
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
Dim Range As Excel.Range
' Start Excel and get Application object:
ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False
' Add a new workbook
Book = ExcelApp.Workbooks.Add
Sheet = Book.ActiveSheet
' NOTE: Excel worksheet name max length = 31.
Sheet.Name = Left(ReportTitle, 31)
Application.DoEvents()
'''''''''''''''''' ERROR OCCURS HERE ''''''''''''''''''
' Paste the Clipboard contents.
Sheet.Paste()
' Format column headers.
Range = Sheet.Rows(3)
Range.Font.Bold = True
' AutoFit Columns
Range = Sheet.Range("A1", "IA1")
Range.EntireColumn.AutoFit()
' Format title.
Range = Sheet.Cells(1, 1)
Range.Font.Bold = True
Range.Font.Size = 14
Application.DoEvents()
' Add date/time created.
Range = Sheet.Cells(2, tbl.Columns.Count)
Range.Value = "Report Created: " & Now.ToString
Range.Font.Size = 8
Range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
' Center title across selection.
Dim cellStart As Excel.Range = ExcelApp.Range("A1")
Dim cellEnd As Excel.Range = _
DirectCast(Sheet.Cells(1, _
tbl.Columns.Count), Excel.Range)
Dim rng As Excel.Range = _
ExcelApp.Range(cellStart, cellEnd)
rng.Merge()
rng.HorizontalAlignment = _
Excel.XlHAlign.xlHAlignCenterAcrossSelection
Application.DoEvents()
ExcelApp.Visible = True
End Sub
End Class
'''''''''''''''''' END CODE ''''''''''''''''''