Chuyển file dữ liệu Excel sang file Access

Các mẹo vặt linh tinh khác, không thuộc nhóm nào
Đăng trả lời
Hình đại diện của thành viên
truongphu
VIP
VIP
Bài viết: 4785
Ngày tham gia: Chủ nhật 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 times
Been thanked: 530 times

Chuyển file dữ liệu Excel sang file Access

Gửi bài by truongphu »

Thủ thuật: Chuyển file dữ liệu Excel sang file Access
Tác giả: Sree Kumar gợi ý, truongphu
Mô tả: Trong CLBVB cũng có bài viết tương tự, bài viết mầy với code gọn nhẹ hơn. Nhớ thêm các thành phần sau:
Add Project\References:
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ADO Ext 2.8 for DDL and Security

Mã: Chọn tất cả

Private Sub Command1_Click()    Call Ex2Ac(App.Path & "\Book1.xls")  '<-- Thay tên file Excel kèm Path thích ho'p    MsgBox "Done"End Sub Sub Ex2Ac(ExcelPath$)'Sree Kumar B.A, truongphu   Dim catNewDB As New ADOX.Catalog, recNew As New ADODB.Recordset, tbl As New Table   ' Tao file Access và kêt nôi vo'i nó   catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\NewAccess.MDB"   catNewDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\NewAccess.MDB"    ' Kêt nôi file Excel   Dim cn As New ADODB.Connection, rec As New ADODB.Recordset, fld As ADODB.Field    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= " & ExcelPath & ";Extended Properties=Excel 8.0;" & "Persist Security Info=False"    rec.Open "Select * from [Sheet1$]", cn, adOpenKeyset    ' Tên Table    tbl.Name = "Test"    ' Tùy sô côt và tên côt cua file Excel    For Each fld In rec.Fields         tbl.Columns.Append fld.Name, adVarWChar, 100   '<-- Thay Ðôi    Next    ' Tao Table nhu câu trúc file Excel        catNewDB.Tables.Append tbl    ' Mo' Table vu`a tao ra    recNew.Open "Test", catNewDB.ActiveConnection, adOpenKeyset, adLockOptimistic     Do Until rec.EOF        With recNew             .AddNew            For Each fld In rec.Fields                ' Công tác chép sô liêu tu` file Excel sang Access                .Fields(fld.Name) = IIf(IsNull(rec(fld.Name)), "", rec(fld.Name))            Next            .Update        End With        rec.MoveNext    Loop   Set catNewDB = Nothing: Set recNew = Nothing: Set cn = NothingEnd Sub 
Đã chỉnh sửa lần 3, Loại bỏ thành phần thừa, chuyển *.XLS/XP sang *.MDB/XP tốt. Quá nhẹ nhàng phải không?
o0o--truongphu--o0o

.........
Ghé thăm:
Chuyện Linh Tinh
Hình đại diện của thành viên
truongphu
VIP
VIP
Bài viết: 4785
Ngày tham gia: Chủ nhật 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 times
Been thanked: 530 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by truongphu »

Sau đây là 2 function tuyệt vời để chuyển dữ liệu từ Excel sang Access với code gọn tối đa
Function tuyệt vời thứ nhất: Nhớ khai báo MS Access 10 hay 11 Object Library

Mã: Chọn tất cả

Private Sub ExceltoAccess(ExcelPath$, AccessPath$)Const acImport = 0: Const acSpreadsheetTypeExcel9 = 8Dim objAccess As New Access.ApplicationobjAccess.NewCurrentDatabase AccessPath$objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, TrueEnd Sub
Function tuyệt vời thứ hai: Khỏi khai báo, chép là chạy, đương nhiên máy phải có cài Access chuẩn

Mã: Chọn tất cả

Private Sub ExceltoAccess2(ExcelPath$, AccessPath$)Const acImport = 0: Const acSpreadsheetTypeExcel9 = 8Set objAccess = CreateObject("Access.Application")objAccess.NewCurrentDatabase AccessPath$objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, TrueEnd Sub
Giống nhau, chỉ khác bên là VBA có IDE hổ trợ, bên dùng VBS
Sử dụng: ví dụ dùng hàm thứ 2

Mã: Chọn tất cả

Private Sub Form_Load()Call ExceltoAccess2("E:\win\desktop\Book1.xls", "E:\win\desktop\New.mdb")End Sub
Đương nhiên Tập tin Excel trong đường dẫn phải có, còn đường dẫn Access KHÔNG CÓ TẬP TIN để function tạo MỚI!
(Nếu muốn dùng tập tin cũ, trong các function trên, xin thay hàm nhỏ .NewCurrentDatabase bằng .OpenCurrentDatabase)

