Pasting to Excel sheet (getting desperate!)

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(0 x800A03EC): Exception from
HRESULT: 0x800A03EC.
at Microsoft.Offic e.Interop.Excel ._Worksheet.Pas te(Object Destination,
Object Link)
at myProject.Excel Writer.ExportTo Excel(DataTable & tbl)
at myProject.frmMa in.btnExportToE xcel_Click(Obje ct 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.Offic e.Interop

Public Class ExcelWriter

Public Shared Sub ExportToExcel(B yRef 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.Len gth < 1 Then ReportTitle = "New Report"
sb.Append(Repor tTitle & vbNewLine & vbNewLine)

' Copy column headers.
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Co lumns.Item(col) .ColumnName) Then
sb.Append(tbl.C olumns.Item(col ).ColumnName)
End If
sb.Append(vbTab )
Application.DoE vents()
Next
sb.Append(vbNew Line)

' Copy rows.
For row = 0 To tbl.Rows.Count - 1
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Ro ws(row)(col)) Then
If TypeOf tbl.Rows(row)(c ol) Is DateTime Then
Dim d As DateTime = tbl.Rows(row)(c ol)
sb.Append(d.ToS hortDateString)
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)(c ol).ToString
rawString = rawString.Repla ce(vbTab, " "c)
rawString = rawString.Repla ce(N, " "c)
rawString = rawString.Repla ce(NewLine, " "c)
sb.Append(tbl.R ows(row)(col))
End If
End If
sb.Append(vbTab )
Application.DoE vents()
Next
sb.Append(vbNew Line)
Next

Clipboard.SetDa taObject(sb.ToS tring)

' Create Excel Objects
Dim ExcelApp As Excel.Applicati on
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
Dim Range As Excel.Range

' Start Excel and get Application object:
ExcelApp = CreateObject("E xcel.Applicatio n")
ExcelApp.Visibl e = False

' Add a new workbook
Book = ExcelApp.Workbo oks.Add
Sheet = Book.ActiveShee t
' NOTE: Excel worksheet name max length = 31.
Sheet.Name = Left(ReportTitl e, 31)
Application.DoE vents()

''''''''''''''' ''' 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.EntireCol umn.AutoFit()

' Format title.
Range = Sheet.Cells(1, 1)
Range.Font.Bold = True
Range.Font.Size = 14
Application.DoE vents()

' Add date/time created.
Range = Sheet.Cells(2, tbl.Columns.Cou nt)
Range.Value = "Report Created: " & Now.ToString
Range.Font.Size = 8
Range.Horizonta lAlignment = Excel.XlHAlign. xlHAlignRight

' Center title across selection.
Dim cellStart As Excel.Range = ExcelApp.Range( "A1")
Dim cellEnd As Excel.Range = _
DirectCast(Shee t.Cells(1, _
tbl.Columns.Cou nt), Excel.Range)
Dim rng As Excel.Range = _
ExcelApp.Range( cellStart, cellEnd)
rng.Merge()
rng.HorizontalA lignment = _
Excel.XlHAlign. xlHAlignCenterA crossSelection
Application.DoE vents()

ExcelApp.Visibl e = True
End Sub
End Class

''''''''''''''' ''' END CODE ''''''''''''''' '''

[Alien2_51]

Just a suggestion, you could remove the dependency on having Excel installed
on the machine you run your program and eleminate the possibility in
differences in the API versions by formatting your data from your datatable
into an html table, then just save the string as .xls, Excel will interpret
the html correctly when the file is opened.

"Eric" wrote:

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(0 x800A03EC): Exception from
HRESULT: 0x800A03EC.
at Microsoft.Offic e.Interop.Excel ._Worksheet.Pas te(Object Destination,
Object Link)
at myProject.Excel Writer.ExportTo Excel(DataTable & tbl)
at myProject.frmMa in.btnExportToE xcel_Click(Obje ct 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.Offic e.Interop

Public Class ExcelWriter

Public Shared Sub ExportToExcel(B yRef 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.Len gth < 1 Then ReportTitle = "New Report"
sb.Append(Repor tTitle & vbNewLine & vbNewLine)

' Copy column headers.
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Co lumns.Item(col) .ColumnName) Then
sb.Append(tbl.C olumns.Item(col ).ColumnName)
End If
sb.Append(vbTab )
Application.DoE vents()
Next
sb.Append(vbNew Line)

' Copy rows.
For row = 0 To tbl.Rows.Count - 1
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Ro ws(row)(col)) Then
If TypeOf tbl.Rows(row)(c ol) Is DateTime Then
Dim d As DateTime = tbl.Rows(row)(c ol)
sb.Append(d.ToS hortDateString)
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)(c ol).ToString
rawString = rawString.Repla ce(vbTab, " "c)
rawString = rawString.Repla ce(N, " "c)
rawString = rawString.Repla ce(NewLine, " "c)
sb.Append(tbl.R ows(row)(col))
End If
End If
sb.Append(vbTab )
Application.DoE vents()
Next
sb.Append(vbNew Line)
Next

