Xuất dử liệu từ Access ra Excel và ngược lại

[Nguyễn Duy Tuân]

Dùng A-Tools để liên kết dữ liệu từ Access sang Excel

Dựa vào cách làm công thức tại sheet "Baocao" của bác ThuNghi, tôi làm cho bạn bằng công cụ A-Tools với mục đích liên kết dữ liệu từ Access sang Excel và tự động tạo Name/Tên khi dữ liệu ngồn thay đổi.

Việc của bạn là phải cài A-Tools và tạo DBKEY là BT trước khi mở file "Baocao.xls"

Dữ liệu trên báo cáo Excel sẽ tự động cập nhật nếu dữ liệu trong file Access "NPL-Ptm.mdb" thay đổi.

Bạn cần đọc và làm theo file "Huong dan su dung truoc khi dung.rtf"

 

[ThuNghi]

nhập liệu trên excel không tốt lắm, đôi khi mình phải dò tên NPL rồi nhập dm,tlhh nếu 1 style thì cố gắng cũng được đằng này quá nhiều style, mỗi lần nhập thấy nhức mắt.
Từ lúc tạo bảng trong access mình thấy nhập liệu rất thoải mái.

Theo tôi nếu chỉ đơn giản như thế thì Excel dùng vô tư. Tôi cũng đã làm công việc như bạn với # 1000 style, mỗi style # 4 NL.
- Bảng định mức thì bắt buộc phải có theo dạng
Style---MaNL---TkHh---DinhMuc
- DMNL:
MaNL---Item
- DMStyle
- Biến lúc này chỉ có: bạn chỉ cần nhập bảng này thôi.
Contract---Style---Qty
Nếu bạn biết chút ít về VBA thì rất dễ trong việc TH, còn không dùng công thức. Chỉ cần nhập số Contact là sẽ có báo cáo.

 

[ngocmaipretty]

Xin mạn phép mấy cao thủ các bác, tôi nhận được tin nhắn của Binhtam:

trong bài lấy data trong access,tạo báo cáo ở excel.
Nếu gọi sưnương thì ngocmaipretty hướng dẫn get external query
Nay mình muốn nhận hướng từ sưnương hay suphụ PTM cũng được
cho đến giờ nay, mình thấy giải pháp của sưphụ PTM là hay nhất.
có gì sưnương gửi qua mail của mình nha : greenmaple@vnn.vn

cho mình gửi lời thăm đến sưphụ PTM

rất tiếc tôi không mail được cho BinhTam, và xét thấy nếu trả lời chung trong topic này cũng không đến nỗi nào (đối với BinhTam); nên tôi xin chen vào hướng dẫn BinhTam thực hành bài này. Thực sự tôi cũng mới đọc loạt bài của Anh Ptm thấy hữu dụng nhưng chưa áp dụng thực tế lần nào. Nhưng đối với bài này tôi có thể làm được. Hơn nữa ngoài Binh Tam ra có thể một số thành viên mới cũng muốn thực hành. Ngoài ra post bài ở đây mới có thể chèn hình minh hoạ.

To đệ tử BinhTam: Riêng đối với sư phụ Ptm, đệ tử phải tự liên hệ, sư nương cũng chỉ học sư phụ Ptm qua GPE thôi.

Dùng lại file của sư phụ Ptm (đúng ra là thái sư phụ), đệ tử phải làm từng bước như sau:

1. Mở file Access (của sư phụ Ptm, đừng lấy file gốc):

• mở table SlSX dưới dạng design mode, thêm vào 1 field LoaiTP (loại thành phẩm) rồi đệ tử thêm dữ liệu quần nữ, áo nữ .... cho các mã hàng.
• Trong table Dmuc (định mức), đệ tử có thể thêm vào field tenNVL theo thực tế, cái này để theo dõi riêng thôi chứ không đưa vào form 5a, vì form 5a bị ràng buộc tên NVT theo hợp đồng đã đăng ký với hải quan.
• Đóng file Access lại.

2. Mở file Excel cũng của sư phụ Ptm

• Xóa sạch dữ liệu trong sheet, không phải xóa cột nha. nếu thông báo gì thì cũng cứ yes.
• Mở menu Data - Import External Data - New Database Query hiện ra hộp thoại:

• Chọn MS Access Database, OK, hiện hộp thoại tiếp theo:

• Chọn file Access hồi nãy, OK, hiện tiếp:

• Chọn table SlSX, click Add, click Close
• Trong cửa sổ tiếp theo, kéo thả lần lượt các field vào vùng lưới bên dưới.

• Mở menu File chọn Return Data to Microsoft Excel.
• Trong hộp thoại hiện ra chọn cell A1

Kết quả được 1 query đầu tiên:

 

[ngocmaipretty]

Bước 2: tạo query thứ 2:
Đệ tử BinhTam phải làm chính xác nha.

- Vào lại Data - Import External Data - New Database Query
- Chọn file Access NVL.mdb
- Lần này chọn và add 2 table DMNVL và Dmuc

- dùng chuột kéo field MaNL của DMNVL vào field MANPL của Dmuc để tạo mối liên kết. Click đôi vào mối liên kết hiện ra hộp thoại chọn loại liên kết, chọn loại 2, Add, Close Kết quả mối liên kết phải có hình mũi tên như sau:

- Dùng chuột kéo thả các field theo thứ tự vào vùng lưới. Muốn sort theo field nào, để dấu nháy trong field đó và nhấn nút sort trên tool bar. Ở đây là field Style.

- Riêng tỷ lệ hao hụt thì vào menu Record - Add column hiện 1 hộp thoại và gõ vào như sau và click Add, rồi Close:

Kết quả trong Query Windows:

- Vào menu - File - Return data to Microsoft Excel
- chọn cell E1
Kết quả query thứ 2:

 

[ngocmaipretty]

Bước 3: Xem lại sheet báo cáo:
Có thể thứ tự thứ tự field của Query không giống như cũ nên sheet báo cáo không đúng. Đệ tử BinhTam phải sửa lại công thức lại, chỉ là lookup và sumproduct thôi.
Ngoài ra còn loại TP, cell E3, H3, K3... đang là mã style, thì ở cell D3, G3, J3 sẽ là tên style (quần , áo ...)

Công thức tại E3 là:
=VLOOKUP(E3;Query!$A$2:$C$19;3;0)
Copy vào các cell liên quan

Công thức tại D7 là:
=SUMPRODUCT((Query!$F$2:$F$164=$A7)*(Query!$E$2:$E$164=E$3)*Query!$H$2:$H$164)
Fill xuống

Công thức tại E7 là
=SUMPRODUCT((Query!$F$2:$F$164=$A7)*(Query!$E$2:$E$164=E$3)*Query!$I$2:$I$164)
Fill xuống

Sau đó copy D7:E44 paste vào các style còn lại.

To BinhTam: như vậy là cặn kẽ lắm rồi, đệ tử mà làm không được nữa thì mang tiếng sư nương lắm đó.

 

[ptm0412]

Riêng đối với sư phụ Ptm, đệ tử phải tự liên hệ, sư nương cũng chỉ học sư phụ Ptm qua GPE thôi.

Cám ơn ngocmai, BinhTam đã liên hệ với tôi trước rồi:

Chúc bạn luôn vui vẻ và thành công.

Chúc BinhTam tiến bộ sau các bài của Ngocmaipretty

 

[BinhTam]

access-excel

Hi Mr.Tuan,

File a-tools hiện tại bị lỗi gì đó nên download bi lỗi.

Rất mong nhận file a-tool của bạn qua email : greenmaple@vnn.vn

Bạn cho mình hỏi với công cụ a-tool có bị giới hạn sử dụng hay ko?
hay bạn viết code tích hợp vào luôn để khỏi bị hết hạn sử dụng.

Dựa vào cách làm công thức tại sheet "Baocao" của bác ThuNghi, tôi làm cho bạn bằng công cụ A-Tools với mục đích liên kết dữ liệu từ Access sang Excel và tự động tạo Name/Tên khi dữ liệu ngồn thay đổi.

Việc của bạn là phải cài A-Tools và tạo DBKEY là BT trước khi mở file "Baocao.xls"

Dữ liệu trên báo cáo Excel sẽ tự động cập nhật nếu dữ liệu trong file Access "NPL-Ptm.mdb" thay đổi.

Bạn cần đọc và làm theo file "Huong dan su dung truoc khi dung.rtf"

 

[BinhTam]

access-excel

khi làm bước 2, máy tính yêu cầu bỏ đĩa office vao.
Cam on sưnuong đã hướng dẫn.

