Dùng SQL trên Excel

[hoahuongduong1986]

Kính gửi Anh chị,
Em có File sưu tầm trên mạng dùng SQL - Coi như Table là một Database và sẽ dùng SQL để lọc, tính toán...như trên SQLSERV thì làm thế nào ạ. Em đọc Code mà chưa hiểu cách chạy. Mong anh chị chỉ giúp ạ (Bỏ qua việc cài đặt Add in có sẵn ạ).

 

[ptm0412]

Có 1 cách dùng câu lệnh SQL, đó là dùng ADO liên kết. Cụ thể là topic https://www.giaiphapexcel.com/diendan/threads/ADO-Excel-Ketoan.25759/
Một cách khác tương tự SQL query đó là Power Query

 

[Maika8008]

Kính gửi Anh chị,
Em có File sưu tầm trên mạng dùng SQL - Coi như Table là một Database và sẽ dùng SQL để lọc, tính toán...như trên SQLSERV thì làm thế nào ạ. Em đọc Code mà chưa hiểu cách chạy. Mong anh chị chỉ giúp ạ (Bỏ qua việc cài đặt Add in có sẵn ạ).

Bạn làm như sau để chạy:

'Khai bao bien
Dim cn as Object, sql as String
Dim FPath As String, FName As String
'FPath và FName là đường dẫn đầy đủ và tên của file dữ liệu nguồn, dùng cho câu .ConnectionString bên dưới

'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FPath & "\" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With

'Cau lenh SQL cua ban:
sql = "SELECT MAX(NVL.[hh_ten]) AS TENHH "
'....
'....
sql = sql & "FROM (([nhapxuat_vatlieu_chitiet$] NXCT "

sql = sql & "INNER JOIN [nhapxuat_vatlieu$] NX ON NXCT.[nxct_nx_id]=NX.[nx_id]) "
sql = sql & "INNER JOIN [nguyenvatlieu$] NVL ON NXCT.[nxct_hh_id]=NVL.[hh_id]) "

sql = sql & "GROUP BY NXCT.nxct_hh_id"

'Truy van, ghi ket qua ra trang tinh
Set rs = cn.Execute(sql)
Sheet1.Range("A1").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban

 

[hoahuongduong1986]

Bạn làm như sau để chạy:

'Khai bao bien
Dim cn as Object, sql as String
Dim FPath As String, FName As String
'FPath và FName là đường dẫn đầy đủ và tên của file dữ liệu nguồn, dùng cho câu .ConnectionString bên dưới

'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FPath & "\" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With

'Cau lenh SQL cua ban:
sql = "SELECT MAX(NVL.[hh_ten]) AS TENHH "
'....
'....
sql = sql & "FROM (([nhapxuat_vatlieu_chitiet$] NXCT "

sql = sql & "INNER JOIN [nhapxuat_vatlieu$] NX ON NXCT.[nxct_nx_id]=NX.[nx_id]) "
sql = sql & "INNER JOIN [nguyenvatlieu$] NVL ON NXCT.[nxct_hh_id]=NVL.[hh_id]) "

sql = sql & "GROUP BY NXCT.nxct_hh_id"

'Truy van, ghi ket qua ra trang tinh
Set rs = cn.Execute(sql)
Sheet1.Range("A1").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban

Mình thử mà không được ? Không rõ mình còn thiếu Refer đến thư viện nào không mà không chạy được ? Với mình hỏi là mình không muốn câu lệnh SQL nó cố định trong đoạn VBA mà khi mình gõ đoạn code đó tại A1 của Sheet4 thì nó chạy ?

 

[hoahuongduong1986]

Mình thử mà không được ? Không rõ mình còn thiếu Refer đến thư viện nào không mà không chạy được ? Với mình hỏi là mình không muốn câu lệnh SQL nó cố định trong đoạn VBA mà khi mình gõ đoạn code đó tại A1 của Sheet4 thì nó chạy ?

.

 

[vanaccex]

Mình thử mà không được ? Không rõ mình còn thiếu Refer đến thư viện nào không mà không chạy được ? Với mình hỏi là mình không muốn câu lệnh SQL nó cố định trong đoạn VBA mà khi mình gõ đoạn code đó tại A1 của Sheet4 thì nó chạy ?

Đoạn sub này của bạn sửa thành thế này

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
sql = "SELECT * FROM RangeName WHERE Reporter='Albania'"

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

 

[hoahuongduong1986]

Đoạn sub này của bạn sửa thành thế này

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
sql = "SELECT * FROM RangeName WHERE Reporter='Albania'"

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

Cảm ơn Vân. Vân cho mình hỏi là mình muốn linh hoạt trong câu lệnh SQL nên muốn cho nó đoạn lênh SQL vào ô A1 để tiện. Mình đã sửa như File mà không được. Vân xem giúp mình với nhé !

 

[vanaccex]

Cảm ơn Vân. Vân cho mình hỏi là mình muốn linh hoạt trong câu lệnh SQL nên muốn cho nó đoạn lênh SQL vào ô A1 để tiện. Mình đã sửa như File mà không được. Vân xem giúp mình với nhé !