Clipboard.SetDa taObject(sb.ToS tring)

' Create Excel Objects
Dim ExcelApp As Excel.Applicati on
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
Dim Range As Excel.Range

' Start Excel and get Application object:
ExcelApp = CreateObject("E xcel.Applicatio n")
ExcelApp.Visibl e = False

' Add a new workbook
Book = ExcelApp.Workbo oks.Add
Sheet = Book.ActiveShee t
' NOTE: Excel worksheet name max length = 31.
Sheet.Name = Left(ReportTitl e, 31)
Application.DoE vents()

''''''''''''''' ''' 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.EntireCol umn.AutoFit()

' Format title.
Range = Sheet.Cells(1, 1)
Range.Font.Bold = True
Range.Font.Size = 14
Application.DoE vents()

' Add date/time created.
Range = Sheet.Cells(2, tbl.Columns.Cou nt)
Range.Value = "Report Created: " & Now.ToString
Range.Font.Size = 8
Range.Horizonta lAlignment = Excel.XlHAlign. xlHAlignRight

' Center title across selection.
Dim cellStart As Excel.Range = ExcelApp.Range( "A1")
Dim cellEnd As Excel.Range = _
DirectCast(Shee t.Cells(1, _
tbl.Columns.Cou nt), Excel.Range)
Dim rng As Excel.Range = _
ExcelApp.Range( cellStart, cellEnd)
rng.Merge()
rng.HorizontalA lignment = _
Excel.XlHAlign. xlHAlignCenterA crossSelection
Application.DoE vents()

ExcelApp.Visibl e = True
End Sub
End Class

''''''''''''''' ''' END CODE ''''''''''''''' '''

[Fredrik Wahlgren]

<Eric> wrote in message news:eW******** ******@TK2MSFTN GP10.phx.gbl...

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(0 x800A03EC): Exception from
HRESULT: 0x800A03EC.
at Microsoft.Offic e.Interop.Excel ._Worksheet.Pas te(Object Destination,
Object Link)
at myProject.Excel Writer.ExportTo Excel(DataTable & tbl)
at myProject.frmMa in.btnExportToE xcel_Click(Obje ct 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.Offic e.Interop

Public Class ExcelWriter

Public Shared Sub ExportToExcel(B yRef 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.Len gth < 1 Then ReportTitle = "New Report"
sb.Append(Repor tTitle & vbNewLine & vbNewLine)

' Copy column headers.
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Co lumns.Item(col) .ColumnName) Then
sb.Append(tbl.C olumns.Item(col ).ColumnName)
End If
sb.Append(vbTab )
Application.DoE vents()
Next
sb.Append(vbNew Line)

' Copy rows.
For row = 0 To tbl.Rows.Count - 1
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Ro ws(row)(col)) Then
If TypeOf tbl.Rows(row)(c ol) Is DateTime Then
Dim d As DateTime = tbl.Rows(row)(c ol)
sb.Append(d.ToS hortDateString)
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)(c ol).ToString
rawString = rawString.Repla ce(vbTab, " "c)
rawString = rawString.Repla ce(N, " "c)
rawString = rawString.Repla ce(NewLine, " "c)
sb.Append(tbl.R ows(row)(col))
End If
End If
sb.Append(vbTab )
Application.DoE vents()
Next
sb.Append(vbNew Line)
Next

Clipboard.SetDa taObject(sb.ToS tring)

' Create Excel Objects
Dim ExcelApp As Excel.Applicati on
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
Dim Range As Excel.Range

' Start Excel and get Application object:
ExcelApp = CreateObject("E xcel.Applicatio n")
ExcelApp.Visibl e = False

' Add a new workbook
Book = ExcelApp.Workbo oks.Add
Sheet = Book.ActiveShee t
' NOTE: Excel worksheet name max length = 31.
Sheet.Name = Left(ReportTitl e, 31)
Application.DoE vents()

''''''''''''''' ''' 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.EntireCol umn.AutoFit()

' Format title.
Range = Sheet.Cells(1, 1)
Range.Font.Bold = True
Range.Font.Size = 14
Application.DoE vents()

' Add date/time created.
Range = Sheet.Cells(2, tbl.Columns.Cou nt)
Range.Value = "Report Created: " & Now.ToString
Range.Font.Size = 8
Range.Horizonta lAlignment = Excel.XlHAlign. xlHAlignRight

