[Attachment(s) from Trisno included below]
From: Andi Ali <aliandi05@yahoo.com>
To: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Sent: Wednesday, April 11, 2012 11:59 PM
Subject: Re: [belajar-access] Import Record exel ke table access
From: Soetrisno <trisno_ae@yahoo.com>
To: belajar-access@yahoogroups.com
Sent: Tuesday, April 10, 2012 3:47 PM
Subject: [belajar-access] Import Record exel ke table access
mas Andi sudah aku coba-coba tp ko ga bisa, ada peringatan compile Error. dimananya yang error ya mas.
mohon pencerahan, file terlampir
Terima kasih
From: Andi Ali <aliandi05@yahoo.com>
To: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Sent: Wednesday, April 11, 2012 11:59 PM
Subject: Re: [belajar-access] Import Record exel ke table access
Mas Trisno (maaf kalo nama panggilannya salah ya")
mungkin ini bisa membantu,
copy code berikut:
Function CopyRecordDariExcel()
'Tahap 1: Deklarasi nama object dan variable yg digunakan
Dim MyConnect As String
Dim myrecordset As New ADODB.Recordset
Dim MyTable As ADODB.Recordset
Dim MySQL As String
Dim serverName As String
Dim UserName As String
Dim userPass As String
Dim dbPath As String
Dim dbName As String
'Tahap 2: Deklarasi jenis koneksi database dan lokasinya
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & CurrentProject.Path & "\nama file di excel.xlsm;" & _
"Extended Properties=Excel 12.0"
'Tahap 3: Membuat kode SQL
MySQL = " SELECT * FROM [sheet1 di excel$]"
'Tahap 4: Inisialisasi recordset
Set myrecordset = New ADODB.Recordset
myrecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
'Tahap 5: Membuka table di Access menjadi Recordset
Set MyTable = New ADODB.Recordset
MyTable.Open "nama tabel di access", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Tahap 6: Import data dari excel ke table di Access
Do Until myrecordset.EOF
MyTable.AddNew
MyTable![nama kolom1 di excel] = myrecordset![nama field 1 di tabel access]
MyTable![nama kolom2 di excel] = myrecordset![nama field 2 di tabel access]
MyTable![nama kolom3 di excel] = myrecordset![nama field 3 di tabel access]
MyTable.Update
myrecordset.MoveNext
Loop
'Tahap 7: Bersihkan memory di VBA
myrecordset.close
MyTable.close
Set myrecordset = Nothing
Set MyTable = Nothing
End Function
kemudian buat file Excel yang akan di import ke access, kemudian simpan pada folder yang sama dengan file access...
buat suatu commmand button kemudian masukkan pada on klik kode berikut:
Private Sub Command0_Click()
Call CopyRecordDariExcel
MsgBox "Import data dari Excel ke tabel berhasil....", vbOKOnly, "Informasi"
DoCmd.close
End Sub
semoga membantu
Andi
From: Soetrisno <trisno_ae@yahoo.com>
To: belajar-access@yahoogroups.com
Sent: Tuesday, April 10, 2012 3:47 PM
Subject: [belajar-access] Import Record exel ke table access
dear master2 access
mohon pencerahan bagaimana membuat fungsi import record file exel ke table access.
terima kasih
mohon pencerahan bagaimana membuat fungsi import record file exel ke table access.
terima kasih
__._,_.___
Attachment(s) from Trisno
1 of 1 File(s)
SPAM IS PROHIBITED
.
__,_._,___
Tidak ada komentar:
Posting Komentar