Bài Chuyển file dữ liệu Excel sang file Access xứng đáng điểm 10/10 vì hàng "độc"
Mong được phản hồi vì gởi 4 ngày rồi mà vẫn lặng lẽ, buồn quá! :D ;) :( :((
o0o--truongphu--o0o

.........
Ghé thăm:
Chuyện Linh Tinh
Hình đại diện của thành viên
truongphu
VIP
VIP
Bài viết: 4785
Ngày tham gia: Chủ nhật 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 times
Been thanked: 530 times

Access qua Excel

Gửi bài by truongphu »

Thủ thuật: Access qua Excel
Tác giả: truongphu
Mô tả: Access qua Excel
Sau đây là 2 function tuyệt vời để chuyển dữ liệu từ Access sang Excel với code gọn tối đa
Function tuyệt vời thứ nhất: Nhớ khai báo MS Access 10 hay 11 Object Library

Mã: Chọn tất cả

Private Sub Access2Excel(AccessPath$, ExcelPath$)Const acExport = 1: Const acSpreadsheetTypeExcel9 = 8Dim objAccess As New Access.ApplicationobjAccess.OpenCurrentDatabase AccessPath$  ' Câu sau có Test là tên TableobjAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, TrueEnd Sub
Function tuyệt vời thứ hai: Khỏi khai báo, chép là chạy, đương nhiên máy phải có cài Access chuẩn

Mã: Chọn tất cả

Private Sub Access2Excel2(AccessPath$, ExcelPath$)Const acExport = 1: Const acSpreadsheetTypeExcel9 = 8Set objAccess = CreateObject("Access.Application")objAccess.OpenCurrentDatabase AccessPath$  ' Câu sau có Test là tên TableobjAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, TrueEnd Sub
Giống nhau, chỉ khác bên là VBA có IDE hổ trợ, bên dùng VBS
Sử dụng: ví dụ dùng hàm thứ 2

Mã: Chọn tất cả

Private Sub Form_Load()Call Access2Excel2("E:\win\desktop\new.mdb", "E:\win\desktop\new.xls")End Sub
Đương nhiên Tập tin Access trong đường dẫn phải có, còn đường dẫn Excel KHÔNG CÓ TẬP TIN để function tạo MỚI!
"Độc không?"
o0o--truongphu--o0o

.........
Ghé thăm:
Chuyện Linh Tinh
tindl88
Thành viên tâm huyết
Thành viên tâm huyết
Bài viết: 363
Ngày tham gia: Thứ 7 26/04/2008 6:10 pm
Has thanked: 16 times
Been thanked: 10 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by tindl88 »

Hay thật đấy :D
Code ngắn không tưởng.
Mà cháu thấy bác Phú có vẻ khoái món VBS nhỉ.
cứng nhắc...vớ vẩn
Hình đại diện của thành viên
truongphu
VIP
VIP
Bài viết: 4785
Ngày tham gia: Chủ nhật 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 times
Been thanked: 530 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by truongphu »

thank to bạn tindl88
Mặc dù nick của bạn mới
nhưng với câu nhận định:
tindl88 đã viết:Code ngắn không tưởng.
= quá chính xác!
Là tôi khoái bạn rồi (ai khen mình, nhất là khi mình đang ế khách, mà lại không khoái nhỉ?)
(Ghi chú:Trong Forum nầy, người thích code ngắn như thế chỉ 2 người: PhuongThanh37 (=sư phụ, tôi học phong cách nầy từ PT37), sau đó là anhtuyenbk có code đôi khi cũng ngắn gọn gớm!)
tindl88 đã viết:thấy bác Phú có vẻ khoái món VBS nhỉ.
Cảm ơn bạn, đúng thế, tôi cũng lớn tuổi. theo mấy hàm API "đỏ con mắt bên trái", "ngứa cả người..." luôn!
Học để vui, tại sao không chọn VBS?
Theo tôi biết VB6 -> VBA -> VBS và...-> tiệm cận VB.Net, vì rất nhiều hằng (VBS) dùng chung với Vb.Net (không dùng chung VB6!)
Tuy nhiên trên Forum nầy, VBS không được xem trọng (tuy rằng người ta vẫn dùng "vô tư" mà không biết"), bạn cứ lang thang trên Forum sẽ thấy nhiều code VBS!
Cũng nhờ thế, nên tôi tự phân công ? mình phụ trách mảng VBS :D :D
Còn Vb6, tôi chỉ mới qua sơ cấp, vui là chính! (đừng quên học)
TB: Mấy hàm nho nhỏ ở trên là "độc quyền cấp World", chỉ riêng tặng CLBVB! dùng thoải mái, không bản quyền
o0o--truongphu--o0o

.........
Ghé thăm:
Chuyện Linh Tinh
Hình đại diện của thành viên
GacKiem
Bài viết: 9
Ngày tham gia: Thứ 5 29/05/2008 5:11 pm

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by GacKiem »

Ủa bạn truongphu lớn tuổi lắm rồi à...
Cái này hay lắm, chưa thấy bao giờ nhưng mà có lẽ ít xài :D

Dù sao cũng cảm ơn bạn truongphu. =D>
Thiệt thà thẳng thắng thì thua thiệt
Lươn lẹo lọc lừa lại lên lương
Hình đại diện của thành viên
NoBi
Quản trị
Quản trị
Bài viết: 971
Ngày tham gia: Thứ 3 18/03/2008 1:22 pm
Đến từ: Sài Gòn
Has thanked: 57 times
Been thanked: 66 times
Tiếp xúc:

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by NoBi »

Độc hơn thịt vịt :)) :)) :)) Nhưng những người có nhu cầu mới thấy được giá trị của nó.
Mà ở bài viết thứ 2, đối với file excel có nhiều sheet thì làm sao chọn được sheet cần export nhỉ?.
:>
Hình đại diện của thành viên
truongphu
VIP
VIP
Bài viết: 4785
Ngày tham gia: Chủ nhật 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 times
Been thanked: 530 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by truongphu »

