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

[Ngô Hải Đăng]

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,[iDataHasColumnName],[iHeader],[iConnectType],[iConnectInfo])
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.

 

[Ngô Hải Đăng]

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.

 

[weddingnight]

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

 

[mrbl4c]

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 ạ. Thanks

 

[Maika8008]

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 ạ. ThanksView attachment 289326

Sao có ký tự @ ở đầu công thức vậy nhỉ? File ví dụ ở bài #1 có viết vậy đâu.

 

[mrbl4c]

Sao có ký tự @ ở đầu công thức vậy nhỉ? File ví dụ ở bài #1 có viết vậy đâu.

Mình không rõ tại sao nhưng thi thoảng 1 số công thức vẫn có, k rõ nguyên nhân ạ

 

[HeSanbi]

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

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