Click here to Skip to main content
15,887,214 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to save excel worksheet in VB but it gives me error on
xlWorkBook = xlApp.Workbooks.Add(MissValue)

this line as error:
Exception from HRESULT: 0x800A03EC


Public Sub ExportToExcelFromDGView(ByVal dgView As DataGridView, ByVal strFileName As String, _
                                                  ByVal ParamArray strSkippedCol() As String)

        Try
            Dim strOutput(dgView.RowCount + 2, dgView.ColumnCount - 1) As String

            Dim str As String = ""
            Dim xlApp As New Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet

            Dim MissValue = System.Reflection.Missing.Value
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(MissValue)
            xlWorkSheet = xlWorkBook.Sheets.Add("Sheet1")

            '****For Column Heading(Adding column Names)
            Dim intColDisp As Integer = 0
            Dim flgCol As Boolean = False
            For intCol As Integer = 0 To dgView.ColumnCount - 1
                'Check for skipped Records
                flgCol = False
                For iArrLength As Integer = 0 To strSkippedCol.Length - 1
                    If dgView.Columns(intCol).Name.Trim.ToUpper = strSkippedCol.GetValue(iArrLength).ToString.Trim.ToUpper Then
                        flgCol = True
                        Exit For
                    End If
                Next
                If flgCol Then
                    Continue For
                End If

                strOutput(0, intCol) = CStr(dgView.Columns(intCol).HeaderText.Trim)
                intColDisp = intColDisp + 1
            Next

            '****For Records(Adding record text)
            For intRow As Integer = 0 To dgView.RowCount - 1
                intColDisp = 0
                For intCol As Integer = 0 To dgView.ColumnCount - 1
                    flgCol = False
                    For iArrLength As Integer = 0 To strSkippedCol.Length - 1
                        If dgView.Columns(intCol).Name.Trim.ToUpper = strSkippedCol.GetValue(iArrLength).ToString.Trim.ToUpper Then
                            flgCol = True
                            Exit For
                        End If
                    Next

                    If flgCol Then
                        Continue For
                    End If

                    strOutput(intRow + 1, intCol) = CStr(dgView.Item(intCol, intRow).Value.ToString.Trim)
                    intColDisp = intColDisp + 1

                    'End If
                Next
            Next

            xlWorkSheet.Range("A1").Resize(dgView.RowCount + 1, dgView.ColumnCount).Value = strOutput

            xlWorkBook.SaveAs(strFileName)
            xlWorkBook.Close()
            xlApp.Quit()
            ReleaseObjects(xlApp)
            ReleaseObjects(xlWorkBook)
            ReleaseObjects(xlWorkSheet)
            ReleaseObjects(MissValue)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub



This is my whole code ,Please help me out

What I have tried:

I tried adding
xlWorkBook = xlApp.Workbooks.Add()
Posted
Updated 26-Apr-18 2:01am
Comments
Jochen Arndt 26-Apr-18 6:00am    
Probably not the source of your problem but should be noted:
You are creating two Excel instances.

Remove the "New" from
Dim xlApp As New Excel.Application
snehalpawar 26-Apr-18 7:53am    
same error on same line :(
Jochen Arndt 26-Apr-18 8:04am    
I did not expect that it solves the error but thought it should be noted.

However, see my meanwhile posted solution.
CHill60 26-Apr-18 6:15am    
Did you get the same error when you used
xlApp.Workbooks.Add()
snehalpawar 26-Apr-18 7:53am    
yes

Are you sure that the error occurs when adding the workbook?

I guess it is happening when adding the sheet with
VB
xlWorkSheet = xlWorkBook.Sheets.Add("Sheet1")
The first parameter specifies the sheet before which the new sheet is added (see Worksheets.Add method (Microsoft.Office.Interop.Excel)[^]). But if there is no sheet named "Sheet1", the call will fail with 0x800A03EC (NAME_NOT_FOUND).

Use
VB
xlWorkSheet = xlWorkBook.Sheets.Add()
instead.
 
Share this answer
 
Comments
Maciej Los 26-Apr-18 8:25am    
Hawk eye!
snehalpawar 26-Apr-18 8:26am    
yes, error is on "xlWorkBook = xlApp.Workbooks.Add(MissValue)" this line,It is not going on 2nd line i.e "xlWorkSheet = xlWorkBook.Sheets.Add("Sheet1")"
Jochen Arndt 26-Apr-18 8:33am    
Have you tried calling both functions without parameters?

xlWorkBook = xlApp.Workbooks.Add()
xlWorkSheet = xlWorkBook.Sheets.Add()

As noted in my solution the error is NAME_NOT_FOUND. So it should be gone when not passing any name.
Workbooks.Add method (Microsoft.Office.Interop.Excel)[^] does not require parameter unless you want to create new workbook from template.
This should works as well:
C#
xlWorkBook  = xlApp.Workbooks.Add()


For further details, please see:
How to: Programmatically Create New Workbooks[^]
 
Share this answer
 
Comments
snehalpawar 26-Apr-18 8:20am    
sorry ,I tried this one but same error

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900