Export Ms. Acces ke Ms. Excel dengan Menggunakan Visual Basic


Hi jumpa lagi untuk tutorial Visual Basic.
Pada kali ini kita akan mencoba membuat sebuah aplikasi dari Visual Basic untuk mengekspor data dari Acces ke Excel dengan menggunakan Visual Basic.

Langkah pertama, pastikan anda sudah menginstal Ms Acces dan Ms. Excel pada komputer anda agar semua langkah bisa dilakukan. Buka aplikasi visual basic anda dan buat 1 buah form dan 1 buah modul. Nama form dan Modul terserah anda.

Sisipkan source berikut ini pada modul :
=======
Public Function Export2XL(InitRow As Long, DBAccess As String, DBTable As String) As Long

Dim cn As New ADODB.Connection ‘Use for the connection string
Dim cmd As New ADODB.Command ‘Use for the command for the DB
Dim rs As New ADODB.Recordset ‘Recordset return from the DB
Dim MyIndex As Integer ‘Used for Index
Dim MyRecordCount As Long ‘Store the number of record on the table
Dim MyFieldCount As Integer ‘Store the number of fields or column
Dim ApExcel As Object ‘To open Excel
Dim MyCol As String
Dim Response As Integer

Set ApExcel = CreateObject(“Excel.application”) ‘Creates an object

ApExcel.Visible = True ‘This enable you to see the process in Excel
ApExcel.Workbooks.Add ‘Adds a new book.

‘Set the connection string
cn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & DBAccess
‘Open the connection
cn.Open

‘Check that the connection is open
If cn.State = 0 Then cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandText = DBTable
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
‘Count the number of fields or column
MyFieldCount = rs.Fields.Count

‘Fill the first line with the name of the fields
For MyIndex = 1 To MyFieldCount – 1
ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields(MyIndex).Name ‘Write Title to a Cell
ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
ApExcel.Cells(InitRow, (MyIndex + 1)).interior.colorindex = 36
ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
Next

‘Draw border on the title line
MyCol = Chr((64 + MyIndex)) & InitRow
ApExcel.Range(“A” & InitRow & “:” & MyCol).Borders.Color = RGB(0, 0, 0)
MyRecordCount = 2 + InitRow

‘Fill the excel book with the values from the database
Do While rs.EOF = False
For MyIndex = 2 To MyFieldCount
ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex – 1)).Value ‘Write Value to a Cell
ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False ‘Format the Cell
Next
MyRecordCount = MyRecordCount + 1
rs.MoveNext
If MyRecordCount > 50 Then
Exit Do
End If
Loop

‘Suggest to the user to save it’s work
Response = MsgBox(“Simpan Data ke Excel,baru klik OK”, vbOKOnly, “Simpan File ke Excel”)

‘Close the connection with the DB
rs.Close

‘Return the last position in the workbook
Export2XL = MyRecordCount

End Function

==========

Selanjutnya masukkan sebuah button pada form anda dan sisipkan source berikut pada button tadi, dalam hal ini button tadi kita kasih nama “CmdSalin“, nama databasenya “DATA.MDB“, dan tabel yang akan diekspor adalah “mahasiswa” :

Private Sub CmdSalin_Click()
Export2XL 3, “.\DATA.MDB”, “mahasiswa”
End Sub

K
Lik button tadi maka semua data secara otomatis akan tersalin ke Ms. Excel.

Demikian tutorial kali ini, sampai jumpa pada tutorial yang lain…
Semoga berhasil

11 thoughts on “Export Ms. Acces ke Ms. Excel dengan Menggunakan Visual Basic

  1. terima kasih atas tips nya, btw bagaimana kalau pada saat export ke Excell data tersebut di buat dalam 2 (dua) sheet sekaligus dan bagaimana menambah Sheet baru, thanks

    • Buat modul ke-2

      Public Function ExporttoXL(InitRow As Long, DBAccess As String, DBTable As String) As Long
      Dim cn As New ADODB.Connection
      Dim cmd As New ADODB.Command ‘Use for the command for the DB
      Dim rs As New ADODB.Recordset ‘Recordset return from the DB
      Dim MyIndex As Integer ‘Used for Index
      Dim MyRecordCount As Long ‘Store the number of record on the table
      Dim MyFieldCount As Integer ‘Store the number of fields or column
      Dim ApExcel As Object ‘To open Excel
      Dim MyCol As String
      Dim Response As Integer
      Set ApExcel = GetObject(, “Excel.application”) ‘Creates an object
      ApExcel.Visible = True ‘This enable you to see the process in Excel
      ‘ApExcel.Workbooks.Open (App.Path & “\Report.xls”) ‘.Add ‘Adds a new book.
      ApExcel.worksheets.Add

      ‘Set the connection string
      cn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & DBAccess
      ‘Open the connection
      cn.Open
      ‘Check that the connection is open
      If cn.State = 0 Then cn.Open
      Set cmd.ActiveConnection = cn
      cmd.CommandText = DBTable
      cmd.CommandType = adCmdTable
      Set rs = cmd.Execute
      ‘Count the number of fields or column
      MyFieldCount = rs.Fields.Count
      ‘Fill the first line with the name of the fields
      For MyIndex = 0 To MyFieldCount – 1 ‘ pengaturan penulisan column
      ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields(MyIndex).Name ‘Write Title to a Cell
      ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
      ApExcel.Cells(InitRow, (MyIndex + 1)).interior.colorindex = 36
      ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
      Next
      ‘Draw border on the title line
      MyCol = Chr((64 + MyIndex)) & InitRow
      ApExcel.Range(“A” & InitRow & “:” & MyCol).Borders.Color = RGB(0, 0, 0)
      MyRecordCount = 1 + InitRow ‘pengaturan penulisan column baris
      ‘Fill the excel book with the values from the database
      Do While rs.EOF = False
      For MyIndex = 1 To MyFieldCount ‘pengaturan kerapatan baris
      ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex – 1)).Value ‘Write Value to a Cell
      ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False ‘Format the Cell
      ApExcel.Cells(MyRecordCount, MyIndex).Borders.Color = RGB(0, 0, 0)
      Next
      MyRecordCount = MyRecordCount + 1
      rs.MoveNext
      If MyRecordCount > 500 Then
      Exit Do
      End If
      Loop
      ‘Suggest to the user to save it’s work
      Response = MsgBox(“Simpan Data ke Excel,baru klik OK”, vbOKOnly, “Simpan File ke Excel”)
      ‘Close the connection with the DB
      rs.Close
      ‘Return the last position in the workbook
      ExporttoXL = MyRecordCount
      End Function

      Private Sub CmdSalin_Click()
      Export2XL 3, “.\DATA.MDB”, “mahasiswa”
      ‘tambahkan perintah lagi…
      ExporttoXL 3,”.\Data.mdb”,”guru”
      ‘jika nama database 2 hurup atau lebih maka menggunakan
      ExporttoXL 3,”.\Data.mdb”,”[Nama Guru]”
      End Sub

      semoga bermanfaat…

  2. Ping-balik: Export Ms. Acces ke Ms. Excel dengan Menggunakan Visual Basic | Kumpulan E-Book | Tutorial Komputer | Video Tutorial | Tips & Trick | Terbaik se-Indonesia

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s