export data ke excel dengan vb.net

Pada kesempatan kali ini saya akan menjelaskan tentang bagaimana cara melakukan export data (access) ke bentuk excel:

1. Buatlah sebuah project baru
2. Buatlah sebuah form dengan bentuk seperti gambar di bawah ini
3. Tambahkan komponen openfiledialog

Lalu tulis atau copy coding di bawah ini. Selamat mencoba semoga berhasil

ImportsSystem.Data.OleDb

Public Class exportkeexcel

    Dim Conn AsOleDbConnection

    Dim da AsOleDbDataAdapter

    Dim ds As DataSet

    Dim cmd AsOleDbCommand

    Dim rd AsOleDbDataReader

    Dim tbl As DataTable

    Public Sub Koneksi()

        Conn = New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source='" & Label1.Text & "'")

        Conn.Open()

    End Sub

    Private SubButton1_Click(ByVal sender As System.Object, ByVale As System.EventArgs) HandlesButton1.Click

        On Error Resume Next

        OpenFileDialog1.ShowDialog()

        Label1.Text = OpenFileDialog1.FileName

        Call tampiltabel()

    End Sub

    Sub tampiltabel()

        Dim userTables AsDataTable = Nothing

        Call Koneksi()

        Dim restrictions() AsString = New String(4) {}

        restrictions(3) = "Table"

        userTables = Conn.GetSchema("Tables")

        userTables = Conn.GetSchema("Tables", New String() {Nothing, Nothing, "TABLE"})

        Conn.Close()

        Dim i As Integer

        For i = 0 TouserTables.Rows.Count - 1 Step i + 1

            System.Console.WriteLine(userTables.Rows(i)(2).ToString())

            ListBox1.Items.Add(userTables.Rows(i)(2).ToString())

        Next

        DimSchemaTable As DataTable

        ListBox1.Items.Clear()

        Call Koneksi()

        SchemaTable = Conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})

        Dim int As Integer

        For int = 0 ToSchemaTable.Rows.Count - 1

            If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then

                ListBox1.Items.Add(SchemaTable.Rows(int)!TABLE_NAME.ToString())

            End If

        Next

    End Sub

    Private SubButton3_Click(ByVal sender As System.Object, ByVale As System.EventArgs) HandlesButton3.Click

        Try

            Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"& Label1.Text & "'")

            AccessConn.Open()

            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Excel 8.0;DATABASE="& ListBox1.Text & ".xls;].["& ListBox1.Text & "] from ["& ListBox1.Text & "]", AccessConn)

            AccessCommand.ExecuteNonQuery()

            AccessConn.Close()

            MsgBox("file sudah berhasil dikonversi")

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

    Private SubListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVale As System.EventArgs) HandlesListBox1.SelectedIndexChanged

        da = New OleDbDataAdapter("select * from " & ListBox1.Text & "", Conn)

        ds = New DataSet

        da.Fill(ds)

        DGV.DataSource = ds.Tables(0)

        DGV.ReadOnly = True

        Label2.Text = Application.StartupPath + "\"+ ListBox1.Text + ".xls"

    End Sub

    Private SubButton2_Click(ByVal sender As System.Object, ByVale As System.EventArgs) HandlesButton2.Click

        Try

            System.Windows.Forms.Help.ShowHelp(Me, Label2.Text)

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

End Class