none
Trying to call existing excel workbook from vb.net application I am creating RRS feed

  • Question

  • Seams I have fallen on an aged problem I have tried to take a cells value and return it into a textbox of the form in my program but when I code up, the line  "xlApp = New Excel.Application" pulls an error

    "Error 1 Type 'Microsoft.Office.Interop.Excel.Application' is not defined. "

    I have looked and I don't have this dll, can I get it or is there another way?

    I'm on vista 64 bit, running Office 2007 and coding with Microsoft Visual Basic 2010 Express

    I have tryed: Putting code in a Class or Module, in the Form1.Designer, Class1.vb*, Form1.vb*

                        Referencing Microsoft Excel 12.0 Object Library

    My code is below, all I need is to know how to read/write to Excel cells and quite new at VB.Net

    Any help would be greatly appriciated, Thanks

                       

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    
    
    Module test1
    
    
        Private Sub Button1_Click_1(sender As System.Object, e As System.EventArgs) 'Handles Button1.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
    
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
            xlWorkSheet = xlWorkBook.Worksheets("sheet1")
            'display the cells value B2
            Form1.TextBox1.Text = xlWorkSheet.Cells(2, 2).value()
            'edit the cell with new value
            xlWorkSheet.Cells(2, 4) = "YES IT WORKS!"
    
            xlApp.DisplayAlerts = False
            'xlWorkBook.Save()
            xlWorkBook.Close(True, "test1.xlsx")
    
            xlApp.Quit()
            releaseObject(xlWorkSheet)
            releaseObject(xlWorkBook)
            releaseObject(xlApp)
    
        End Sub
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    End Module


    • Edited by J_XL Monday, May 26, 2014 12:47 PM
    Monday, May 26, 2014 12:46 PM

All replies

  • You writing in VBA forum - that code is not the same as VB.NET

    anyway.. I make a new Windows application and put your code to them.

    Little change and works as well

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
    
            xlApp = New Excel.Application
            Try
                xlWorkBook = xlApp.Workbooks.Open("c:\test1.xlsx")
    
            Catch ex As Exception
                MsgBox("No Workbooks c:\test1.xlsx")
                GoTo koniec
            End Try
            xlWorkSheet = xlWorkBook.Worksheets(1)
            'display the cells value B2
            TextBox1.Text = xlWorkSheet.Cells(2, 2).value
            'edit the cell with new value
            xlWorkSheet.Cells(2, 4) = "YES IT WORKS!"
    
            xlApp.DisplayAlerts = False
            xlWorkBook.Close(True)
    
            releaseObject(xlWorkSheet)
            releaseObject(xlWorkBook)
    
    koniec:
            xlApp.Quit()
            releaseObject(xlApp)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    End Class



    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Thursday, June 5, 2014 8:14 PM
    Answerer