Hàm tự tạo EXCELQUERY truy vấn dữ liệu file Excel

Chia sẻ bởi:hands

★★★★★

‹ Định nghĩa tên cột trong ADO Cập nhật thay đổi dữ liệu từ... ›

Quảng cáo

Công dụng: Truy vấn dữ liệu và trả kết quả trên file Excel thông qua câu lệnh SQL.
Yêu cầu: Biết sử dụng SQL để truy vấn dữ liệu trên Excel. (Có thể đọc qua loạt bài về ADO của anh Hai Lúa Miền Tây trên diễn đàn này).

#Cập nhật 02/06/2021
– Kết nối với file CSV và Access.

Cú pháp hàm: =EXCELQUERY(iSQL,,,,)
Giải thích các tham số:

iSQL (String)
Câu lệnh SQL. Nếu iDataHasColumnName = 0 thì dùng f1,f2,…,fn thay cho tên cột. Nếu iConnectType = 2 thì phải dùng tên cột.

iDataHasColumnName (Boolean)
Là 0 hoặc không nhập nếu dữ liệu không có tên cột, là 1 nếu dữ liệu có tên cột. Mặc định là 0.

iHeader (String)
Tiêu đề cột cách nhau bởi dấu .. (CỘT A..CỘT B..CỘT C). Mặc định là không có tiêu đề cột.

iConnectType (Byte)
Không nhâp hoặc 0: kết nối file Excel; 1: kết nối file CSV; 2: kết nối file Access. Mặc định là 0.

iConnectInfo (String)
Nếu iConnectType=0 hoặc 2 thì nhập dầy đủ đường dẫn bao gồm tên File; iConnectType=1 thì nhập đường dẫn đến Folder chứa File. Không nhập đường dẫn thì là Folder hoặc File Excel hiện hành.

Cập nhật thêm hàm FROM để bổ trợ cho câu lệnh SQL. Hàm FROM nhận tham số là Range và trả về chuỗi "_FROM [TênSheet$ĐịaChỉ]_", nếu Range là 1 ô thì trả về chuỗi "_FROM [TênSheet$]_". Copy code vào Module1.

Function FROM(iRange As Range) As String
  If Application.Caller.Parent.Parent Is iRange.Parent.Parent Then
    If iRange.CountLarge = 1 Then
      FROM = " FROM [" & iRange.Parent.Name & "$" & "] "
    Else
      FROM = " FROM [" & iRange.Parent.Name & "$" & iRange.Address(0, 0) & "] "
    End If
  End If
End Function

Thay vì viết "Select …. From…." thì viết thành "Select…."&FROM(iRange)

File ví dụ mình đính kèm bên dưới. Rất mong nhận được ý kiến đóng góp của mọi người.

www.giaiphapexcel.com/diendan/threads/h%C3%A0m-t%E1%BB%B1-t%E1%BA%A1o-excelquery-truy-v%E1%BA%A5n-d%E1%BB%AF-li%E1%BB%87u-file-excel.155857/

Khóa học SprinGO phù hợp

Xây dựng Lương 3P, KPI cho Doanh nghiệp

Làm thế nào để trả lương cho nhân viên chính xác nhất? Đây là một trong những câu hỏi khó trong quản trị nhân...

Xem khóa học

★★★★★ 5 ★ 1 👤 3 ▥ 0

‹ Định nghĩa tên cột trong ADO Cập nhật thay đổi dữ liệu từ Table... ›

Quảng cáo

Bạn nên đọc

3 Responses

hands says:

30/01/2023 at 6:03 pm

Cấu trúc một Workbook: Worksheets >> Range (Columns, Rows).

Không thấy tham số để gọi tới Worksheets thì có vẻ hơi thiêu thiếu rồi.

Do nó nằm luôn trong câu truy vấn SQL nên em không để tham số này.

Thấy từ hàm UDF trước nhưng quên nhắc: trong Workbook_SheetChange thừa dòng Debug.Print rX.Address

