• Vui lòng đọc nội qui diễn đàn để tránh bị xóa bài viết
  • Tìm kiếm trước khi đặt câu hỏi

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
Hình đại diện của người dùng
truongphu
VIP
VIP
Bài viết: 4756
Ngày tham gia: CN 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 time
Been thanked: 509 time

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

Gửi bàigửi bởi truongphu » T.Bảy 31/05/2008 12:01 pm

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 hết

  1. Private Sub Command1_Click()
  2.     Call Ex2Ac(App.Path & "\Book1.xls")  '<-- Thay tên file Excel kèm Path thích ho'p
  3.     MsgBox "Done"
  4. End Sub
  5.  
  6. Sub Ex2Ac(ExcelPath$)
  7. 'Sree Kumar B.A, truongphu
  8.    Dim catNewDB As New ADOX.Catalog, recNew As New ADODB.Recordset, tbl As New Table
  9.    ' Tao file Access và kêt nôi vo'i nó
  10.    catNewDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\NewAccess.MDB"
  11.    catNewDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\NewAccess.MDB"
  12.     ' Kêt nôi file Excel
  13.    Dim cn As New ADODB.Connection, rec As New ADODB.Recordset, fld As ADODB.Field
  14.     cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= " & ExcelPath & ";Extended Properties=Excel 8.0;" & "Persist Security Info=False"
  15.     rec.Open "Select * from [Sheet1$]", cn, adOpenKeyset
  16.     ' Tên Table
  17.     tbl.Name = "Test"
  18.     ' Tùy sô côt và tên côt cua file Excel
  19.     For Each fld In rec.Fields
  20.          tbl.Columns.Append fld.Name, adVarWChar, 100   '<-- Thay Ðôi
  21.     Next
  22.     ' Tao Table nhu câu trúc file Excel
  23.         catNewDB.Tables.Append tbl
  24.     ' Mo' Table vu`a tao ra
  25.     recNew.Open "Test", catNewDB.ActiveConnection, adOpenKeyset, adLockOptimistic
  26.  
  27.     Do Until rec.EOF
  28.         With recNew
  29.              .AddNew
  30.             For Each fld In rec.Fields
  31.                 ' Công tác chép sô liêu tu` file Excel sang Access
  32.                 .Fields(fld.Name) = IIf(IsNull(rec(fld.Name)), "", rec(fld.Name))
  33.             Next
  34.             .Update
  35.         End With
  36.         rec.MoveNext
  37.     Loop
  38.    Set catNewDB = Nothing: Set recNew = Nothing: Set cn = Nothing
  39. End Sub
  40.  

Đã 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 người dùng
truongphu
VIP
VIP
Bài viết: 4756
Ngày tham gia: CN 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 time
Been thanked: 509 time

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

Gửi bàigửi bởi truongphu » T.Ba 03/06/2008 1:29 pm

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 hết

  1. Private Sub ExceltoAccess(ExcelPath$, AccessPath$)
  2. Const acImport = 0: Const acSpreadsheetTypeExcel9 = 8
  3. Dim objAccess As New Access.Application
  4. objAccess.NewCurrentDatabase AccessPath$
  5. objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, True
  6. End 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 hết

  1. Private Sub ExceltoAccess2(ExcelPath$, AccessPath$)
  2. Const acImport = 0: Const acSpreadsheetTypeExcel9 = 8
  3. Set objAccess = CreateObject("Access.Application")
  4. objAccess.NewCurrentDatabase AccessPath$
  5. objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, True
  6. End 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 hết

  1. Private Sub Form_Load()
  2. Call ExceltoAccess2("E:\win\desktop\Book1.xls", "E:\win\desktop\New.mdb")
  3. 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 người dùng
truongphu
VIP
VIP
Bài viết: 4756
Ngày tham gia: CN 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 time
Been thanked: 509 time

Access qua Excel

