from data textbox to excel vb.net

Question

I have this code that allows me to export the data contained in the two TextBoxs in an excel spreadsheet, the problem is that the excel file is not created. I put the two statements are not working for rescuing comment below, maybe someone can test it and tell me why it is wrong.

Dim objExcel As New Excel.Application     ' Represents an instance of Excel
Dim objWorkbook As Excel.Workbook     'Represents a workbook object
Dim objWorksheet As Excel.Worksheet     'Represents a worksheet object

objWorkbook = objExcel.Workbooks.Add
objWorksheet = CType(objWorkbook.Worksheets.Item(1), Excel.Worksheet)

'This form contains two text boxes, write values to cells A1 and A2
objWorksheet.Cells(1, 1) = TextBox1.Text
objWorksheet.Cells(2, 1) = TextBox2.Text

objWorkbook.Close(False)

'objWorkbook.Save()
'or
'objWorkbook.SaveAs("C:\Temp\Book1.xls")

objExcel.Quit()

How/where specifically does the code fail? Is there an error at run time? What is that error? Presumably if SaveAs() doesn't throw an error then the file should indeed be successfully saved. Are you sure it wasn't? Did you uncomment the "save" lines when you actually ran the code? — David
– David, Commented Oct 10, 2014 at 11:58
Would you have done it before to debug the code, however: Would you have done it before to debug the code, however, this line of code: objWorkbook.SaveAs ("C: \ Temp \ Book1.xls") I get this error after pressing the button that invokes the code written above: The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED)) — pollonz
– pollonz, Commented Oct 10, 2014 at 12:10
Do you mean the code is throwing an exception? What is the exact exception? What is the stack trace? Don't just let the application crash and make guesses about it, catch the exception and examine it. — David
– David, Commented Oct 10, 2014 at 12:12

Trevor · Accepted Answer · 2014-10-10 22:47:44Z

Code is tried and tested

Your issue can be from a few different things that I can see. I provided a few issues that I could see from your code you posted.

Your calling .Close before actually saving the workbook.
You didn't specify the "FileFormat" in the save function.
Your not quitting the instance of Excel.
It's important to release ALL objects of Excel you use. If not, it's being held up in memory and will not be released.

Code Below

    Dim xlApp As New excel.Application
    Dim xlWorkBook As excel.Workbook
    Dim xlWorkSheet As excel.Worksheet
    Try
        xlApp.DisplayAlerts = False
        xlWorkBook = xlApp.Workbooks.Add
        xlWorkSheet = DirectCast(xlWorkBook.Sheets("Sheet1"), excel.Worksheet)

        xlApp.Visible = False 'Don't show Excel; we can and we don't have too

        xlWorkSheet.Cells(1, 1) = TextBox1.Text
        xlWorkSheet.Cells(2, 1) = TextBox2.Text

        xlWorkBook.SaveAs("C:\Temp\Book1.xls", FileFormat:=56) 'Save the workbook
        xlWorkBook.Close() 'Close workbook
        xlApp.Quit() 'Quit the application

        'Release all of our excel objects we used...
        ReleaseObject(xlWorkSheet)
        ReleaseObject(xlWorkBook)
        ReleaseObject(xlApp)

    Catch ex As Exception
    End Try

Method to Release Excel Objects

 Public Shared Sub ReleaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
 End Sub

Collectives™ on Stack Overflow

from data textbox to excel vb.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related