Results 1 to 5 of 5

Thread: Excel Extraction (COMException was unhandled l Exception de HRESULT : 0x800A03EC)

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Sep 2014
    Posts
    80

    Excel Extraction (COMException was unhandled l Exception de HRESULT : 0x800A03EC)

    Hi team,

    Need your help on this one.. Using visual studio and working on a simple form that I would like to export contents to an excel sheet...I've imported all the references to the Excel object library but I'm getting an error (COMException was unhandled l Exception de HRESULT : 0x800A03EC) on the line bellow..


    Code:
       oSheet.range("A" & intRowEnd.ToString).value = TextBox1.Text

    Can you please help me figure this out ? ..Driving me insane




    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
    
      Dim oExcel As Object
            Dim oBook As Object
            Dim oSheet As Object
            Dim intRowEnd As Integer
            Dim FilePath As String
            Dim FileName As String
    
            ' Create excel object
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.workbooks.add
            oSheet = oBook.worksheets(1)
    
            ' File path and name as domain name
            FilePath = "C:\Users\ssroujian\Desktop\SpeedyLink"
            FileName = Me.TextBox1.Text
    
    
            oBook = oExcel.Workbooks.Open(FilePath & "\" & FileName & ".xlsx", UpdateLinks:=False, ReadOnly:=False)
            oSheet = oBook.worksheets(1)
            oBook.Activate()
    
    
    
            'find last row
            intRowEnd = oSheet.Range("A1").End(Excel.XlDirection.xlDown).Row
    
            ' Move to next row
            intRowEnd += 1
            oSheet.range("A" & intRowEnd.ToString).value = TextBox1.Text
            oSheet.range("B" & intRowEnd.ToString).value = TextBox2.Text
    
            'Save and Close
            oBook.Save()
            oExcel.Workbooks.close()
            oExcel = Nothing
    
    End Sub

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Excel Extraction (COMException was unhandled l Exception de HRESULT : 0x800A03EC)

    Hello,

    The following example is code modified from MSDN Excel get used rows and columns. This code uses early binding so if you want to stay with late binding you will need to tweak the code a bit. I kept it simple, get last row for a specific column, increment the value by one, write value to this cell.

    example, open w2.xlsx in the same folder as the app, work on sheet1, column J.
    You will need a reference added to the project for Microsoft.Office.Interop.Excel

    Code:
    WriteToCell(IO.Path.Combine(Application.StartupPath, "W2.xlsx"), "Sheet1", "J", "Hello")
    Code:
    Option Strict On
    Option Infer On
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Module Demo
        Public Sub WriteToCell(ByVal FileName As String, ByVal SheetName As String, ByVal Column As String, ByVal Value As String)
            Dim LastRowCount As Integer = 1
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
    
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
    
                    Dim xlCells As Excel.Range = xlWorkSheet.Cells()
                    Dim xlTempRange1 As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
                    Dim xlTempRange2 = xlWorkSheet.Rows
    
    
                    Dim xlTempRange3 = xlWorkSheet.Range(Column.ToUpper & xlTempRange2.Count)
                    Dim xlTempRange4 = xlTempRange3.End(Excel.XlDirection.xlUp)
    
                    LastRowCount = xlTempRange4.Row + 1
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells)
                    xlCells = Nothing
    
                    xlCells = xlWorkSheet.Range(Column & LastRowCount)
                    xlCells.Value = Value
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange4)
                    xlTempRange4 = Nothing
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange3)
                    xlTempRange3 = Nothing
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange2)
                    xlTempRange2 = Nothing
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange1)
                    xlTempRange1 = Nothing
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells)
                    xlCells = Nothing
    
                End If
    
            Next
    
    
            ' Common reason for failure, someone has the file open and we get it in read-only 
            ' mode
            Try
                xlWorkSheet.SaveAs(FileName)
            Catch ex As Exception
                MessageBox.Show("Failed to save data" & Environment.NewLine & ex.Message)
            End Try
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        End Sub
        Public Sub ReleaseComObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    End Module

  3. #3
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Excel Extraction (COMException was unhandled l Exception de HRESULT : 0x800A03EC)

    Quote Originally Posted by kevininstructor;4763363...The following example is code modified from MSDN [URL="http://code.msdn.microsoft.com/Excel-get-used-rows-and-15b43cb7"
    Excel get used rows and columns[/URL] ...
    Kevin, just a heads up comment on accessing the WorkSheet.UsedRange via SpecialCells(Excel.XlCellType.xlCellTypeLastCell). The UsedRange does not get updated until the property is explicitly called; retrieving the last cell in the UsedRange does not cause an update to occur. This is a known issue, but I do not know if this has been corrected in newer versions. It does it still exist in my 2007 Office version.

    In your example, you would never observe this issue, but if you were to clear a range containing the LastCell, then the method you used will report it as still being there.

    Code:
    Sub test()
       Dim app As New Excel.Application
       app.Visible = True
       Dim books As Excel.Workbooks = app.Workbooks
       Dim wb As Excel.Workbook = books.Add()
       ReleaseCOM(books)
       Dim ws As Excel.Worksheet = DirectCast(wb.Worksheets(1), Excel.Worksheet)
    
       Dim rng As Excel.Range = ws.Range("a1")
       rng.Value = 5
       ReleaseCOM(rng)
    
       rng = ws.Range("a5")
       rng.Value = "hi there"
    
       Dim cells As Excel.Range = ws.Cells
       Dim lastcell1 As Excel.Range = cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
    
       Debug.WriteLine("last cell1: " & lastcell1.Address) ' all looks good at this point - last cell1: $A$5
    
       rng.Clear() ' clear out that last cell
       ReleaseCOM(rng)
    
       ReleaseCOM(cells) ' just to show that a refreshed cells has no effect
       cells = ws.Cells
    
       Dim lastcell2A As Excel.Range = cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
       ' this still shows the old last cell because UsedRange has not been updated
       ' last cell after clear: $A$5
       Debug.WriteLine("last cell after clear: " & lastcell2A.Address)
    
       Dim ur As Excel.Range = ws.UsedRange
       ReleaseCOM(ur)
       Dim lastcell2B As Excel.Range = cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
       ' all's well - last cell after clear - the correct one: $A$1
       Debug.WriteLine("last cell after clear - the correct one: " & lastcell2B.Address)
    
       ReleaseCOM(cells)
       ReleaseCOM(ws)
       ReleaseCOM(lastcell1)
       ReleaseCOM(lastcell2A)
       ReleaseCOM(lastcell2B)
    
       wb.Close(False)
       ReleaseCOM(wb)
       app.Quit()
    
       ReleaseCOM(app, True)
    End Sub

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Excel Extraction (COMException was unhandled l Exception de HRESULT : 0x800A03EC)

    Quote Originally Posted by TnTinMN View Post
    Kevin, just a heads up comment on accessing the WorkSheet.UsedRange via SpecialCells(Excel.XlCellType.xlCellTypeLastCell). The UsedRange does not get updated until the property is explicitly called; retrieving the last cell in the UsedRange does not cause an update to occur. This is a known issue, but I do not know if this has been corrected in newer versions. It does it still exist in my 2007 Office version.

    In your example, you would never observe this issue, but if you were to clear a range containing the LastCell, then the method you used will report it as still being there.

    Code:
    Sub test()
       Dim app As New Excel.Application
       app.Visible = True
       Dim books As Excel.Workbooks = app.Workbooks
       Dim wb As Excel.Workbook = books.Add()
       ReleaseCOM(books)
       Dim ws As Excel.Worksheet = DirectCast(wb.Worksheets(1), Excel.Worksheet)
    
       Dim rng As Excel.Range = ws.Range("a1")
       rng.Value = 5
       ReleaseCOM(rng)
    
       rng = ws.Range("a5")
       rng.Value = "hi there"
    
       Dim cells As Excel.Range = ws.Cells
       Dim lastcell1 As Excel.Range = cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
    
       Debug.WriteLine("last cell1: " & lastcell1.Address) ' all looks good at this point - last cell1: $A$5
    
       rng.Clear() ' clear out that last cell
       ReleaseCOM(rng)
    
       ReleaseCOM(cells) ' just to show that a refreshed cells has no effect
       cells = ws.Cells
    
       Dim lastcell2A As Excel.Range = cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
       ' this still shows the old last cell because UsedRange has not been updated
       ' last cell after clear: $A$5
       Debug.WriteLine("last cell after clear: " & lastcell2A.Address)
    
       Dim ur As Excel.Range = ws.UsedRange
       ReleaseCOM(ur)
       Dim lastcell2B As Excel.Range = cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
       ' all's well - last cell after clear - the correct one: $A$1
       Debug.WriteLine("last cell after clear - the correct one: " & lastcell2B.Address)
    
       ReleaseCOM(cells)
       ReleaseCOM(ws)
       ReleaseCOM(lastcell1)
       ReleaseCOM(lastcell2A)
       ReleaseCOM(lastcell2B)
    
       wb.Close(False)
       ReleaseCOM(wb)
       app.Quit()
    
       ReleaseCOM(app, True)
    End Sub
    Thanks for the heads up... Yes I know about this, I have written a bunch of articles on Excel, none of them are guaranteed to work in all possible situations.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Sep 2014
    Posts
    80

    Re: Excel Extraction (COMException was unhandled l Exception de HRESULT : 0x800A03EC)

    Hi guys!

    Thank you so much for providing so much clarity on this subject I really appreciate the help and looking forward in learning more with you guys !! Kudos for the efficiency you guys have !

    I do have one more question for you though, In my Form I have a rich texbox where user may paste pictures of screenshots they take and wanted to know how do I go in including this pasted picture(s) as an attachment or possible as part of the HTML body.

    Here's what I have so far in the HTMLBODY:





    Code:
        With objEmail
                .To = "serge.sroujian@shawdirect.ca"
                .Subject = "Error Report - SHAWcut - " & txt_agntName.Text & " - PC Name : " & PC_Name.Text
                '.body = "Hi, " & vbCrLf & vbCrLf & "An error or feedback has been submitted in the SHAWCut tool:" _
                '    & vbCrLf & vbCrLf & vbCrLf & _
                '    "Agent Name : " & txt_agntName.Text _
                '    & vbCrLf & vbCrLf & _
                '    "Comments / Feedbacks: " & vbCrLf & Rtxt_ErrorDetails.Text _
                '    & vbCrLf & vbCrLf & vbCrLf & _
                '    "ScreenShots: " & Rtxt_Screenshots.Text _
                '& vbCrLf & vbCrLf & _
                'vbCrLf & vbCrLf & _
                '"PC Name : " & PC_Name.Text _
                '& vbCrLf & vbCrLf & _
                '"OS Version : " & Combo_OSVersions.Text
    
    
    
                .HTMLBody = "<span LANG=EN>" _
    & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" & _
    "Hi, <br ><br > " & "An error or feedback has been submitted in the SHAWCut tool: <br ><br ><br >" & _
    "<B>Agent Name : </B>" & txt_agntName.Text _
    & "<br ><br >" & _
    "<B>Comments / Feedbacks: </B>" & "<br >" & Rtxt_ErrorDetails.Text _
    & "<br ><br ><br >" & _
    "<B>ScreenShots: </B>" & Rtxt_Screenshots.Text _
    & "<br ><br ><br ><br ><br ><br >" & _
    "<B>PC Name : </B>" & PC_Name.Text _
    & "<br ><br >" & _
                "<B>OS Version : </B>" & Combo_OSVersions.Text

Posting Permissions

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



Click Here to Expand Forum to Full Width