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.

 

[befaint]

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.

 

[Maika8008]

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ú pháp hàm: =EXCELQUERY(iSQL,[iDataHasColumnName],[iHeader],[iFilePath])
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.
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.
iFilePath (String)	Nếu lấy dữ liệu từ file Excel khác thì nhập đường dẫn. Không nhập đường dẫn thì là file Excel hiện hành.

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.

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

 

[Ngô Hải Đăng]

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.

Bài đã được tự động gộp: 1/6/21

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.

 

[befaint]

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

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

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

 

[Ngô Hải Đăng]

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

Kiểu như vậy này.
View attachment 259830

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#.

 

[Pi-Pikachu]

Chủ đề hay quá, cảm ơn cậu.

 

[Nhattanktnn]

Thêm tham số dữ liệu đầu vào là file .csv nữa bạn

 

[truongvu317]

Code chạy đã quá, xem thế nào bạn cho nó là addin.

 

[Yeuvoyeucon]

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ú pháp hàm: =EXCELQUERY(iSQL,[iDataHasColumnName],[iHeader],[iFilePath])
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.
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.
iFilePath (String)	Nếu lấy dữ liệu từ file Excel khác thì nhập đường dẫn. Không nhập đường dẫn thì là 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.

Code hay nhìn thấy bóng dáng SQL trên excel

 

[Maika8008]

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

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

 

[Ngô Hải Đăng]

Thêm tham số dữ liệu đầu vào là file .csv nữa bạn

Đã cập nhật code bài #1. Bạn test thử xem.

Code chạy đã quá, xem thế nào bạn cho nó là addin.

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.

 

[FatBear]

wow.code hoành tráng quá. rất kì công và chi tiết. Cám ơn chủ thớt nhiều !

 

[FatBear]

Đã 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.

hóng bản add-in của bác. quá hay và hiệu quả !

 

[befaint]

quá hay và hiệu quả !

Lại có cả chấm than nữa!

Mình đã thử xóa các cells trong vùng kết quả xem có chuyện gì xảy ra chưa?

 

[FatBear]

Lại có cả chấm than nữa!

Mình đã thử xóa các cells trong vùng kết quả xem có chuyện gì xảy ra chưa?

ý bác là sao. kết quả sau khi được gán ra bảng tính hoàn toàn là value mà.

 

[befaint]

ý bác là sao. kết quả sau khi được gán ra bảng tính hoàn toàn là value mà.

Lại còn ý nào nữa. Bạn cứ đọc thật ---- chậm thôi và làm theo.
Ví dụ công thức trả về kết quả ở 20 ô, bạn xóa đại 3 ô kết quả đi (tất nhiên là không xóa ô công thức). Rồi xem kết quả sau khi xóa là gì, rồi đối chiếu với mục đích cuối cùng của hàm đó. Đơn giản thế thôi.

 

[FatBear]

Lại còn ý nào nữa. Bạn cứ đọc thật ---- chậm thôi và làm theo.
Ví dụ công thức trả về kết quả ở 20 ô, bạn xóa đại 3 ô kết quả đi (tất nhiên là không xóa ô công thức). Rồi xem kết quả sau khi xóa là gì, rồi đối chiếu với mục đích cuối cùng của hàm đó. Đơn giản thế thôi.

Ý bác là xóa đi, nhưng công thức không báo lỗi, cũng không có việc gì xảy ra khi xóa.
Còn riêng tôi thì thấy hàm hoàn toàn đáp ứng được mục đích truy vấn dữ liệu file Excel ở tiêu đề của bài viết.

 

[Ngô Hải Đăng]

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 đó.

 

[Pi-Pikachu]

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.

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?