Click here to Skip to main content
12,699,351 members (34,298 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB7.x VB .NET
I am using a dataset to create an Excel spreadsheet. Here is my code.

Public Sub excelcheck()
            ' 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
                        dt.TableName = "Table" + Convert.ToString(i)
                    End If

                    startind = startind + 65000
                    endind = endind + 65000
                    i = i + 1
                End While
                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

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

                ' 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
            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 = Nothing
            ' Collect the unreferenced objects

            MessageBox.Show("Excel Report Genearted Sucessfully")
        Catch ex As Exception
        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
Updated 6-Jan-10 21:15pm
Rate this: bad
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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170118.1 | Last Updated 7 Jan 2010
Copyright © CodeProject, 1999-2017
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