locked
Open Excel for editing RRS feed

  • Question

  • I am using VB.NET to transfer data from an access DB to an already existing *.xls file.

    I cannot seem to open the excel spreadsheet to get access to the cells.

    Dim sFile As String
            sFile = "Q:\Voicenet\RTS Folder\RTS Charges\Accounts\RTS March-08-Darren.xls"
            FileOpen(1, sFile, OpenMode.Input)
           
            Dim sconn As String
            sconn = "Provider=Microsoft.jet.oledb.4.0;Data Source= sFile ;Extended Properties=Excel 8.0;"
            Dim excelconn As New System.Data.OleDb.OleDbConnection(sconn)
            excelconn.Open()
            Dim objcmd As New System.Data.OleDb.OleDbCommand()
            objcmd.Connection = excelconn
            objcmd.CommandText = "Insert into (Installs)(A1) values ('XXX')"
            objcmd.ExecuteNonQuery()

    last line says that it cannot find "Installs" even though I have checked spelling and it is the first worksheet.

    Please advise, thanks
    Tuesday, March 25, 2008 2:26 AM

Answers

  •  MurrayS wrote:
    I am using VB.NET to transfer data from an access DB to an already existing *.xls file.
    I cannot seem to open the excel spreadsheet to get access to the cells.

     

    Hi Murray,

     

    Here is correct code sample: Writing data to Excel spreadsheet from Access database.

    Firstly Add Reference to COM component: Microsoft Excel Object Library.

    Code Snippet

    Imports Microsoft.Office.Interop.Excel

    Imports System.Data.OleDb

     

    Public Class Form1

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            'Firstly retrieve data from Access database

            Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\myDB.mdb")

            con.Open()

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * From Table1", con)

            Dim sdr As OleDbDataReader = cmd.ExecuteReader

     

            ' Then open specific spreadsheet of Excel Book

            Dim oExcel As Object = CreateObject("Excel.Application")

            Dim oBook As Object = oExcel.Workbooks.Open("C:\Book1.xls")

            Dim oSheet As Object = oBook.Worksheets(1)

     

            'Transfer data from Access DB to the Excel file

            Dim CurCell As Range = oSheet.Range("A1") 'e.g. Start from Cell A1

            While (sdr.Read())

                CurCell.Value = sdr.Item("Column1")

                CurCell = CurCell.Offset(1, 0) ' Move to next cell vertically

            End While

     

            'Dispose objects

            oExcel.Quit()

            sdr.Close()

            con.Close()

     

        End Sub

     

    End Class

     

    There are many code samples about how to manipulate Excel Spreadsheet data in VB.NET.

     

    1. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2919624&SiteID=1

       How to read and write cells in Excel Spreadsheet

     

        Store each Array element to Excel in consecutive cells

     

    3. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2950911&SiteID=1

       Sum those consecutive cells horizontally or vertically

     

    4. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2912407&SiteID=1

       Retrieve all WorkSheet names in Excel file and binding specific WorkSheet content to DataGridView

     

    5. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2983726&SiteID=1

       Retrieve a range of cells and bind to DataGridView

     

     

    Regards,

    Martin

    Thursday, March 27, 2008 8:26 AM