Bạn thử bằng sub này xem

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'sql = "SELECT * FROM RangeName WHERE Reporter='Albania' and Partner='World'"
sql = Sheet4.Range("A1").Value

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

 

[hoahuongduong1986]

Bạn thử bằng sub này xem

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'sql = "SELECT * FROM RangeName WHERE Reporter='Albania' and Partner='World'"
sql = Sheet4.Range("A1").Value

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

Giỏi quá, đã được rồi ! Cảm ơn sự trợ giúp của Vân !

 

[hoahuongduong1986]

Bạn thử bằng sub này xem

Sub sql_1()

'Khai bao bien
Dim cn As Object, sql As String
Dim FPath As String, FName As String
'FPath và FName là du?ng d?n d?y d? và tên c?a file d? li?u ngu?n, dùng cho câu .ConnectionString bên du?i
FName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
'Ket noi du lieu nguon
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & FName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
.Open
End With
'sql = "SELECT * FROM RangeName WHERE Reporter='Albania' and Partner='World'"
sql = Sheet4.Range("A1").Value

Set rs = cn.Execute(sql)
Sheet4.Range("A2").CopyFromRecordset rs 'Thay doi bang vi tri chep ket qua cua ban
End Sub

Cho mình hỏi nốt chút với Vân, mình chạy thử SQL các kiểu thì ổn rồi, nhưng làm sao để lấy được tiêu đề của nó nhỉ, tiêu đề cho vào dòng 2 ấy.

 

[hoahuongduong1986]

Có 1 cách dùng câu lệnh SQL, đó là dùng ADO liên kết. Cụ thể là topic https://www.giaiphapexcel.com/diendan/threads/ADO-Excel-Ketoan.25759/
Một cách khác tương tự SQL query đó là Power Query

Em chào Anh. Nhờ Anh xem giúp em vấn đề Code SQL theo File với ạ.
- Tại A1 (Em tạo 2 mã SQL): Mã 1 chạy nhưng không lấy được tiêu đề làm sao để có được tiêu đề ạ; Mã 2 em không chay ra được kết quả là vì sao ạ và làm sao để lấy được tiêu đề ạ ? Mong anh giúp đỡ ạ.

 

[Kiều Mạnh]

Em chào Anh. Nhờ Anh xem giúp em vấn đề Code SQL theo File với ạ.
- Tại A1 (Em tạo 2 mã SQL): Mã 1 chạy nhưng không lấy được tiêu đề làm sao để có được tiêu đề ạ; Mã 2 em không chay ra được kết quả là vì sao ạ và làm sao để lấy được tiêu đề ạ ? Mong anh giúp đỡ ạ.

Qua thớt kìa tải cái Addin của mình về thứ xem sao

 

[hoahuongduong1986]

Qua thớt kìa tải cái Addin của mình về thứ xem sao

A xem giúp em Code em chỗ nào chưa ổn mà không có Tiêu đề hoặc/và không ra kết quả ạ.

 

[ptm0412]

Em chào Anh. Nhờ Anh xem giúp em vấn đề Code SQL theo File với ạ.
- Tại A1 (Em tạo 2 mã SQL): Mã 1 chạy nhưng không lấy được tiêu đề làm sao để có được tiêu đề ạ; Mã 2 em không chay ra được kết quả là vì sao ạ và làm sao để lấy được tiêu đề ạ ? Mong anh giúp đỡ ạ.

- Tiêu đề phải copy từ Data sang hoặc điền tay. Đáng lẽ khai báo ConnectionString có HDR=YES thì phải có Header nhưng hiện tại không thấy chạy
- Mã 2 có tính Sum nhưng thiếu Group By những field không tính toán. Sửa như sau:
SELECT Distinct [Partner],SUM([Trade Value (US$)]) As [Tongcong] FROM RangeName Group By [Partner]

Nếu lấy 3 cột thì phải Group By 2 cột không tính toán:

SELECT Distinct [Partner],[Commodity],SUM([Trade Value (US$)]) As [Tongcong] FROM RangeName Group By [Partner],[Commodity]

 

[ongke0711]

Em chào Anh. Nhờ Anh xem giúp em vấn đề Code SQL theo File với ạ.
- Tại A1 (Em tạo 2 mã SQL): Mã 1 chạy nhưng không lấy được tiêu đề làm sao để có được tiêu đề ạ; Mã 2 em không chay ra được kết quả là vì sao ạ và làm sao để lấy được tiêu đề ạ ? Mong anh giúp đỡ ạ.

Để lưu dòng tiêu đề vô Sheet thì bạn phải dùng code gán riêng, còn dùng CopyFromRecordset là chỉ lấy Recordset (tức các record thôi chứ không có Field)
Thêm dòng code này vô:

Dim iCol As Integer
For iCol = 1 To rs.Fields.Count
    Sheet4.Cells(2, iCol).Value = rs.Fields(iCol - 1).Name