' Center title across selection.
Dim cellStart As Excel.Range = ExcelApp.Range( "A1")
Dim cellEnd As Excel.Range = _
DirectCast(Shee t.Cells(1, _
tbl.Columns.Cou nt), Excel.Range)
Dim rng As Excel.Range = _
ExcelApp.Range( cellStart, cellEnd)
rng.Merge()
rng.HorizontalA lignment = _
Excel.XlHAlign. xlHAlignCenterA crossSelection
Application.DoE vents()

ExcelApp.Visibl e = True
End Sub
End Class

''''''''''''''' ''' END CODE ''''''''''''''' '''

Start the task manager an select the processes tab. Click on "Image name"
and make sure there's no excel.exe ghost process. Or simply restar the
computer. What's in the datagrid? Does it make any difference if the user
tries to copy something like a single row?

/Fredrik

Thank you so much, Dan! You are truly brilliant. I coded up your idea this
morning, and it works perfectly. It's even much faster than using Interop.

I've had this issue hanging over my head for more than a year, and you saved
my hide. If I had extra money to give, I'd cut you a big fat check. :-)

Eric
"Alien2_51" <da************ ***@monacocoach .removeme.com> wrote in message
news:A6******** *************** ***********@mic rosoft.com...

Just a suggestion, you could remove the dependency on having Excel installed on the machine you run your program and eleminate the possibility in
differences in the API versions by formatting your data from your datatable into an html table, then just save the string as .xls, Excel will interpret the html correctly when the file is opened.

"Eric" wrote:

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(0 x800A03EC): Exception from
HRESULT: 0x800A03EC.
at Microsoft.Offic e.Interop.Excel ._Worksheet.Pas te(Object Destination, Object Link)
at myProject.Excel Writer.ExportTo Excel(DataTable & tbl)
at myProject.frmMa in.btnExportToE xcel_Click(Obje ct 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.Offic e.Interop

Public Class ExcelWriter

Public Shared Sub ExportToExcel(B yRef 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.Len gth < 1 Then ReportTitle = "New Report"
sb.Append(Repor tTitle & vbNewLine & vbNewLine)

' Copy column headers.
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Co lumns.Item(col) .ColumnName) Then
sb.Append(tbl.C olumns.Item(col ).ColumnName)
End If
sb.Append(vbTab )
Application.DoE vents()
Next
sb.Append(vbNew Line)

' Copy rows.
For row = 0 To tbl.Rows.Count - 1
For col = 0 To tbl.Columns.Cou nt - 1
If Not IsDBNull(tbl.Ro ws(row)(col)) Then
If TypeOf tbl.Rows(row)(c ol) Is DateTime Then
Dim d As DateTime = tbl.Rows(row)(c ol)
sb.Append(d.ToS hortDateString)
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)(c ol).ToString
rawString = rawString.Repla ce(vbTab, " "c)
rawString = rawString.Repla ce(N, " "c)
rawString = rawString.Repla ce(NewLine, " "c)
sb.Append(tbl.R ows(row)(col))
End If
End If
sb.Append(vbTab )
Application.DoE vents()
Next
sb.Append(vbNew Line)
Next

Clipboard.SetDa taObject(sb.ToS tring)

' Create Excel Objects
Dim ExcelApp As Excel.Applicati on
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
Dim Range As Excel.Range

' Start Excel and get Application object:
ExcelApp = CreateObject("E xcel.Applicatio n")
ExcelApp.Visibl e = False

' Add a new workbook
Book = ExcelApp.Workbo oks.Add
Sheet = Book.ActiveShee t
' NOTE: Excel worksheet name max length = 31.
Sheet.Name = Left(ReportTitl e, 31)
Application.DoE vents()

''''''''''''''' ''' 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.EntireCol umn.AutoFit()

' Format title.
Range = Sheet.Cells(1, 1)
Range.Font.Bold = True
Range.Font.Size = 14
Application.DoE vents()

' Add date/time created.
Range = Sheet.Cells(2, tbl.Columns.Cou nt)
Range.Value = "Report Created: " & Now.ToString
Range.Font.Size = 8
Range.Horizonta lAlignment = Excel.XlHAlign. xlHAlignRight

' Center title across selection.
Dim cellStart As Excel.Range = ExcelApp.Range( "A1")
Dim cellEnd As Excel.Range = _
DirectCast(Shee t.Cells(1, _
tbl.Columns.Cou nt), Excel.Range)
Dim rng As Excel.Range = _
ExcelApp.Range( cellStart, cellEnd)
rng.Merge()
rng.HorizontalA lignment = _
Excel.XlHAlign. xlHAlignCenterA crossSelection
Application.DoE vents()

ExcelApp.Visibl e = True
End Sub
End Class

''''''''''''''' ''' END CODE ''''''''''''''' '''