Bước 3: Xem lại sheet báo cáo:
Có thể thứ tự thứ tự field của Query không giống như cũ nên sheet báo cáo không đúng. Đệ tử BinhTam phải sửa lại công thức lại, chỉ là lookup và sumproduct thôi.
Ngoài ra còn loại TP, cell E3, H3, K3... đang là mã style, thì ở cell D3, G3, J3 sẽ là tên style (quần , áo ...)

Công thức tại E3 là:
=VLOOKUP(E3;Query!$A$2:$C$19;3;0)
Copy vào các cell liên quan

Công thức tại D7 là:
=SUMPRODUCT((Query!$F$2:$F$164=$A7)*(Query!$E$2:$E$164=E$3)*Query!$H$2:$H$164)
Fill xuống

Công thức tại E7 là
=SUMPRODUCT((Query!$F$2:$F$164=$A7)*(Query!$E$2:$E$164=E$3)*Query!$I$2:$I$164)
Fill xuống

Sau đó copy D7:E44 paste vào các style còn lại.

To BinhTam: như vậy là cặn kẽ lắm rồi, đệ tử mà làm không được nữa thì mang tiếng sư nương lắm đó.

 

[Nguyễn Duy Tuân]

Hi Mr.Tuan,

File a-tools hiện tại bị lỗi gì đó nên download bi lỗi.

Rất mong nhận file a-tool của bạn qua email : greenmaple@vnn.vn

Bạn cho mình hỏi với công cụ a-tool có bị giới hạn sử dụng hay ko?
hay bạn viết code tích hợp vào luôn để khỏi bị hết hạn sử dụng.

Chỉ mỗi việc bạn vào trang web http://www.bluesofts.net/Products/AddIns/A-Tools/Index.htm
đọc và download thôi mà khó khăn vậy?

Tất cả các bài trên mọi người đã giúp bận rất tận tình rồi, đã làm sẵn cho bạn chỉ việc bấm nút "Print" thôi mà sao vẫn còn thắc mắc? Có lẽ bạn phải gắng tự mình làm thêm chút cho vừa ý đi.

 

[thanh.24.vn]

Muốn lập bảng chiết tính từ acces & sau đó lấy qua excel

Mình lập dự toán và muốn dơn giản hóa các khâu lấy chiết tính, mình muốn lập một bảng chiết tính từ Access và sau đó lấy qua Excel.
Bạn có cách nào thì chỉ giùm mình với.

 

[anhphuong]

Mình lập dự toán và muốn dơn giản hóa các khâu lấy chiết tính, mình muốn lập một bảng chiết tính từ Access và sau đó lấy qua Excel.
Bạn có cách nào thì chỉ giùm mình với.

Bạn dùng ngay chức năng trong access để đưa ra Excel từ một table hoặc một query. Dùng macro hoặc VBA tùy bạn
Dùng câu lệnh TransferSpreadsheet để chuyển. Có gì thắc mắc liên hệ với mình
Thân

 

[BinhTam]

xuất table từ access to excel bị báo lỗi

Chào các bạn,

Mình xuất dữ liệu 1 table đến file excel thì ok!

Tuy nhiên mình muốn xuất thêm 1 bảng nữa thì báo lỗi

Mong nhận được trợ giúp của các bạn

Cảm ơn

 

[ptm0412]

Bỏ tên sheet trong dòng lệnh, chỉ để range A:A hoặc F:F, tên sheet để Access tự đặt theo tên file thì OK.

 

[BinhTam]

- Em muốn dữ liệu xuất vào chung 1 sheet"tkn" theo cell(ô) chỉ định để tránh tạo ra nhiều sheet thì bị báo lỗi

Bỏ tên sheet trong dòng lệnh, chỉ để range A:A hoặc F:F, tên sheet để Access tự đặt theo tên file thì OK.

 

[ptm0412]

Xem lại rồi, tên sheet không phải nguyên nhân, mà trước khi export phải clear, và clear sạch cả comment,
Nếu chỉ clearcontent thì bị lỗi.
Để tên sheet hay không cũng chỉ export được 1 lần. (mỗi lần 2 table)

 

[BinhTam]

1.) Lúc đầu em để số ô ít quá nên báo lỗi, em để từ "a2:h1000" và "k2:v1000" thì em làm được rồi

2.) Mỗi lần em tạo thêm 1 nút lệnh xuất như em đã đưa thì em phải di chuyển sheet đến bên trái ngoài cùng thì ok. Còn nếu nó đứng ở vị trí cố định thì dữ liệu xuất ra sẽ đè lên sheet nào nằm ngoài cùng bên trên trái.

