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

Imports System.Data.OleDb

Public Class exportkeexcel

    Dim Conn As OleDbConnection

    Dim da As OleDbDataAdapter

    Dim ds As DataSet

    Dim cmd As OleDbCommand

    Dim rd As OleDbDataReader

    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 Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        On Error Resume Next

        OpenFileDialog1.ShowDialog()

        Label1.Text = OpenFileDialog1.FileName

        Call tampiltabel()

    End Sub

    Sub tampiltabel()

        Dim userTables As DataTable = Nothing

        Call Koneksi()

        Dim restrictions() As String = 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 To userTables.Rows.Count - 1 Step i + 1

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

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

        Next

        Dim SchemaTable 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 To SchemaTable.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 Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.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 Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.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 Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Try

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

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

End Class