Bài này mình bỏ đoạn đó ra rồi.

Có 2 chỗ trong đó á bạn!
2673

Hàm này thực chất không phải hàm mảng mặc dù nó trả ra mảng kết quả. Nó chỉ lợi dụng sự kiện calculate của worksheet để gọi 1 sub chạy sau đó.

Nên để tham số cho tên Worksheet thì hay hơn.

Kiểu như vậy này.
2672

Nếu để tên Worksheet thì bị vướng khi kết hợp dữ liệu từ 2 sheet của cùng một file nên em viết thêm 1 hàm FROM để khi Select bảng nào thì sẽ truyền tham số cho hàm FROM. Cập nhật trên bài 1#.

Thêm tham số dữ liệu đầu vào là file .csv nữa bạn.
Code chạy đã quá, xem thế nào bạn cho nó là addin.

Đã cập nhật code bài #1. Bạn test thử xem.
Addin thì phải chỉnh lại một chút trong Class Module vì code hiện tại chỉ bắt sự kiện trên Workbook.

Reply
hands says:

30/01/2023 at 6:04 pm

Bạn ơi với câu lệnh này:
=EXCELQUERY("SELECT *"&FROM(Data!A2)&"WHERE khach_hang like'%"&D8&"%'",1,"SỐ TỜ KHAI..NGÀY ĐĂNG KÝ..KHÁCH HÀNG..INV..USD")
thì sẽ trả về theo khách trong ô D8 ?
Vậy khi tôi muốn kết quả trả về cho vài chục khách hàng trong một danh sách hàng trăm khách hàng thì sẽ xử lý thế nào vậy bạn hoặc giả sử các khách hàng tôi muốn lấy dữ liệu sẽ liệt kê trong vùng A8:A100 thì sẽ xử lý thế nào vậy bạn?

Nếu ít thì Where khach_hang in ('a','b','c'). Nhiều thì Where khach_hang in (select * from [tên sheet$A7:A100]). Trong đó ô A7 là tiêu đề, từ A8 đến A100 là danh sách khách hàng. Bạn tìm hiểu thêm về các lệnh truy vấn sql là sẽ thấy thôi.
Thấy code hay quá xin phép mình cập nhật một số nội dung cho bạn nào cần:

– Bỏ iDataHasColumnName mặc định là sẽ có tên cột
– Bỏ iConnectType, nếu có iConnectInfo thì sẽ lấy theo iConnectInfo nếu không là lấy bản thân file excel
– Sửa hàm FROM bỏ "from" để viết được câu lệnh sql phức tạp hơn: join, union
– Bổ sung khả năng xóa dòng (không phải clear) dữ liệu cũ và insert dòng dữ liệu mới, tạo dữ liệu động, thiết kế khung báo cáo 1 lần có thay đổi dữ liệu sẽ tự động co dãn

=> Tự test nha

Reply
hands says:

22/04/2023 at 8:59 am

Cho mình hỏi chút, mình copy modul + Class Modul vào để tạo file add in nhưng hàm này chạy lại cho ra kết quả sai, các bạn giúp mình tạo file add in để dùng với ạ. Thanks5213

Trong hàm Query tác giả sử dụng giải thuật gán lại hàm sau khi có kết quả, Nếu mã chạy ở phiên bản Excel mới với @ (Toán tử giao nhau)
Phương thức Range.Formula ở phiên bản cũ chính là Range.Formula2 ở phiên bản mới. Range.Formula ở phiên bản mới tự động thêm toán tử @.

Phải thêm mã phù hợp để bỏ qua lỗi này. Trước hết là tìm phiên bản Excel. Và với mã gán như sau:
```
If NewExcelVersion Then
   [Range].Formula2 = "=EXCELQUERY(...)"
Else
   [Range].Formula = "=EXCELQUERY(...)"
End if
```
Nếu các bạn có thời gian có thể tham khảo thêm hàm Query dưới đây

http://www.giaiphapexcel.com/diendan/threads/164425/

Reply