Tùy chọn
[Demo p1] Import/Export Excel + Hàm Liệt tên ALL Access
- [Demo p1] Import/Export Excel + Hàm Liệt tên ALL Access
-
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
LUUTHAI > 16-09-18, 09:58 PM
Bạn cho mình xin file .mdb để mình học tập Nhập/Xuất giữa Excel và Access
luuthai1974@gmail.com -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
Che_Guevara > 18-09-18, 12:03 PM
cho em xin file tham khảo với thank...!
mail: nhnhanqbi@gmail.com -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
hoabattu3387 > 02-10-18, 10:49 AM
(06-03-17, 06:41 PM)maidinhdan Đã viết: Demo_Code Import IMPORT/EXPORT EXCEL-ACCESS và CÁC HÀM LIỆT KÊ TÊN SHEET, TABLE, QUERY, FORM, REPORT, MACRO, MODULES VÀO COMBOBOX
cho m xin code với nhé: phuongneu3387@gmail.com
Công dụng:
- Nhập/Xuất giữa Excel và Access: Bạn có thể xoá table + file Excel trong bản Demo này và thay thế bằng table + file Excel của bạn nó vẫn chạy ầm ầm ( Lưu ý: Riêng file Excel dòng đầu tiên là dòng tiêu đề nhé, nếu bạn nào có nhu câu thay dòng đâu tiên thành dòng thứ mấy thì xem trong code mình có ghi chú)
- Liệt kê tên Table, query, form, Macro, report, Modules
Có nhiều Code dành cho ai đam mê học tập nghiên cứu: nhất là liên quan đến Vòng lặp và duyệt mãng (Array)
Hình minh hoạ
![[Hình: DemoXuatNhapvaLietkeAll.png]](http://sv1.upsieutoc.com/2017/03/06/DemoXuatNhapvaLietkeAll.png)
Nếu để sử dụng thì tải theo địa chỉ ở dưới.
Nếu muốn tải về để học tập thì vui lòng để lại Email để mình gửi bản *.mdb để xem code
Thân mến! -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
maidinhdan > 03-11-18, 02:28 PM
Đã gửi:
luuthai1974@gmail.com,
Ngo Huu Nhan <nhnhanqbi@gmail.com>,
phuongneu3387@gmail.com -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
hoabattu3387 > 15-11-18, 03:18 PM
(03-11-18, 02:28 PM)maidinhdan Đã viết: Đã gửi:
mình có test thử lấy dữ liêu từ excel vào access bằng cách sử dụng phương thức addnew của recordset . Tuy nhiên đối với dữ liệu khoảng vài nghìn dòng thì phải mất mấy phút mới import xong. vậy có cách nào nhanh hơn không mong bạn chỉ giáo (vì mình phải import nhiều file mà mỗi file vài nghìn dòng)
luuthai1974@gmail.com,
Ngo Huu Nhan <nhnhanqbi@gmail.com>,
phuongneu3387@gmail.com -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
maidinhdan > 16-11-18, 01:26 AM
(15-11-18, 03:18 PM)hoabattu3387 Đã viết: mình có test thử lấy dữ liêu từ excel vào access bằng cách sử dụng phương thức addnew của recordset . Tuy nhiên đối với dữ liệu khoảng vài nghìn dòng thì phải mất mấy phút mới import xong. vậy có cách nào nhanh hơn không mong bạn chỉ giáo (vì mình phải import nhiều file mà mỗi file vài nghìn dòng)
Đây là đoạn code bạn cần tìm:
Mình đã Test với File với 8000 mẫu tin, cho Add 1000mẫu/Lần, trung bình 1000mẫu là 1 giây.
Kết quả test
Mã PHP:Tong so Mau tin Test: 8002
Bat dau vong lap: 12:29:01 AM
Thoi gian UpdateBatch 12:29:02 AM
Thoi gian UpdateBatch 12:29:03 AM
Thoi gian UpdateBatch 12:29:04 AM
Thoi gian UpdateBatch 12:29:05 AM
Thoi gian UpdateBatch 12:29:06 AM
Thoi gian UpdateBatch 12:29:07 AM
Thoi gian UpdateBatch 12:29:08 AM
Thoi gian UpdateBatch 12:29:09 AM
Ket thuc vong lap: 12:29:09 AM
Demo Link Download GoogleDrive
Code Minh Họa
Mã PHP:Function ImportExcelToAccess()
Dim RsExcel As Object, SQL As String
Dim Strcon As String, LinkFileExcel As String, TenSheet As String, VungCopy As String, CotCuoicung As Long
Dim DongCuoiCung As Long
LinkFileExcel = CurrentProject.Path & "\Test.xlsx"
TenSheet = "Sheet1"
'Lay so dong tren File Excel
Dim oEx As New Excel.Application ' oEx la bien oExcel
Dim oBook As Workbook ' set oBook la tap hop oExcel
' Mo file can chen
Set oBook = oEx.Workbooks.Open(LinkFileExcel)
' Mo Sheet can chen
Dim oSheet As Worksheet ' set oSheet la tap hop Sheet
Set oSheet = oBook.Worksheets(TenSheet)
' Dem so dong, so cot
With oSheet.UsedRange
DongCuoiCung = .Rows(UBound(.Value)).Row
End With
oBook.Close False
Set oEx = Nothing
VungCopy = "A7:L" & DongCuoiCung ' Bat cau copy tu cot A7 den cot L xxx
' Mo File Excel
Set RsExcel = CreateObject("ADODB.Recordset")
SQL = "select * from [" & TenSheet & "$" & VungCopy & "]"
Strcon = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & LinkFileExcel _
& ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;"";")
RsExcel.Open SQL, Strcon, 3, 1
''''''''''''
' Xoa du lieu trong tblTest
CurrentDb.Execute "DELETE * FROM tblTest", dbFailOnError 'Xoa tblTest
Dim i As Long
Dim RsAcc As New ADODB.Recordset
'Mo tblTest
RsAcc.Open "tblTest", CurrentProject.AccessConnection, _
adOpenstatic, adLockBatchOptimistic, adCmdTableDirect
'So dong moi lam UpdateBatch
Dim SoDongUpdateTrenLan As Integer, x As Integer
x = 0
SoDongUpdateTrenLan = 1000
With RsExcel
Debug.Print "Bat dau vong lap: ", Time
Do While Not .EOF ' Duyet mau tin tren Excel
RsAcc.AddNew
For i = 0 To (.Fields.Count - 1) ' Duyet mau tin tren Access
RsAcc.Fields(i).Value = .Fields.Item(i).Value
Next
.MoveNext
'So dong moi lam UpdateBatch
x = x + 1
If x = SoDongUpdateTrenLan Then ' 1000Dong Them 1 lan
RsAcc.UpdateBatch
Debug.Print "Thoi gian UpdateBatch", Time
x = 0
End If
Loop
Debug.Print "Ket thuc vong lap: ", Time
End With
MsgBox "Da Hoan thanh"
RsExcel.Close
Set RsExcel = Nothing
RsAcc.Close
Set RsAcc = Nothing
End Function
Chúc bạn vui khỏe -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
mrsiro > 16-11-18, 11:29 AM
Chào bạn maidinhdan, cách này áp dụng vô trường hợp lấy recordset từ sqlserver về rồi add vào table tại access có nhanh ko bạn. Mình đang dùng cách dùng vòng lặp qua từng mẫu tin trong recordset rồi add vào table access.
Code của bạn khác của mình ở đoạn:
Đoạn này có tác dụng gì vậy bạnMã:x = x + 1
If x = SoDongUpdateTrenLan Then ' 1000Dong Them 1 lan
RsAcc.UpdateBatch
Debug.Print "Thoi gian UpdateBatch", Time
x = 0
End If -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
maidinhdan > 16-11-18, 02:23 PM
(16-11-18, 11:29 AM)mrsiro Đã viết: Chào bạn maidinhdan, cách này áp dụng vô trường hợp lấy recordset từ sqlserver về rồi add vào table tại access có nhanh ko bạn. Mình đang dùng cách dùng vòng lặp qua từng mẫu tin trong recordset rồi add vào table access.
Code của bạn khác của mình ở đoạn:
Đoạn này có tác dụng gì vậy bạnMã:x = x + 1
If x = SoDongUpdateTrenLan Then ' 1000Dong Them 1 lan
RsAcc.UpdateBatch
Debug.Print "Thoi gian UpdateBatch", Time
x = 0
End If
Mình có giải thích phía trên bạn không chịu đọc kỹ.
Đoạn này có ý nghĩa như sau: Thay vì con trỏ đi qua 1 mẫu tin thì update 1 lần, tương đương 1 dòng. Thì
Thứ 1, đoạn code này nó sẽ canh đủ 1000 dòng mới ra lệnh Update.
Thứ 2, Không gây ra hiện tượng treo/lag khi load trên 10.000 dòng.
Thứ 3, Rút ngắn thời gian Import dù cho bạn Update 1 triệu mẫu tin đi chăng nửa.
* Cuối cùng đoạn code này bạn có thể áp dụng với SQL Server to Access còn nhanh hơn nửa, chỉ cần chỉnh lại chuỗi kết nối là ok.
* Về mặc hạn chế của code này đó là: phải mở Excel 2 lần
1. Mở Excel lần 1, để xác định tổng số dòng cần Import ( Tốn khoảng 1 giây) rồi nó sẽ đóng lại sau khi đếm số mẫu tin.
2. Mở lần 2 để tiến hành Add vào Access. -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
mrsiro > 16-11-18, 03:20 PM
Chào bạn maidinhdan, trong trường hợp của mình table là table tại file mdb, dùng theo ví dụ của bạn là khai báo 1 recordset theo kiểu adodb.recordset, sau đó cho recordset.open table này.
Nhưng đây là table tại file mdb thì có cần phải dùng cái này hay ko, bởi vì nếu dùng adorecordset phải thêm chuỗi kết nối đằng sau, trong khi đó table ở tại file mdb thì khai báo thêm chuỗi kết nối rườm rà quá.
Còn nếu chuyển sang daorecordset thì lại ko dùng được updatebtch.
Thật là đau đầu. Nếu bạn có cách nào hay thì chỉ mình với nhé. -
RE: Demo Import/Export Excel + Hàm Liệt tên ALL Access
ongke0711 > 16-11-18, 04:07 PM
(16-11-18, 01:26 AM)maidinhdan Đã viết: Code Minh Họa
Mã PHP:Function ImportExcelToAccess()
Dim RsExcel As Object, SQL As String
Dim Strcon As String, LinkFileExcel As String, TenSheet As String, VungCopy As String, CotCuoicung As Long
Dim DongCuoiCung As Long
LinkFileExcel = CurrentProject.Path & "\Test.xlsx"
TenSheet = "Sheet1"
'Lay so dong tren File Excel
Dim oEx As New Excel.Application ' oEx la bien oExcel
Dim oBook As Workbook ' set oBook la tap hop oExcel
' Mo file can chen
Set oBook = oEx.Workbooks.Open(LinkFileExcel)
' Mo Sheet can chen
Dim oSheet As Worksheet ' set oSheet la tap hop Sheet
Set oSheet = oBook.Worksheets(TenSheet)
' Dem so dong, so cot
With oSheet.UsedRange
DongCuoiCung = .Rows(UBound(.Value)).Row
End With
oBook.Close False
Set oEx = Nothing
VungCopy = "A7:L" & DongCuoiCung ' Bat cau copy tu cot A7 den cot L xxx
' Mo File Excel
Set RsExcel = CreateObject("ADODB.Recordset")
SQL = "select * from [" & TenSheet & "$" & VungCopy & "]"
Strcon = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & LinkFileExcel _
& ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;"";")
RsExcel.Open SQL, Strcon, 3, 1
''''''''''''
' Xoa du lieu trong tblTest
CurrentDb.Execute "DELETE * FROM tblTest", dbFailOnError 'Xoa tblTest
Dim i As Long
Dim RsAcc As New ADODB.Recordset
'Mo tblTest
RsAcc.Open "tblTest", CurrentProject.AccessConnection, _
adOpenstatic, adLockBatchOptimistic, adCmdTableDirect
'So dong moi lam UpdateBatch
Dim SoDongUpdateTrenLan As Integer, x As Integer
x = 0
SoDongUpdateTrenLan = 1000
With RsExcel
Debug.Print "Bat dau vong lap: ", Time
Do While Not .EOF ' Duyet mau tin tren Excel
RsAcc.AddNew
For i = 0 To (.Fields.Count - 1) ' Duyet mau tin tren Access
RsAcc.Fields(i).Value = .Fields.Item(i).Value
Next
.MoveNext
'So dong moi lam UpdateBatch
x = x + 1
If x = SoDongUpdateTrenLan Then ' 1000Dong Them 1 lan
RsAcc.UpdateBatch
Debug.Print "Thoi gian UpdateBatch", Time
x = 0
End If
Loop
Debug.Print "Ket thuc vong lap: ", Time
End With
MsgBox "Da Hoan thanh"
RsExcel.Close
Set RsExcel = Nothing
RsAcc.Close
Set RsAcc = Nothing
End Function
Chúc bạn vui khỏe
Dân ơi, chắc viết code nhanh qua nên không tính toán kỹ phải ko.
- Thứ nhất: là không cần phải mở Excel file lên để duyệt qua vùng cần import đâu. Kết nối ADO nó có thể tương tác và lấy dữ liệu luôn rồi. Bỏ việc mở file Excel sẽ tiết kiệm thời gian nhiều lần đó. Ngừoi dùng khi cần import file Excel thì cũng phải mở file đó lên, xem lại, chỉnh sửa nếu cần rồi mới import chứ đâu thể trỏ vô một file excel rồi kêu Access import. Lúc đó dữ liệu vô sai tè le. Do vậy khi chỉnh sửa file Excel , yêu cầu họ sẽ thiết lập luôn vùng muốn copy, khỏi phải code mở file lên duyệt.
Vd: demo của Dân import 8.000 dòng cần 5s thì khi bỏ việc mở file Excel thì chỉ cần 1.2s thôi.
![[Hình: 45903938981_b46b7e3f1f_o.png]](https://farm5.staticflickr.com/4889/45903938981_b46b7e3f1f_o.png)
- Thứ 2: Dùng UpdateBatch trong trường hợp này lại làm thời gian nó kéo dài thêm chút. Dân dùng vòng lặp chuyển Excel ADO Recordset (RsExcel) lên một cái Access ADO Recordset (RsAcc) -> UpdateBatch RsAcc vô table -> nó xử lý tốn thêm thời gian và bộ nhớ cho cái RsAcc mặc dù đã dùng Updatebatch. Cách anh dùng là chạy vòng lặp update thẳng cái RsExcel vô DAO recordset luôn. UpdateBatch dùng trong trường hợp này hơi bị quá tay
vì yêu cầu này chỉ cần import vào local Table của file Access đang xử lý thôi chứ không phải cập nhật lại cái "disconnected Recordset" lên một file .mdb khác hoặc lên SQL Server.