Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB7.x .NET
I am using a dataset to create an Excel spreadsheet. Here is my code.
 
Public Sub excelcheck()
        Try
            ' Create the Excel Application object
            Dim excelApp As New ApplicationClass()
 
            ' Create a new Excel Workbook
            Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing)
 
            Dim sheetIndex As Integer = 0
            Dim col, row As Integer
            Dim excelSheet As Worksheet
 
           
            ' Copy each DataTable as a new Sheet

            dsold = Utilities.exceldataset.Copy
            If dsold.Tables(0).Rows.Count > 65000 Then
                Dim i As Integer = 0
                Dim startind As Integer
                Dim endind As Integer
                startind = 0
                endind = 64999
                Dim totalcount As Double
                totalcount = dsold.Tables(0).Rows.Count
                Dim currentcount As Double
                currentcount = Math.Ceiling(totalcount / 65000)
                While (i < currentcount)
 
                    Dim dt As New System.Data.DataTable("Table" + Convert.ToString(i))
                    dt = splitrecords(dsold.Tables(0), dsold.Tables(0).Rows.Count, startind, endind)
 
                    If i = 0 Then
                        ds.Tables.Add(dt.Copy)
                    Else
                        dt.TableName = "Table" + Convert.ToString(i)
                        ds.Tables.Add(dt.Copy)
                    End If
 
                    startind = startind + 65000
                    endind = endind + 65000
                    i = i + 1
                End While
            Else
                ds = dsold.Copy
            End If
 

            For Each dt As System.Data.DataTable In ds.Tables
 
                sheetIndex += 1
 
                ' Copy the DataTable to an object array
                Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object
 
                ' Copy the column names to the first row of the object array
                For col = 0 To dt.Columns.Count - 1
                    rawData(0, col) = dt.Columns(col).ColumnName
                Next
 
                ' Copy the values to the object array
                For col = 0 To dt.Columns.Count - 1
                    For row = 0 To dt.Rows.Count - 1
                        rawData(row + 1, col) = dt.Rows(row).ItemArray(col)
                    Next
                Next
 
                ' Calculate the final column letter
                Dim finalColLetter As String = String.Empty
                Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
                Dim colCharsetLen As Integer = colCharset.Length
 
                If dt.Columns.Count > colCharsetLen Then
                    finalColLetter = colCharset.Substring( _
                     (dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
                End If
 
                finalColLetter += colCharset.Substring( _
                  (dt.Columns.Count - 1) Mod colCharsetLen, 1)
 
                ' Create a new Sheet
                excelSheet = CType( _
                    excelWorkbook.Sheets.Add(excelWorkbook.Sheets(sheetIndex), _
                    Type.Missing, 1, XlSheetType.xlWorksheet), Worksheet)
 
                excelSheet.Name = dt.TableName
 
                ' Fast data export to Excel
                Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
                excelSheet.Range(excelRange, Type.Missing).Value2 = rawData
 
                ' Mark the first row as BOLD
                CType(excelSheet.Rows(1, Type.Missing), Range).Font.Bold = True
                excelSheet = Nothing
            Next
            Dim strDate1 As String = String.Format("{0:dd}", DateTime.Now).ToUpper
            Dim strMonth As String = String.Format("{0:MM}", DateTime.Now).ToUpper()
            Dim stryear As String = String.Format("{0:yyyy}", DateTime.Now).ToUpper()
            StrServerFile = Utilities.Jobid + " " + strDate1 + strMonth + stryear + ".xls"
            ' Save and Close the Workbook
            excelWorkbook.SaveAs("C:\Bcp\" + StrServerFile, XlFileFormat.xlWorkbookNormal, Type.Missing, _
             Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            excelWorkbook.Close(True, Type.Missing, Type.Missing)
            excelWorkbook = Nothing
            ' Release the Application object
            excelApp.Quit()
            excelApp = Nothing
            insertexcelfile(StrServerFile)
            ' Collect the unreferenced objects
            GC.Collect()
            GC.WaitForPendingFinalizers()
 
            MessageBox.Show("Excel Report Genearted Sucessfully")
            SetLabelText(False)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
The exception details are:
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.set_Value2(Object )
at Exceltest.Form1.excelcheck() in D:\exceltest\Exceltest\Form1.vb:line 118
Posted 6-Jan-10 3:08am
Edited 6-Jan-10 21:15pm
0x3c033.2K
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This is a lot of code for people to glean an answer from given the dearth of detail. I'd recommend modifying the catch to show ex.ToString() instead of just the message. Then you'll get a stack trace and you'll be able to narrow down the cause, or come back here with more details.
 
A quick google seems to indicate this error is thrown when trying save, you might want to check out SaveCopyAs.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Yes, you're right; I've edited my exception details into the question.
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 7 Jan 2010
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100