Hê! Mát ruột quá, bữa nào nời NoBi một chầu thịt vịt

Mã: Chọn tất cả

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, True, "Sheet2!"
o0o--truongphu--o0o

.........
Ghé thăm:
Chuyện Linh Tinh
dungcld75
Bài viết: 7
Ngày tham gia: Chủ nhật 29/06/2008 12:24 pm
Has thanked: 2 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by dungcld75 »

Chào pác Trường Phú.
đoạn code chuyển từ Access sang Excel của pác đưa lên quá tuyệt, sử dụng đã con mắt tuy nhiên xin pác chỉ thêm nếu database access có dùng password thi khai báo thế nào, xin chờ hồi âm của pác.
Hình đại diện của thành viên
truongphu
VIP
VIP
Bài viết: 4785
Ngày tham gia: Chủ nhật 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 times
Been thanked: 530 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by truongphu »

Cảm ơn bạn dungcld75 đã đọc và tán thưởng
Hiện tôi đang hoạt động ở mục thắc mắc vì học nhiều.
Tôi đang viết một chuyên đề tổng hợp (những điều vụn văt): Các câu hỏi và trả lời liên quan đến file *.MDB
http://www.caulacbovb.com/forum/viewtop ... 942#p24277
Mời bạn đọc qua, có phần password
o0o--truongphu--o0o

.........
Ghé thăm:
Chuyện Linh Tinh
thang314
Thành viên năng nổ
Thành viên năng nổ
Bài viết: 69
Ngày tham gia: Thứ 7 17/12/2011 10:30 am
Has thanked: 5 times
Been thanked: 3 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by thang314 »

nếu trong file excel co ca dữ liệu bị trùng trong access thì code thế nào bác phú ơi. em thấy code hay nhưng cho update thoải mái
thang314
Thành viên năng nổ
Thành viên năng nổ
Bài viết: 69
Ngày tham gia: Thứ 7 17/12/2011 10:30 am
Has thanked: 5 times
Been thanked: 3 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by thang314 »

nếu trong file excel co ca dữ liệu bị trùng trong access thì code thế nào bác phú ơi. em thấy code hay nhưng cho update thoải mái
HaiPT
VIP
VIP
Bài viết: 259
Ngày tham gia: Thứ 4 07/09/2005 4:02 pm
Has thanked: 3 times
Been thanked: 14 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by HaiPT »

NoBi đã viết:Độc hơn thịt vịt :)) :)) :)) Nhưng những người có nhu cầu mới thấy được giá trị của nó.
Mà ở bài viết thứ 2, đối với file excel có nhiều sheet thì làm sao chọn được sheet cần export nhỉ?.
Đúng là độc thật :)
:)>- BRAVO ... MR TruongPhu!
nguyen nhu thanh
Bài viết: 1
Ngày tham gia: Thứ 2 30/01/2012 9:49 am

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by nguyen nhu thanh »

