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

Selamat datang di Leak, terimakasih karena anda telah berkunjung ke blog saya, kali ini saya akan menulis tentang Export Ms. Acces ke Ms. Excel dengan Menggunakan Visual Basic, semoga informasi yang anda cari ada disini, selamat membaca.

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.

Demikian informasi dari saya tentang Search Engine Simulator yang dapat saya sampaikan, semoga berguna dan dapat menambah pengetahuan bagi yang membacanya.