Use VB to Copy dataset from Excel ListObject to a new Excel file. RRS feed

  • General discussion

  • If you are using VB.NET and VSTO and you have a ListObject on top of Excel, you can use ADO.NET to copy the dataset associated with the ListObject to a completely new Excel .xls file like this:


    Imports System.Data.OleDb


    Private m_sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\ExcelData2.xls;" & _

    "Extended Properties=""Excel 8.0;HDR=YES"""


    Private Sub MySub


    'Let's assume you have an Excel worksheet called Sheet1 and on it is a ListObject and an associated dataset called MyExcelDataset and a bindingsource called Sheet1BindingSource.  Your dataset comprises of two fields called

    'Bank and Checknum.  All of this code is inside the Sheet1.vb Class, so Me refers to Sheet1.


    'Stop any editing going on in the ListObject



    'This procedure probably only works on new files, so if we did this once already, get rid of the output file.

    If Dir("C:\ExcelData2.xls") <> "" Then Kill("C:\ExcelData2.xls")


    'Create the table inside of the new Excel file.

    Dim conn As New OleDbConnection()

    conn.ConnectionString = m_sConn


    Dim cmd As New OleDbCommand()

    cmd.Connection = conn

    cmd.CommandText = "CREATE TABLE Checks (Bank char(8), Checknum float)"




    'Get the empty dataset from the new Excel file.

    Dim da As New OleDbDataAdapter("Select * From [Checks$]", conn)

    Dim ds As DataSet = New DataSet()

    da.Fill(ds, "Checks")


    ' Generate the InsertCommand and add the parameters for the command.

    da.InsertCommand = New OleDbCommand( _

    "INSERT INTO [Checks$] (Bank, Checknum) VALUES (?, ?)", conn2)

    da.InsertCommand.Parameters.Add("@Bank", OleDbType.Char, 8, "Bank")

    da.InsertCommand.Parameters.Add("@Checknum", OleDbType.Decimal, 9, "Checknum")


    'Copy the entire table, inserting and/or updating rows.

    'Upsert makes sure rows are marked as Added so that the da.Update will work.

    ds.Tables(0).Load(Me.MyExcelDataset.Sheet1.CreateDataReader, LoadOption.Upsert)


    ' Apply the dataset changes to the actual data source (the workbook).

    da.Update(ds, "Checks")



    End Sub

    Tuesday, December 18, 2007 8:52 AM