cho em hỏi nếu mún chuyển dữ liệu từ exel hoặc word sang corel drow để in một bản A0 thi làm thế nào
nguyenminhlong
Bài viết: 1
Ngày tham gia: Thứ 6 14/09/2012 10:32 am
Has thanked: 2 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by nguyenminhlong »

Function tuyệt vời thứ hai: Khỏi khai báo, chép là chạy, đương nhiên máy phải có cài Access chuẩn

Mã: Chọn tất cả

Private Sub ExceltoAccess2(ExcelPath$, AccessPath$)Const acImport = 0: Const acSpreadsheetTypeExcel9 = 8Set objAccess = CreateObject("Access.Application")objAccess.NewCurrentDatabase AccessPath$objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, TrueEnd Sub
Giống nhau, chỉ khác bên là VBA có IDE hổ trợ, bên dùng VBS
Sử dụng: ví dụ dùng hàm thứ 2

Mã: Chọn tất cả

Private Sub Form_Load()Call ExceltoAccess2("E:\win\desktop\Book1.xls", "E:\win\desktop\New.mdb")End Sub
Đương nhiên Tập tin Excel trong đường dẫn phải có, còn đường dẫn Access KHÔNG CÓ TẬP TIN để function tạo MỚI!
(Nếu muốn dùng tập tin cũ, trong các function trên, xin thay hàm nhỏ .NewCurrentDatabase bằng .OpenCurrentDatabase)

Bài Chuyển file dữ liệu Excel sang file Access xứng đáng điểm 10/10 vì hàng "độc"
Mong được phản hồi vì gởi 4 ngày rồi mà vẫn lặng lẽ, buồn quá! :D ;) :( :(([/quote]

@: Chào a Phú và mọi người. Trước hết xin cảm ơn anh đã chia sẻ đoạn code rất hay. Em đã làm được việc chuyển đổi theo hướng dẫn tuy nhiên em đang có một thắc mắc là nếu như em muốn update thêm dữ liệu từ excel vào table trước đó (đã chuyển 1 lần: table có dữ liệu rồi). Giờ em muốn đưa thêm các trường mới vào, nếu trong table đã có trường trùng khóa thì em cần cập nhật giá trị lớn hơn. VD: cùng mã hàng hóa nhưng giá tăng và có thêm 1 số mặt hàng khác nên em muốn cập nhật vào table từ file excel. Nếu được nhờ mọi người hỗ trợ giúp vấn đề này với. Em cảm ơn!
Hình đại diện của thành viên
truongphu
VIP
VIP
Bài viết: 4785
Ngày tham gia: Chủ nhật 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 times
Been thanked: 530 times

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by truongphu »

nguyenminhlong đã viết:anh đã chia sẻ đoạn code rất hay. Em đã làm được việc chuyển đổi theo hướng dẫn tuy nhiên em đang có một thắc mắc là nếu như em muốn update thêm dữ liệu từ excel vào table trước đó (đã chuyển 1 lần: table có dữ liệu rồi). Giờ em muốn đưa thêm các trường mới vào, nếu trong table đã có trường trùng khóa thì em cần cập nhật giá trị lớn hơn. VD: cùng mã hàng hóa nhưng giá tăng và có thêm 1 số mặt hàng khác nên em muốn cập nhật vào table từ file excel.
1- Nếu Excel có thêm Field kèm dữ liệu mới, đoạn code trên = Excel2Access = tạo file mới là hay nhất.
2- Hoặc ít ra bạn có thể dùng tạo Table mới, lúc đó thay vì viết mở file mới:
objAccess.NewCurrentDatabase AccessPath$
ta lại viết:
AcApp.OpenCurrentDatabase AccessPath$
là mở file đang có
Kèm theo, bạn chỉ việc đổi tên Table để tạo Table mới.
3- Trường hợp bạn vẫn không thích tạo Table mới hay File mới, mà chỉ muốn Khai thêm Field và cập nhật các giá trị mới: có 2 cách
a- Làm bằng tay
b- Dùng vòng lặp duyệt Excel, Rồi dùng vòng lặp duyệt Access để Edit số liệu
o0o--truongphu--o0o

.........
Ghé thăm:
Chuyện Linh Tinh
haihoa
Bài viết: 3
Ngày tham gia: Thứ 5 18/10/2012 2:30 pm

Re: Chuyển file dữ liệu Excel sang file Access

Gửi bài by haihoa »

Bác có thể làm cái chuyển đổi từ excel sang txt và ngược lại được không?
Đăng trả lời

Quay về