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(0x800A 03EC): Exception from
HRESULT: 0x800A03EC.
at Microsoft.Office.Interop.Excel._Worksheet.Paste(Ob ject 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 '''''''''''''''''' 3 9873
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(0x800A 03EC): Exception from HRESULT: 0x800A03EC. at Microsoft.Office.Interop.Excel._Worksheet.Paste(Ob ject 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 ''''''''''''''''''
<Eric> wrote in message news:eW**************@TK2MSFTNGP10.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(0x800A 03EC): Exception from HRESULT: 0x800A03EC. at Microsoft.Office.Interop.Excel._Worksheet.Paste(Ob ject 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 ''''''''''''''''''
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**********************************@microsof t.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(0x800A 03EC): Exception from HRESULT: 0x800A03EC. at Microsoft.Office.Interop.Excel._Worksheet.Paste(Ob ject
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 '''''''''''''''''' This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rajiv |
last post by:
HI ALL,
actually , i m working in a company having large number of employee , i have
two userid and password .
on id is from admin group(id given to me by technology manager) of the
server and...
|
by: pmud |
last post by:
Hi,
I need to use an Excel Sheet in ASP.NET application so that the users can
enter (copy, paste ) large number of rows in this Excel Sheet. Also, Whatever
the USER ENETRS needs to go to the...
|
by: TM |
last post by:
Is there any way that I can take the records from a datagrid and paste them
into an Excel sheet, then print preview or print the sheet ?
Is there any way I can sort the sheet before I print it ?...
|
by: surekhads |
last post by:
Hi all,
I have developed a project for extracting data from a HTML file and export that data to a excel sheet. I am storing that extracted data to the db. And then I am exporting that data...
|
by: imtmub |
last post by:
Hi All,
when i am trying to insert records from excel sheet to wko table in my database. My Excel sheet contains 400 records. I am getting the error message. please check this code and advise me. ...
|
by: ammoos |
last post by:
Hi Friends
I am getting the following error when I am trying to read data from an excel sheet using sql script
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not...
|
by: ammoos |
last post by:
Hi Friends,
I need to read data from an excel sheet. I am keeping this excel sheet in a remote machine. I am using OLEDB connection to read the data from this excel sheet. But when I am trying to...
|
by: kirubagari |
last post by:
I would like to duplicate the numbers from from excel sheet 1 to excel sheet 2.Kindly help me on this.Sometime its unable to duplicate..
Sub Duplicate
Dim oDoc As Object, oSheet As Object,...
|
by: kirubagari |
last post by:
Hai experts,
How to duplicate the data from 1 excel sheet to another excel sheet 2.
Lets say
Name Voucher Value Voucher Number
lee 300.00 ...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |