-
Sep 27th, 2014, 12:57 AM
#1
Thread Starter
Lively Member
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
-
Sep 27th, 2014, 07:16 AM
#2
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
-
Sep 27th, 2014, 02:16 PM
#3
Re: Excel Extraction (COMException was unhandled l Exception de HRESULT : 0x800A03EC)
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
-
Sep 27th, 2014, 05:26 PM
#4
Re: Excel Extraction (COMException was unhandled l Exception de HRESULT : 0x800A03EC)
Originally Posted by TnTinMN
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.
-
Sep 29th, 2014, 01:20 AM
#5
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|