+ Reply to Thread
Results 1 to 4 of 4

Pasting to Excel sheet (getting desperate!)

  1. #1
    Guest

    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(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 ''''''''''''''''''




  2. #2
    Alien2_51
    Guest

    RE: Pasting to Excel sheet (getting desperate!)

    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(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 ''''''''''''''''''
    >
    >
    >
    >


  3. #3
    Fredrik Wahlgren
    Guest

    Re: Pasting to Excel sheet (getting desperate!)


    <Eric> wrote in message news:eWjdc0aKFHA.3652@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(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 ''''''''''''''''''
    >
    >


    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




  4. #4
    Guest

    Re: Pasting to Excel sheet (getting desperate!)

    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" <dan.billow.remove@monacocoach.removeme.com> wrote in message
    news:A66E84F1-FBF5-42F2-8A5A-68AAFEB6D4EA@microsoft.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(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 ''''''''''''''''''
    > >
    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1