anh cung cấp em thêm giải pháp thêm nút xuất dữ liệu.

Cứ bỏ tên sheet trong dòng lệnh. Sao chưa thử mà biết không được? Tên sheet phải giống tên file.
Đổi tên sheet hoặc đổi tên file tuỳ ý, file có thể nhiều sheet, nhưng sheet đó phải ngoài cùng bên trái.

 

[ptm0412]

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "details", "d:\p2.xls", , "tkn!A:A"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "import", "d:\p2.xls", , "tkn!K:K"
End Sub

Private Sub Command6_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "tkxde", "d:\p2.xls", , "tkx!a:a"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "tkx", "d:\p2.xls", , "tkx!k:k"
End Sub

Sub clearxls()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Const conMAX_ROWS = 20000
Dim conSHT_NAME(1 To 2) As String
conSHT_NAME(1) = "tkn"
conSHT_NAME(2) = "tkx"
Const conWKB_NAME = "d:\p2.xls"
Set objXL = New Excel.Application
With objXL
Set objWkb = .Workbooks.Open(conWKB_NAME)
For i = 1 To 2
Set objSht = objWkb.Worksheets(conSHT_NAME(i))
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol + 10)).Clear
End With
Next
End With
objXL.Workbooks.Close
objXL.Application.Quit
 
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
 
End Sub

- gộp cmd0_click và cmd4_click
- gộp cmd6_click và cmd7_click
- xoá thêm sheet tkx trước khi export.
- phùuuuuuuuuuuuù

 

[BinhTam]

cảm ơn a.PTM

em sẽ về test code bên dưới của anh.

Chúc anh cuối tuần vui vẻ!

Thanks!
BìnhTâm

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "details", "d:\p2.xls", , "tkn!A:A"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "import", "d:\p2.xls", , "tkn!K:K"
End Sub

Private Sub Command6_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "tkxde", "d:\p2.xls", , "tkx!a:a"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel11, "tkx", "d:\p2.xls", , "tkx!k:k"
End Sub

Sub clearxls()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Const conMAX_ROWS = 20000
Dim conSHT_NAME(1 To 2) As String
conSHT_NAME(1) = "tkn"
conSHT_NAME(2) = "tkx"
Const conWKB_NAME = "d:\p2.xls"
Set objXL = New Excel.Application
With objXL
Set objWkb = .Workbooks.Open(conWKB_NAME)
For i = 1 To 2
Set objSht = objWkb.Worksheets(conSHT_NAME(i))
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol + 10)).Clear
End With
Next
End With
objXL.Workbooks.Close
objXL.Application.Quit
 
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
 
End Sub

- gộp cmd0_click và cmd4_click
- gộp cmd6_click và cmd7_click
- xoá thêm sheet tkx trước khi export.
- phùuuuuuuuuuuuù

 

[cadafi]

Import dữ liệu từ excel vào access bị lỗi không biết lý do

Chào các anh chị,
Em đang làm chương trình nhập liệu kế toán cho công ty, sử dụng access làm cơ sở dữ liệu, excel làm màn hình nhập liệu. Sau đó thực thi kết nối DSN với access để đưa dữ liệu từ excel vào access (theo như hướng dẫn của anh Duyệt).
Tuy nhiên, file của em khi import dữ liệu từ sheet("GLINVC") thì ok (module AExportInvoice) nhưng khi chạy thủ tục import dữ liệu từ sheet("GLDATA") (Module AExportData) thì nó báo lỗi thế này:

Run-time error '-2147217900 (80040e14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

Khi vào debug thì nó báo lỗi dòng này:

TargetDetail.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, gcnAccess

Thiệt tình em cũng không hiểu, vì em làm y như thủ tục import invoice, chỉ thay đổi tham số rngHeader, rngDetails và tblName thôi, nhưng nó không chạy.

Các anh xem giúp em nhé. Em có đính kèm hai file.
Phải đăng ký DSN cho file access AMGSYS.mdb trước, sau đó mở file excel lên coi giúp em nhé (code trong module AExportData).
UserID: admin
PW: admin.

Thanks in advance.

 

[cadafi]

EM bẫy lỗi thử q dòng dữ liệu thì biến rcdDetail nó cho ra cú pháp đúng, nhưng không hiểu tại sao nó lại báo lỗi như thế khi import vào bảng GLDATA.