Next

 

[hoahuongduong1986]

Để lưu dòng tiêu đề vô Sheet thì bạn phải dùng code gán riêng, còn dùng CopyFromRecordset là chỉ lấy Recordset (tức các record thôi chứ không có Field)
Thêm dòng code này vô:

Dim iCol As Integer
For iCol = 1 To rs.Fields.Count
    Sheet4.Cells(2, iCol).Value = rs.Fields(iCol - 1).Name
Next

Code chạy đúng rồi ạ. Em cảm ơn các Anh đã trợ giúp em !

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

- Tiêu đề phải copy từ Data sang hoặc điền tay. Đáng lẽ khai báo ConnectionString có HDR=YES thì phải có Header nhưng hiện tại không thấy chạy
- Mã 2 có tính Sum nhưng thiếu Group By những field không tính toán. Sửa như sau:
SELECT Distinct [Partner],SUM([Trade Value (US$)]) As [Tongcong] FROM RangeName Group By [Partner]

Nếu lấy 3 cột thì phải Group By 2 cột không tính toán:

SELECT Distinct [Partner],[Commodity],SUM([Trade Value (US$)]) As [Tongcong] FROM RangeName Group By [Partner],[Commodity]

Em cảm ơn ạ !

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

Để lưu dòng tiêu đề vô Sheet thì bạn phải dùng code gán riêng, còn dùng CopyFromRecordset là chỉ lấy Recordset (tức các record thôi chứ không có Field)
Thêm dòng code này vô:

Dim iCol As Integer
For iCol = 1 To rs.Fields.Count
    Sheet4.Cells(2, iCol).Value = rs.Fields(iCol - 1).Name
Next

Em hỏi nốt anh cái này ạ. Em muốn thêm số thứ tự vào Code SQL em làm thế này mà không chạy được thì làm sao ạ

SELECT ROW_NUMBER() OVER (ORDER BY [Classification]) AS [Số thứ tự],[Classification],[Trade Flow],[Reporter],[Partner],[Reporter ISO],[Trade Value (US$)] FROM RangeName WHERE [Reporter ISO]='ALB' and [Trade Value (US$)] > 20000000

 

[ongke0711]

Em hỏi nốt anh cái này ạ. Em muốn thêm số thứ tự vào Code SQL em làm thế này mà không chạy được thì làm sao ạ

SELECT ROW_NUMBER() OVER (ORDER BY [Classification]) AS [Số thứ tự],[Classification],[Trade Flow],[Reporter],[Partner],[Reporter ISO],[Trade Value (US$)] FROM RangeName WHERE [Reporter ISO]='ALB' and [Trade Value (US$)] > 20000000

- Hàm ROW_NUMBER() chỉ dùng cho CSDL SQL Server thôi nhé.
- Nếu như các dòng dữ liệu của em có 1 field làm khoá (Primary Key) để phân biệt dòng này với dòng khác thì có thể dùng Subquery lồng vô để tạo tự động số thứ tự dòng. Đối với trường hợp dữ liệu như hiện tại thì anh bó tay.
Em ngâm cứu cách tạo thêm cột Stt sau bằng code riêng trong Excel đi.

 

[ptm0412]

Em hỏi nốt anh cái này ạ. Em muốn thêm số thứ tự vào Code SQL em làm thế này mà không chạy được thì làm sao ạ

Thêm cột STT đơn giản hơn bằng câu SQL sau:

SELECT 1 AS [Số thứ tự],[Classification],[Trade Flow],[Reporter],[Partner],[Reporter ISO],[Trade Value (US$)]
FROM RangeName WHERE [Reporter ISO]='ALB' and [Trade Value (US$)] > 20000000

Sau đó thêm các câu lệnh sau:

Dim LastRw
LastRw = Sheet4.[A10000].End(xlUp).Row
Sheet4.Range("A3:A" & LastRw).Value = Evaluate("=Row(R:R)")

Liên quan đến chủ đề này, nếu biết SQL join table nhưng chưa rành VBA thì sử dụng Power Query (Merge) cho dễ.
Trong file đính kèm có 2 sheet query, thay đổi validation và refresh là được

 

[hoahuongduong1986]

Thêm cột STT đơn giản hơn bằng câu SQL sau:

SELECT 1 AS [Số thứ tự],[Classification],[Trade Flow],[Reporter],[Partner],[Reporter ISO],[Trade Value (US$)]
FROM RangeName WHERE [Reporter ISO]='ALB' and [Trade Value (US$)] > 20000000

Sau đó thêm các câu lệnh sau:

Dim LastRw
LastRw = Sheet4.[A10000].End(xlUp).Row
Sheet4.Range("A3:A" & LastRw).Value = Evaluate("=Row(R:R)")

Liên quan đến chủ đề này, nếu biết SQL join table nhưng chưa rành VBA thì sử dụng Power Query (Merge) cho dễ.
Trong file đính kèm có 2 sheet query, thay đổi validation và refresh là được

Cảm ơn các Anh ạ !