Gửi bàigửi bởi truongphu » T.Ba 03/06/2008 3:25 pm

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 hết

  1. Private Sub Access2Excel(AccessPath$, ExcelPath$)
  2. Const acExport = 1: Const acSpreadsheetTypeExcel9 = 8
  3. Dim objAccess As New Access.Application
  4. objAccess.OpenCurrentDatabase AccessPath$  ' Câu sau có Test là tên Table
  5. objAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, True
  6. End 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 hết

  1. Private Sub Access2Excel2(AccessPath$, ExcelPath$)
  2. Const acExport = 1: Const acSpreadsheetTypeExcel9 = 8
  3. Set objAccess = CreateObject("Access.Application")
  4. objAccess.OpenCurrentDatabase AccessPath$  ' Câu sau có Test là tên Table
  5. objAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Test", ExcelPath$, True
  6. End 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 hết

  1. Private Sub Form_Load()
  2. Call Access2Excel2("E:\win\desktop\new.mdb", "E:\win\desktop\new.xls")
  3. 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: T.Bảy 26/04/2008 6:10 pm
Has thanked: 16 time
Been thanked: 10 time

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

Gửi bàigửi bởi tindl88 » T.Ba 03/06/2008 7:32 pm

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 người dùng
truongphu
VIP
VIP
Bài viết: 4756
Ngày tham gia: CN 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 time
Been thanked: 509 time

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

Gửi bàigửi bởi truongphu » T.Ba 03/06/2008 8:30 pm

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 người dùng
GacKiem
Bài viết: 9
Ngày tham gia: T.Năm 29/05/2008 5:11 pm

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

Gửi bàigửi bởi GacKiem » T.Tư 04/06/2008 11:39 am

Ủ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 người dùng
NoBi
Quản trị
Quản trị
Bài viết: 946
Ngày tham gia: T.Ba 18/03/2008 1:22 pm
Đến từ: Sài Gòn
Has thanked: 50 time
Been thanked: 66 time
Liên hệ:

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

Gửi bàigửi bởi NoBi » T.Hai 09/06/2008 4:41 pm

Độ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 người dùng
truongphu
VIP
VIP
Bài viết: 4756
Ngày tham gia: CN 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 time
Been thanked: 509 time

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

Gửi bàigửi bởi truongphu » T.Hai 09/06/2008 6:06 pm

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

Mã: Chọn hết

  1. 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: CN 29/06/2008 12:24 pm
Has thanked: 2 time

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

Gửi bàigửi bởi dungcld75 » T.Sáu 07/11/2008 9:02 am

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 người dùng
truongphu
VIP
VIP
Bài viết: 4756
Ngày tham gia: CN 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 time
Been thanked: 509 time

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

Gửi bàigửi bởi truongphu » T.Sáu 07/11/2008 12:03 pm

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/viewtopic.php?f=7&t=3942#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: T.Bảy 17/12/2011 10:30 am
Has thanked: 5 time
Been thanked: 3 time

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

Gửi bàigửi bởi thang314 » T.Năm 02/02/2012 10:52 am

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: T.Bảy 17/12/2011 10:30 am
Has thanked: 5 time
Been thanked: 3 time

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

Gửi bàigửi bởi thang314 » T.Năm 02/02/2012 10:58 am

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: 247
Ngày tham gia: T.Tư 07/09/2005 4:02 pm
Đến từ: Hải Phòng
Has thanked: 1 time
Been thanked: 12 time
Liên hệ:

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

Gửi bàigửi bởi HaiPT » T.Bảy 04/02/2012 1:32 pm

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!
Phạm Hải
Quản trị dự án ,Chuyên gia đào tạo
Đại học FPT

nguyen nhu thanh
Bài viết: 1
Ngày tham gia: T.Hai 30/01/2012 9:49 am

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

Gửi bàigửi bởi nguyen nhu thanh » T.Tư 15/02/2012 9:49 am

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: T.Sáu 14/09/2012 10:32 am
Has thanked: 2 time

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

Gửi bàigửi bởi nguyenminhlong » T.Sáu 14/09/2012 10:46 am

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 hết

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 hết

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 người dùng
truongphu
VIP
VIP
Bài viết: 4756
Ngày tham gia: CN 04/11/2007 10:57 am
Đến từ: Cam Đức, Khánh hòa
Has thanked: 14 time
Been thanked: 509 time

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

Gửi bàigửi bởi truongphu » T.Sáu 14/09/2012 4:07 pm

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: T.Năm 18/10/2012 2:30 pm

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

Gửi bàigửi bởi haihoa » T.Năm 18/10/2012 3:44 pm

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


Quay về “[VB] Mẹo vặt khác”

Đang trực tuyến

Đang xem chuyên mục này: Không có thành viên nào trực tuyến.1 khách