Đố vui về ADO, DAO. (2 người xem)

VetMini · 12/7/13

"FROM [Data$] where trim(Partition([id],1,100,10)) in ('1: 10','91:100')"

Tôi tưởng là cái chỗ đó phải do code tính ra chứ không được gõ vào. Đoạn code tính ra chỗ này chạy chậm quá (mất hơn một phút cho một CSDL 10000 dòng - thử trên SQL Server, chứ Access có thể còn chậm hơn) cho nên tôi chịu thua.

Hai Lúa Miền Tây · 12/7/13

Câu hỏi mấy lần trước tuy không khó nhưng có rất ít người tham gia, thôi thì lần này dể hơn.

Ví dụ tôi có 3 WB: Data1.xls, Data2.xls, KetQua.xls có cùng 1 thư mục.

Trong Data1.xls và Data2.xls đều có 1 sheet là Sheet2, vùng dữ liệu là A1:E20, có cấu trúc giống nhau.
Trong KetQua.xls có 2 sheet: Sheet2 vùng dữ liệu là A1:E20, có cấu trúc giống nhau với 2 WB trên, và 1 sheet còn lại là KetQua dùng để chứa kết quả khi chạy code.

Xin hỏi: Là tại WB KetQua.xls chạy code như thế nào mà không dùng vòng lặp để gộp dữ liệu Sheet2 của 3 WB trên với điều kiện cột STT là không trống?

dhn46 · 12/7/13

Hai Lúa Miền Tây đã viết:
Câu hỏi mấy lần trước tuy không khó nhưng có rất ít người tham gia, thôi thì lần này dể hơn.

Ví dụ tôi có 3 WB: Data1.xls, Data2.xls, KetQua.xls có cùng 1 thư mục.

Trong Data1.xls và Data2.xls đều có 1 sheet là Sheet2, vùng dữ liệu là A1:E20, có cấu trúc giống nhau.

Trong KetQua.xls có 2 sheet: Sheet2 vùng dữ liệu là A1:E20, có cấu trúc giống nhau với 2 WB trên, và 1 sheet còn lại là KetQua dùng để chứa kết quả khi chạy code.

Xin hỏi: Là tại WB KetQua.xls chạy code như thế nào mà không dùng vòng lặp để gộp dữ liệu Sheet2 của 3 WB trên với điều kiện cột STT là không trống?

Em xin đưa ra giải pháp đầu tiên

Mã:

Sub Tonghop()
Dim lsSQL As String, Cnn As Object, lrs As Object
Set Cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
FileFullName = Application.ThisWorkbook.FullName
With Cnn
   If Val(Application.Version) < 12 Then
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
   Else
      .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
   End If
   .Open
End With

lsSQL = "Select a.* From [Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\Data1.xls].[Sheet2$A1:E20] a " _
        & "Where a.Stt Is Not Null " _
        & "Union all " _
        & "Select b.* From [Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\Data2.xls].[Sheet2$A1:E20] b " _
        & "Where b.Stt Is Not Null " _
        & "Union all " _
        & "Select c.* From [sheet2$A1:E20] c " _
        & "Where c.Stt Is Not Null"
        
lrs.Open lsSQL, Cnn, 3, 1
Range("A2").CopyFromRecordset lrs
lrs.Close: Set lrs = Nothing
Cnn.Close: Set Cnn = Nothing
Exit Sub
Handle:
MsgBox Err.Description
Set lrs = Nothing
Cnn.Close: Set Cnn = Nothing
End Sub

Hai Lúa Miền Tây · 12/7/13

dhn46 đã viết:

Em xin đưa ra giải pháp đầu tiên

Mã:

Sub Tonghop()
Dim lsSQL As String, Cnn As Object, lrs As Object
Set Cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
FileFullName = Application.ThisWorkbook.FullName
With Cnn
   If Val(Application.Version) < 12 Then
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
   Else
      .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
   End If
   .Open
End With

lsSQL = "Select a.* From [Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\Data1.xls].[Sheet2$A1:E20] a " _
        & "Where a.Stt Is Not Null " _
        & "Union all " _
        & "Select b.* From [Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path & "\Data2.xls].[Sheet2$A1:E20] b " _
        & "Where b.Stt Is Not Null " _
        & "Union all " _
        & "Select c.* From [sheet2$A1:E20] c " _
        & "Where c.Stt Is Not Null"
        
lrs.Open lsSQL, Cnn, 3, 1
Range("A2").CopyFromRecordset lrs
lrs.Close: Set lrs = Nothing
Cnn.Close: Set Cnn = Nothing
Exit Sub
Handle:
MsgBox Err.Description
Set lrs = Nothing
Cnn.Close: Set Cnn = Nothing
End Sub

Rất nhanh, nhưng cần rút gọn lại nữa. Code có xử lý file 2003 và 2007 nhưng ở câu lệnh truy vấn vẫn để 2003.

dhn46 · 12/7/13

Hai Lúa Miền Tây đã viết:
Rất nhanh, nhưng cần rút gọn lại nữa. Code có xử lý file 2003 và 2007 nhưng ở câu lệnh truy vấn vẫn để 2003.

Vâng anh HLMT, tới thời điểm này em cũng chưa thuộc hết các phần trong sub, chủ yếu là Copy Paste rồi chỉnh sửa. Anh có bí quyết gì để học các cấu trúc nhanh hơn không, bật mí cho em với?

Mã:

 Sub Tonghop()
Dim lsSQL As String, Cnn As Object, lrs As Object, Ver As String
Set Cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
FileFullName = Application.ThisWorkbook.FullName
With Cnn
   If Val(Application.Version) < 12 Then
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
      Ver = "[Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path
   Else
      .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
      Ver = "[Excel 12.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path
   End If
   .Open
End With
lsSQL = "Select a.* From " & Ver & "\Data1.xls].[Sheet2$A1:E20] a " _
        & "Where a.Stt Is Not Null " _
        & "Union all " _
        & "Select b.* From " & Ver & "\Data2.xls].[Sheet2$A1:E20] b " _
        & "Where b.Stt Is Not Null " _
        & "Union all " _
        & "Select c.* From [sheet2$A1:E20] c " _
        & "Where c.Stt Is Not Null"
lrs.Open lsSQL, Cnn, 3, 1
Range("A2").CopyFromRecordset lrs
lrs.Close: Set lrs = Nothing
Cnn.Close: Set Cnn = Nothing
Exit Sub
Handle:
MsgBox Err.Description
Set lrs = Nothing
Cnn.Close: Set Cnn = Nothing
End Sub

Hai Lúa Miền Tây · 12/7/13

dhn46 đã viết:

Vâng anh HLMT, tới thời điểm này em cũng chưa thuộc hết các phần trong sub, chủ yếu là Copy Paste rồi chỉnh sửa. Anh có bí quyết gì để học các cấu trúc nhanh hơn không, bật mí cho em với?

Mã:

 Sub Tonghop()
Dim lsSQL As String, Cnn As Object, lrs As Object, Ver As String
Set Cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
FileFullName = Application.ThisWorkbook.FullName
With Cnn
   If Val(Application.Version) < 12 Then
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
      Ver = "[Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path
   Else
      .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
      Ver = "[Excel 12.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path
   End If
   .Open
End With
lsSQL = "Select a.* From " & Ver & "\Data1.xls].[Sheet2$A1:E20] a " _
        & "Where a.Stt Is Not Null " _
        & "Union all " _
        & "Select b.* From " & Ver & "\Data2.xls].[Sheet2$A1:E20] b " _
        & "Where b.Stt Is Not Null " _
        & "Union all " _
        & "Select c.* From [sheet2$A1:E20] c " _
        & "Where c.Stt Is Not Null"
lrs.Open lsSQL, Cnn, 3, 1
Range("A2").CopyFromRecordset lrs
lrs.Close: Set lrs = Nothing
Cnn.Close: Set Cnn = Nothing
Exit Sub
Handle:
MsgBox Err.Description
Set lrs = Nothing
Cnn.Close: Set Cnn = Nothing
End Sub

- Để nhớ cấu trúc của nó thì mình không có bí quyết gì hết, chỉ đơn thuần là viết nhiều, áp dụng nhiều là nhớ.
- Câu lệnh truy vấn ở trên có thể rút gọn lại nữa đó bạn, cố lên nhé.
- Với lại code của bạn không có phần bắt lỗi nhưng ở bên dưới có kết quả bắt lỗi, nên dư mà thiếu:

Mã:

Exit Sub 
Handle: 
MsgBox Err.Description 
Set lrs = Nothing 
Cnn.Close: Set Cnn = Nothing

dhn46 · 12/7/13

Hai Lúa Miền Tây đã viết:
- Để nhớ cấu trúc của nó thì mình không có bí quyết gì hết, chỉ đơn thuần là viết nhiều, áp dụng nhiều là nhớ.
- Câu lệnh truy vấn ở trên có thể rút gọn lại nữa đó bạn, cố lên nhé.
- Với lại code của bạn không có phần bắt lỗi nhưng ở bên dưới có kết quả bắt lỗi, nên dư mà thiếu:

Mã:

Exit Sub Handle: MsgBox Err.Description Set lrs = Nothing Cnn.Close: Set Cnn = Nothing

Dạ vâng. Em thiếu phần

Mã:

On Error Go to Handle

Anh HLMT nếu rút gọn phần truy vấn thì em bỏ hết a, b, c đi thì còn rút được nữa không anh?

Hai Lúa Miền Tây · 12/7/13

dhn46 đã viết:
Dạ vâng. Em thiếu phần

Mã:

On Error Go to Handle

Anh HLMT nếu rút gọn phần truy vấn thì em bỏ hết a, b, c đi thì còn rút được nữa không anh?

Ngoài a, b, c ra vẫn còn chổ để rút gọn đó bạn.

dhn46 · 12/7/13

Hai Lúa Miền Tây đã viết:
Ngoài a, b, c ra vẫn còn chổ để rút gọn đó bạn.

Vậy em sửa như sau có rút được nữa không anh?

Mã:

lsSQL = "Select a.* from " _
        & "(Select * From " & Ver & "\Data1.xls].[Sheet2$A1:E20] " _
        & "Union all " _
        & "Select * From " & Ver & "\Data2.xls].[Sheet2$A1:E20] " _
        & "Union all " _
        & "Select * From [sheet2$A1:E20]) a " _
        & "Where a.STT Is Not Null"

Hai Lúa Miền Tây · 12/7/13

dhn46 đã viết:
Vậy em sửa như sau có rút được nữa không anh?

Mã:

lsSQL = "Select a.* from " _ & "(Select * From " & Ver & "\Data1.xls].[Sheet2$A1:E20] " _ & "Union all " _ & "Select * From " & Ver & "\Data2.xls].[Sheet2$A1:E20] " _ & "Union all " _ & "Select * From [sheet2$A1:E20]) a " _ & "Where a.STT Is Not Null"

Có thể rút ngắn lại biến Ver.

VetMini · 12/7/13

Thử nghiên cứu cách này, không dùng CopyRecordset:
Nếu sheet1 có sẵn dữ liệu thì dùng "INSERT INTO... SELECT * FROM ..."
Nếu sheet1 không có săn dữ liệu thì dùng "SELECT * INTO ... FROM ..."

Đố tiếp: nếu tôi dùng vòng lặp thì như thế nào?
Gợi ý: code có thể dùng cho N sheets.

Hai Lúa Miền Tây · 12/7/13

VetMini đã viết:
Thử nghiên cứu cách này, không dùng CopyRecordset:
Nếu sheet1 có sẵn dữ liệu thì dùng "INSERT INTO... SELECT * FROM ..."
Nếu sheet1 không có săn dữ liệu thì dùng "SELECT * INTO ... FROM ..."

Đố tiếp: nếu tôi dùng vòng lặp thì như thế nào?
Gợi ý: code có thể dùng cho N sheets.

Với dữ liệu câu đố ở bài 102 thì mình đưa tên file vào 1 array, duyệt qua từng tên file ở array rồi đưa dữ liệu vào.

Hai Lúa Miền Tây · 12/7/13

dhn46 đã viết:
Vậy em sửa như sau có rút được nữa không anh?

Mã:

lsSQL = "Select a.* from " _ & "(Select * From " & Ver & "\Data1.xls].[Sheet2$A1:E20] " _ & "Union all " _ & "Select * From " & Ver & "\Data2.xls].[Sheet2$A1:E20] " _ & "Union all " _ & "Select * From [sheet2$A1:E20]) a " _ & "Where a.STT Is Not Null"

Chỉnh cái sau

Mã:

Ver = "[Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path

Thành

Mã:

strPath= ThisWorkbook.Path

Như vậy phần bẫy lỗi cũng không cần thêm 2 dòng này... Bạn test thử nhé.

dhn46 · 12/7/13

Hai Lúa Miền Tây đã viết:
Chỉnh cái sau

Mã:

Ver = "[Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.Path

Thành

Mã:

strPath= ThisWorkbook.Path

Như vậy phần bẫy lỗi cũng không cần thêm 2 dòng này... Bạn test thử nhé.

Anh HLMT, em đã chỉnh sửa như trên sao file vẫn lỗi, anh kiểm tra giúp em nhé

Hai Lúa Miền Tây · 13/7/13

dhn46 đã viết:
Anh HLMT, em đã chỉnh sửa như trên sao file vẫn lỗi, anh kiểm tra giúp em nhé

Không chạy là do bạn thiếu dấu [

Mã:

lsSQL = "Select a.* from " _
        & "(Select * From [B][COLOR=#ff0000][[/COLOR][/B]" & strPath & "\Data1.xls].[Sheet2$A1:E20] " _
        & "Union all " _
        & "Select * From [B][COLOR=#ff0000][[/COLOR][/B]" & strPath & "\Data2.xls].[Sheet2$A1:E20] " _
        & "Union all " _
        & "Select * From [sheet2$A1:E20]) a " _
        & "Where a.STT Is Not Null"

Hai Lúa Miền Tây · 13/7/13

Đánh số TT

Tôi có bảng dữ liệu ở sheet1 (STT, Ten, SL, TenWB, TenSheet), do số thứ tự của sheet1 không đồng nhất nên phải đáng STT lại.
Vậy xin hỏi là câu lệnh truy vấn như thế nào sẽ cho ra cột STT đánh theo nhóm (Dựa vào cột TenWB). Kết quả ra ở Sheet2.
Xin cảm ơn.

VetMini · 13/7/13

VetMini đã viết:
...
Đố tiếp: nếu tôi dùng vòng lặp thì như thế nào?
Gợi ý: code có thể dùng cho N sheets.

Xi lỗi tôi lầm rồi. Xin các bạn bỏ qua câu này.
Lúc có ý kiến trên, tôi dự tính dùng chuỗi SQL nhiều lệnh để lấy dữ liệu vào multi-recordsets. Nhưng lúc thực hiện thì khám phá ra là loại CSDL thẳng hàng như Excel không cho phép làm như thế (SQL Server thì được, có lẽ Access cũng được)

Hai Lúa Miền Tây · 13/7/13

VetMini đã viết:
Xi lỗi tôi lầm rồi. Xin các bạn bỏ qua câu này.
Lúc có ý kiến trên, tôi dự tính dùng chuỗi SQL nhiều lệnh để lấy dữ liệu vào multi-recordsets. Nhưng lúc thực hiện thì khám phá ra là loại CSDL thẳng hàng như Excel không cho phép làm như thế (SQL Server thì được, có lẽ Access cũng được)

Chèn vẫn được nguyên bảng nhưng nó không hổ trợ cho điều kiện lọc để chèn.

[GPECODE=sql]Sub CopyDL()
Dim cnn As Object, lsSQL As String, lrs As Object, strPath As String, arr As Variant, i As Integer
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
strPath = ThisWorkbook.Path
arr = Array("Data1", "Data2", "KetQua")
With cnn
.ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & strPath & _
"\KetQua.xls;Extended Properties=Excel 8.0;"
.Open
End With
For i = LBound(arr) To UBound(arr)
lsSQL = "INSERT INTO [KetQua$] select * FROM [" & strPath & "\" & arr(i) & "].[Sheet2$]"
lrs.Open lsSQL, cnn
Next
Set rs = Nothing: cnn.Close: Set cnn = Nothing: Erase arr

End Sub

[/GPECODE]

dhn46 · 15/7/13

Hai Lúa Miền Tây đã viết:
Tôi có bảng dữ liệu ở sheet1 (STT, Ten, SL, TenWB, TenSheet), do số thứ tự của sheet1 không đồng nhất nên phải đáng STT lại.
Vậy xin hỏi là câu lệnh truy vấn như thế nào sẽ cho ra cột STT đánh theo nhóm (Dựa vào cột TenWB). Kết quả ra ở Sheet2.
Xin cảm ơn.

Em lại mạnh dạn đưa ra phương án đầu tiên, anh xem và góp ý nhé

Mã:

Sub STT_Dhn46()
    Set adoConn = CreateObject("ADODB.Connection")
    Set adoRS = CreateObject("ADODB.Recordset")
    With adoConn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & _
                            ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
        .Open
    End With
    With adoRS
        .ActiveConnection = adoConn
        .Open "Select (Select Count(*)+1 from [sheet1$B1:E35] a Where a.TenWb = [sheet1$B1:E35].TenWb And a.Ten<[sheet1$B1:E35].Ten) As STT, " _
             & "Ten, SL, TenWb, TenSheet From [sheet1$B1:E35]" _
             & "Group by Ten, SL,TenWb, TenSheet " _
             & "Order by TenWb"
    End With
    With Sheet2
        .[A2:E100].ClearContents
        .[A2].CopyFromRecordset adoRS
    End With
    adoRS.Close: Set adoRS = Nothing
    adoConn.Close: Set adoConn = Nothing


End Sub

Hai Lúa Miền Tây · 16/7/13

dhn46 đã viết:

Em lại mạnh dạn đưa ra phương án đầu tiên, anh xem và góp ý nhé

Mã:

Sub STT_Dhn46()
    Set adoConn = CreateObject("ADODB.Connection")
    Set adoRS = CreateObject("ADODB.Recordset")
    With adoConn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & _
                            ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
        .Open
    End With
    With adoRS
        .ActiveConnection = adoConn
        .Open "Select (Select Count(*)+1 from [sheet1$B1:E35] a Where a.TenWb = [sheet1$B1:E35].TenWb And a.Ten<[sheet1$B1:E35].Ten) As STT, " _
             & "Ten, SL, TenWb, TenSheet From [sheet1$B1:E35]" _
             & "Group by Ten, SL,TenWb, TenSheet " _
             & "Order by TenWb"
    End With
    With Sheet2
        .[A2:E100].ClearContents
        .[A2].CopyFromRecordset adoRS
    End With
    adoRS.Close: Set adoRS = Nothing
    adoConn.Close: Set adoConn = Nothing


End Sub

Có 2 vấn đề như sau:

Nếu có n dòng dữ liệu giống nhau thì nó chỉ lấy 1 (Làm sao nếu có n dòng giống nhau thì chỉ lấy 1 nhưng phải cộng cột SL lại)
Cột SL khi ra kết quả phải là dạng số.

hungpecc1 · 16/7/13

Hai Lúa Miền Tây đã viết:
Tôi có bảng dữ liệu ở sheet1 (STT, Ten, SL, TenWB, TenSheet), do số thứ tự của sheet1 không đồng nhất nên phải đáng STT lại.
Vậy xin hỏi là câu lệnh truy vấn như thế nào sẽ cho ra cột STT đánh theo nhóm (Dựa vào cột TenWB). Kết quả ra ở Sheet2.
Xin cảm ơn.

Em cũng vọc từ hôm qua, nhưng mà so với kết quả ở sheet2 của anh thì khác --> nên chắc là không đúng đáp án +-+-+-+

, nhưng mà đã mất công viết thì cứ post lên thôi :-=

* Cách của bạn Dn46 nhìn tuy gọn , nhưng mà theo em tốc độ code cũng tương đương với cách dùng vòng lặp của em ( do có các câu lệnh select lồng vào nhau )
cụ thể :
[GPECODE=vb]
Sub HLMT()
Dim cnn As Object, rstWB As Object, rstTT As Object
Dim SQL$, i&
Set cnn = CreateObject("ADODB.Connection")
Set rstWB = CreateObject("ADODB.Recordset"): Set rstTT = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data source=" & ThisWorkbook.FullName & _
";Extended properties=""excel 8.0;HDR=Yes;IMEX=2"";"
.Open
End With
rstWB.Open "SELECT TenWB FROM [A:E] GROUP BY TenWB", cnn, 3, 1, 1
With rstWB
If .RecordCount > 0 Then
.MoveFirst
While Not .EOF
If Len(rstWB!TenWB) Then
SQL = "SELECT * FROM [A:E] WHERE TenWB='" & rstWB!TenWB & "'"
rstTT.Open SQL, cnn, adOpenStatic, adLockOptimistic, 1
With rstTT
.MoveFirst
i = 1
While Not .EOF
rstTT!STT = i
.MoveNext
i = i + 1
Wend
.Close
End With
End If
.MoveNext
Wend
End If
End With
Set rstTT = Nothing
rstWB.Close: Set rstWB = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]

^^ code này em sắp xếp trực tiếp trên sheet1 luôn, còn nếu muốn copy từ sheet1 sang sheet2 thì thêm 1 câu lệnh phụ copy nữa !

dhn46 · 16/7/13

Hai Lúa Miền Tây đã viết:
Có 2 vấn đề như sau:

Nếu có n dòng dữ liệu giống nhau thì nó chỉ lấy 1 (Làm sao nếu có n dòng giống nhau thì chỉ lấy 1 nhưng phải cộng cột SL lại)

Cột SL khi ra kết quả phải là dạng số.

Để giải quyết 2 vấn đề trên em xin đưa ra truy vấn sau, anh xem và nhận xét nhé. Cảm ơn anh HLMT

Mã:

        .Open "Select (Select Count(*)+1 from [sheet1$B1:E35] a Where a.TenWb = [sheet1$B1:E35].TenWb And a.Ten<[sheet1$B1:E35].Ten) As STT, " _
             & "Ten, Sum(Val(SL)), TenWb, TenSheet From [sheet1$B1:E35]" _
             & "Group by Ten, TenWb, TenSheet " _
             & "Order by TenWb"

Hai Lúa Miền Tây · 16/7/13

dhn46 đã viết:
Để giải quyết 2 vấn đề trên em xin đưa ra truy vấn sau, anh xem và nhận xét nhé. Cảm ơn anh HLMT

Mã:

.Open "Select (Select Count(*)+1 from [sheet1$B1:E35] a Where a.TenWb = [sheet1$B1:E35].TenWb And a.Ten<[sheet1$B1:E35].Ten) As STT, " _ & "Ten, Sum(Val(SL)), TenWb, TenSheet From [sheet1$B1:E35]" _ & "Group by Ten, TenWb, TenSheet " _ & "Order by TenWb"

Kết quả ok nhưng còn 1 chút nữa là thêm 1 điều kiện là các dữ liệu giống nhau mới được group, trường hợp bạn đưa ra thiếu 1 điều kiện group của cột STT.

dhn46 · 16/7/13

Hai Lúa Miền Tây đã viết:
Kết quả ok nhưng còn 1 chút nữa là thêm 1 điều kiện là các dữ liệu giống nhau mới được group, trường hợp bạn đưa ra thiếu 1 điều kiện group của cột STT.

Vâng vậy em chốt lại phương án của em cho câu hỏi lần này:
- Đánh số thự tự theo nhóm, nhóm được tính theo cột TenWB
- Xuất dữ liệu cột SL dạng Number
- Nhóm các hàng có tất cả thông tin trùng nhau (STT,Ten,TenWB,TenSheet) thành 1 số thứ tự và cộng dồn cột SL.

Mã:

Sub STT_Dhn46()
    Set adoConn = CreateObject("ADODB.Connection")
    Set adoRS = CreateObject("ADODB.Recordset")
    With adoConn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & _
                            ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
        .Open
    End With
    With adoRS
        .ActiveConnection = adoConn
        .Open "Select (Select Count(*)+1 from [sheet1$A1:E35] a Where a.TenWb = [sheet1$A1:E35].TenWb And a.Ten<[sheet1$A1:E35].Ten) As STT, " _
             & "Ten, Sum(Val(SL)) as SL, TenWb, TenSheet From [sheet1$A1:E35]" _
             & "Group by STT, Ten, TenWb, TenSheet " _
             & "Order by TenWb"
    End With
    With Sheet2
        .[A2:E100].ClearContents
        .[A2].CopyFromRecordset adoRS
    End With
    adoRS.Close: Set adoRS = Nothing
    adoConn.Close: Set adoConn = Nothing
End Sub

Hai Lúa Miền Tây · 17/7/13

dhn46 đã viết:
Vâng vậy em chốt lại phương án của em cho câu hỏi lần này:
- Đánh số thự tự theo nhóm, nhóm được tính theo cột TenWB
- Xuất dữ liệu cột SL dạng Number
- Nhóm các hàng có tất cả thông tin trùng nhau (STT,Ten,TenWB,TenSheet) thành 1 số thứ tự và cộng dồn cột SL.

Mã:

Sub STT_Dhn46() Set adoConn = CreateObject("ADODB.Connection") Set adoRS = CreateObject("ADODB.Recordset") With adoConn .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & ThisWorkbook.FullName & _ ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" .Open End With With adoRS .ActiveConnection = adoConn .Open "Select (Select Count(*)+1 from [sheet1$A1:E35] a Where a.TenWb = [sheet1$A1:E35].TenWb And a.Ten<[sheet1$A1:E35].Ten) As STT, " _ & "Ten, Sum(Val(SL)) as SL, TenWb, TenSheet From [sheet1$A1:E35]" _ & "Group by STT, Ten, TenWb, TenSheet " _ & "Order by TenWb" End With With Sheet2 .[A2:E100].ClearContents .[A2].CopyFromRecordset adoRS End With adoRS.Close: Set adoRS = Nothing adoConn.Close: Set adoConn = Nothing End Sub

Còn phải xử lý lại bạn nhé:
- Nếu có dòng có dữ liệu trùng, code của bạn nó sẽ nhảy STT bị nhảy số tương ứng với dòng trùng thay vì phải bỏ qua những dòng trùng này.
- STT phải được sắp xếp theo thứ tự tăng dần.

Bạn xem kết quả sau khi chạy của bạn nhé.

dhn46 · 19/7/13

Hai Lúa Miền Tây đã viết:
Còn phải xử lý lại bạn nhé:
- Nếu có dòng có dữ liệu trùng, code của bạn nó sẽ nhảy STT bị nhảy số tương ứng với dòng trùng thay vì phải bỏ qua những dòng trùng này.
- STT phải được sắp xếp theo thứ tự tăng dần.

Bạn xem kết quả sau khi chạy của bạn nhé.

Em sửa lại như sau, anh góp ý nhé. Cảm ơn anh HLMT

Mã:

        .Open "Select (Select Count(*)+1 from " _
             & "(Select STT,Ten,TenWb,TenSheet from [sheet1$A1:E50] Tb Group by STT,Ten,TenWb,TenSheet) a " _
             & "Where a.TenWb = [sheet1$A1:E50].TenWb " _
             & "And a.STT&a.Ten&a.TenWb&a.TenSheet < [sheet1$A1:E50].STT&[sheet1$A1:E50].Ten&[sheet1$A1:E50].TenWB&[sheet1$A1:E50].TenSheet) As STT, " _
             & "Ten, Sum(Val(SL)), TenWb, TenSheet From [sheet1$A1:E50]" _
             & "Group by STT, Ten, TenWb, TenSheet " _
             & "Order by TenWb,Ten&TenWb&TenSheet&STT"

quocphuoc88 · 19/8/13

không biết e post câu hỏi trong đây có được không, nhưng sẵn chủ đề này các a và các Thầy cho e hỏi là cái chữ "a" trong câu lệnh SQL này nghĩa là gì ( nếu e hiểu là cột A trong bảng dữ liệu A1:E35 thì đúng không ạ )

.Open "Select (Select Count(*)+1 from [sheet1$A1:E35] a Where a.TenWb = [sheet1$A1:E35].TenWb And a.Ten<[sheet1$A1:E35].Ten) As STT, " _

Cám ơn các a/chị và các Thầy !

dhn46 · 19/8/13

quocphuoc88 đã viết:
không biết e post câu hỏi trong đây có được không, nhưng sẵn chủ đề này các a và các Thầy cho e hỏi là cái chữ "a" trong câu lệnh SQL này nghĩa là gì ( nếu e hiểu là cột A trong bảng dữ liệu A1:E35 thì đúng không ạ )

Mã:

[COLOR=#000000][I].Open "Select (Select Count(*)+1 from [/I][/COLOR][COLOR=#ff0000][I][B][sheet1$A1:E35] a[/B][/I][/COLOR][COLOR=#000000][I] Where a.TenWb = [sheet1$A1:E35].TenWb And a.Ten<[sheet1$A1:E35].Ten) As STT, " _ [/I][/COLOR]

Cám ơn các a/chị và các Thầy !

Bạn hỏi vậy chắc bạn cũng đã tìm hiểu ít nhiều về SQL, a ở đây là cách đặt tên bảng trong truy vấn. Phần màu đỏ đó có thể viết lại như sau cho bạn dễ hình dung, hiểu là cột A trong vùng dữ liệu là sai.

Mã:

[COLOR=#000000][I]........[sheet1$A1:E35]  as a.......

[/I][/COLOR]

quocphuoc88 · 19/8/13

Dạ, e cám ơn a ! E hiểu thêm được chút ít về ADO nữa rồi. E nguyên cứu ADO gần 1 tháng nay mà chả thấy khá hơn chút nào nhưng thấy các a và các Thầy áp dụng nó hay quá nên e cũng cố gắng mày mò. Huy vọng các a và các Thầy tiếp tục bàn luận nữa để e hiểu rõ hơn và vận dụng được ADO ạ !

E cám ơn !

P/s : vậy chữ "a" đó e được thay bằng những chữ khác như "b" được không và chữ "a" này mình cũng không cần phải khai báo ngay từ đầu sub luôn hả a ! ( vì e thấy trong vba thì phải khai báo các biến )

manuchungtinh · 19/8/13

quocphuoc88 đã viết:
P/s : vậy chữ "a" đó e được thay bằng những chữ khác như "b" được không và chữ "a" này mình cũng không cần phải khai báo ngay từ đầu sub luôn hả a ! ( vì e thấy trong vba thì phải khai báo các biến )

Bạn thích thay thành d e f T1...cũng được và cũng không phải khai báo ở đầu thủ tục đâu. Nhưng đặt xong khi gọi tên các trường của vùng đó ra bạn cũng phải thay lun ví dụ :
.Open "Select (Select Count(*)+1 from [sheet1$A1:E35] a Where a.TenWb = [sheet1$A1:E35].TenWb And a.Ten<[sheet1$A1:E35].Ten) As STT, " _
Thành
.Open "Select (Select Count(*)+1 from [sheet1$A1:E35] b Where b.TenWb = b.TenWb And b.Ten < b.Ten) As STT, " _Nó chỉ là cách đặt tên vùng dữ liệu để khi bạn gọi tên cột trong vùng đó viết a.tên cột cho nó gọn ý mà. Không biết mình hỉu vậy đúng hem nhỉ? hi

dhn46 · 19/8/13

manuchungtinh đã viết:
Bạn thích thay thành d e f T1...cũng được và cũng không phải khai báo ở đầu thủ tục đâu. Nhưng đặt xong khi gọi tên các trường của vùng đó ra bạn cũng phải thay lun ví dụ :
.Open "Select (Select Count(*)+1 from [sheet1$A1:E35] a Where a.TenWb = [sheet1$A1:E35].TenWb And a.Ten<[sheet1$A1:E35].Ten) As STT, " _
Thành
.Open "Select (Select Count(*)+1 from [sheet1$A1:E350] b Where b.TenWb = b.TenWb And b.Ten < b.Ten) As STT, " _Nó chỉ là cách đặt tên vùng dữ liệu để khi bạn gọi tên cột trong vùng đó viết a.tên cột cho nó gọn ý mà. Không biết mình hỉu vậy đúng hem nhỉ? hi

- Bạn thử kiểm tra lại phần sửa lại của bạn xem có đúng không nhé. Tại sao lại có phần màu xanh kia? Mình nghĩ bạn nên cân nhắc phần chữ màu đỏ.

- Có lẽ nên để Topic này đúng nghĩa là "Đố vui ADO", những bàn luận tiếp theo chúng ta nên chuyển qua mục ADO căn bản nhỉ?

manuchungtinh · 19/8/13

dhn46 đã viết:
- Bạn thử kiểm tra lại phần sửa lại của bạn xem có đúng không nhé. Tại sao lại có phần màu xanh kia? Mình nghĩ bạn nên cân nhắc phần chữ màu đỏ.

Tất nhiên tùy trường hợp và tùy vào yêu cầu thôi, ý tôi đang muốn giải thích cho người hỏi hiểu thay là thay thế nào ý mà. Tôi cũng đang mơ hồ nên mới hỏi lại là tôi hiểu như thế có đúng không gì mà bạn giãy lên làm tôi giật cả mình, hi, biết thì trả lời giúp tui đi là tôi hiểu thế có đúng hem?

Hai Lúa Miền Tây · 2/11/13

Thay đổi dữ liệu nguồn.

Đã lâu không làm mới Topic này, hôm nay để khởi động lại xin đố 1 câu đố dể về cập nhật dữ liệu:

Mình có 1 cột dữ liệu ở cột A tên cột này là TenVatTu, câu hỏi đặt ra là dựa vào điều kiện cell C2 để cập nhật giá trị cell D2 vào cột TenVatTu.

dhn46 · 2/11/13

Hai Lúa Miền Tây đã viết:
Đã lâu không làm mới Topic này, hôm nay để khởi động lại xin đố 1 câu đố dể về cập nhật dữ liệu:

Mình có 1 cột dữ liệu ở cột A tên cột này là TenVatTu, câu hỏi đặt ra là dựa vào điều kiện cell C2 để cập nhật giá trị cell D2 vào cột TenVatTu.

Em đưa ra phương đầu tiên nhé

Mã:

Sub DHN46()
    Dim cnn As Object, lsSQL As String, lrs As Object, FileFullName As String
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    FileFullName = Application.ThisWorkbook.FullName
    With cnn
        If Val(Application.Version) < 12 Then
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
        .Open
    End With
    lsSQL = "Update [sheet1$A1:A22] set TenVatTu=""" & [D2] & """ Where TenVatTu=""" & [C2] & """"
    lrs.Open lsSQL, cnn, 3, 1
    Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
End Sub

Hai Lúa Miền Tây · 2/11/13

dhn46 đã viết:

Em đưa ra phương đầu tiên nhé

Mã:

Sub DHN46()
    Dim cnn As Object, lsSQL As String, lrs As Object, FileFullName As String
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    FileFullName = Application.ThisWorkbook.FullName
    With cnn
        If Val(Application.Version) < 12 Then
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
        .Open
    End With
    lsSQL = "Update [sheet1$A1:A22] set TenVatTu=""" & [D2] & """ Where TenVatTu=""" & [C2] & """"
    lrs.Open lsSQL, cnn, 3, 1
    Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
End Sub

Chính xác là như vậy, bẫy ở chổ có dấu nháy đơn. Vậy là bạn đã hiểu mọi vấn đề rồi.

Hai Lúa Miền Tây · 20/1/14

Tôi có 1 file tên là Data như hình sau:

Và 1 file khác tên là Material

Giờ thì làm cách nào ở file Test tôi chạy code sẽ cho kết quả như sau:

Lưu ý là tất cả 3 file được lưu chung vào 1 folder.

dhn46 · 20/1/14

Hai Lúa Miền Tây đã viết:
Tôi có 1 file tên là Data như hình sau:
.......................
Và 1 file khác tên là Material
................
Giờ thì làm cách nào ở file Test tôi chạy code sẽ cho kết quả như sau:
..............
Lưu ý là tất cả 3 file được lưu chung vào 1 folder.

Như thế này có được không anh?

Mã:

Sub ADO()
    Dim cnn As Object, lsSQL As String, lrs As Object, FileFullName As String, StrPath As String
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    FileFullName = Application.ThisWorkbook.FullName
    StrPath = ThisWorkbook.Path
    With cnn
        If Val(Application.Version) < 12 Then
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
        .Open
    End With
    lsSQL = "Select Tb1.MatID,Tb2.[TP],Tb2.[Material Name],Tb2.[Spec],Tb2.[Color],Tb2.[Unit],Tb1.Qty,Tb2.[Origin],Tb2.[Supplier] " _
            & "From [" & StrPath & "\Data.xls].[Data$A1:E65536] Tb1 " _
            & "Left Join  (Select * from [" & StrPath & "\Material.xls].[Material$A1:H65536]) Tb2 " _
            & "On Tb1.MatID=Tb2.MatID " _
            & "Order by Tb2.[MatID]"


    lrs.Open lsSQL, cnn, 3, 1
    Sheet1.[A2:I6000].ClearContents
    Sheet1.Range("A2").CopyFromRecordset lrs
    Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
End Sub

Hai Lúa Miền Tây · 20/1/14

Nên tận dụng chuổi kết nối với file đích bạn à.

Hai Lúa Miền Tây · 16/5/14

trangk59b đã viết:
& "where f1 like '[NLT]%'"
End With
With Sheet1
.[H2:I65000].ClearContents
.[H2:I2].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End Sub

Bạn viết cái gì vậy?

hungpecc1 · 7/8/14

Xin chào tất cả các bạn yêu thích, đã và đang sử dụng công cụ ADO.
Hôm nay mình có một câu đố vui thế này :
* Giả sử mình tạo một foder có tên là : YESTERDAY-TODAY;NOW
* Trong folder trên có 1 file excel có gán 1 đoạn code sau :

Mã:

Sub test()
    With [B]CreateObject("ADODB.Connection")[/B]
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & [B]ThisWorkbook.FullName[/B] & _
                                    ";Extended Properties=""Excel 12.0;HDR=No;"";"
        .Open
    End With
    Set cnn = Nothing
End Sub

* Câu hỏi là : Làm sao để sub test() hoạt động được !

Cảm ơn mọi người đã tham gia !

dhn46 · 7/8/14

hungpecc1 đã viết:
Xin chào tất cả các bạn yêu thích, đã và đang sử dụng công cụ ADO.
Hôm nay mình có một câu đố vui thế này :
* Giả sử mình tạo một foder có tên là : YESTERDAY-TODAY;NOW
* Trong folder trên có 1 file excel có gán 1 đoạn code sau :

Mã:

Sub test() With [B]CreateObject("ADODB.Connection")[/B] .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & [B]ThisWorkbook.FullName[/B] & _ ";Extended Properties=""Excel 12.0;HDR=No;"";" .Open End With Set cnn = Nothing End Sub

* Câu hỏi là : Làm sao để sub test() hoạt động được !

Cảm ơn mọi người đã tham gia !

Thêm 2 cái dấu nháy phải không bạn?

hungpecc1 · 7/8/14

dhn46 đã viết:
Thêm 2 cái dấu nháy phải không bạn?

chính xác!
trong chuỗi connectionstring ở trên thiếu 2 dấu nháy !
mà bạn trả lời nhanh quá --=0

, câu hỏi tiếp theo dành cho các thành viên có huy hiệu < 1 sao cứng :
* Bây giờ cũng trong folder YESTERDAY-TODAY;NOW , có 1 file ACCSES : A.mdb
* Câu hỏi đặt ra : ta sẽ phải chính sửa chuỗi connectionstring ở trên như thế nào ? để có thể kết nối đến file A.mdb !

Cảm ơn mọi người đã tham gia !

Quang_Hải · 7/8/14

hungpecc1 đã viết:
chính xác!
trong chuỗi connectionstring ở trên thiếu 2 dấu nháy !
mà bạn trả lời nhanh quá , câu hỏi tiếp theo dành cho các thành viên có huy hiệu < 1 sao cứng :
* Bây giờ cũng trong folder YESTERDAY-TODAY;NOW , có 1 file ACCSES : A.mdb
* Câu hỏi đặt ra : ta sẽ phải chính sửa chuỗi connectionstring ở trên như thế nào ? để có thể kết nối đến file A.mdb !

Cảm ơn mọi người đã tham gia !

Nói 2 cái dấu nháy mà không nói rõ ở đâu thì ai biết thêm ở đâu chứ. Lỡ như thêm nháy thế này
& 'ThisWorkbook.FullName' & thì chắc khóc quá

Mình 4 sao cứng kìa mà hỏng biết đường trả lời huống chi ép < 1 sao cứng thì ế cho chết luôn

nmhung49 · 7/8/14

quanghai1969 đã viết:
Nói 2 cái dấu nháy mà không nói rõ ở đâu thì ai biết thêm ở đâu chứ. Lỡ như thêm nháy thế này
& 'ThisWorkbook.FullName' & thì chắc khóc quá

Mình 4 sao cứng kìa mà hỏng biết đường trả lời huống chi ép < 1 sao cứng thì ế cho chết luôn

Với khả năng mình biết lỗi ConnectString là do tên folder đặt trùng với ký hiệu bắt buộc của câu lệnh connect đó là dấu ";' nếu folder đặt tên không trùng ký hiệu đó thì lệnh connect vẫn bình thường không có gì thay đổi.
Nếu sửa lại chính xác thì là vày
[GPECODE=vb]
Sub test()
With CreateObject("ADODB.Connection")
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & ThisWorkbook.FullName & "';Extended Properties=""Excel 12.0;HDR=No;"";"
.Open
End With
'Set cnn = Nothing
End Sub
[/GPECODE]
Tức là thêm 1 dấu nháy ' trong Data Source='" và 1 dấu nháy "';Extended. Trong đoạn sub không có biến cnn nên bỏ đi nếu có thì set cnn = nothing

Quang_Hải · 7/8/14

nmhung49 đã viết:
Với khả năng mình biết lỗi ConnectString là do tên folder đặt trùng với ký hiệu bắt buộc của câu lệnh connect đó là dấu ";' nếu folder đặt tên không trùng ký hiệu đó thì lệnh connect vẫn bình thường không có gì thay đổi.
Nếu sửa lại chính xác thì là vày
[GPECODE=vb]
Sub test()
With CreateObject("ADODB.Connection")
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & ThisWorkbook.FullName & "';Extended Properties=""Excel 12.0;HDR=No;"";"
.Open
End With
'Set cnn = Nothing
End Sub
[/GPECODE]
Tức là thêm 1 dấu nháy ' trong Data Source='" và 1 dấu nháy "';Extended. Trong đoạn sub không có biến cnn nên bỏ đi nếu có thì set cnn = nothing

Có tí cơ bản về ADO thì nhìn vào là biết liền. Chủ topic gài bẫy thôi mà. Tại vì bài 142 la lên là chính xác mà không nói cho nháy vào chỗ nào strong câu lệnh SQL nên mình chọt cho vui
Quy định của ADO là vậy mà. Số thì không cần bao bọc, text thì bao bằng 2 dấu nháy đơn, ngày thì bao bằng dấu #

hungpecc1 · 7/8/14

quanghai1969 đã viết:
Có tí cơ bản về ADO thì nhìn vào là biết liền. Chủ topic gài bẫy thôi mà. Tại vì bài 142 la lên là chính xác mà không nói cho nháy vào chỗ nào strong câu lệnh SQL nên mình chọt cho vui
Quy định của ADO là vậy mà. Số thì không cần bao bọc, text thì bao bằng 2 dấu nháy đơn, ngày thì bao bằng dấu #

Tự nhiên thấy nhớ anh Hai Lúa nên đố vui cho đỡ nhớ thôi !

nmhung49 · 7/8/14

hungpecc1 đã viết:
Tự nhiên thấy nhớ anh Hai Lúa nên đố vui cho đỡ nhớ thôi !

Dạo này thấy ít người dùng ADO thì phải có lẽ nó hơi khó nhớ cầu lệnh nên ít viết, ADO, DAO luôn mạnh trong việc tổng hợp số liệu nếu ta khai thác chúng đúng cách

Quy định của ADO là vậy mà. Số thì không cần bao bọc, text thì bao bằng 2 dấu nháy đơn, ngày thì bao bằng dấu #

Vì vây tốt nhất nên bao text lại = 2 dấu nháy là an toàn
Lâu rồi không thấy anh Hai Lúa ra câu đố về ADO chắc ít người tham gia nên anh Đỏm buồn --=0

(Ai biểu ra câu đố khó quá mà)

dhn46 · 7/8/14

nmhung49 đã viết:
Dạo này thấy ít người dùng ADO thì phải có lẽ nó hơi khó nhớ cầu lệnh nên ít viết, ADO, DAO luôn mạnh trong việc tổng hợp số liệu nếu ta khai thác chúng đúng cách

Vì vây tốt nhất nên bao text lại = 2 dấu nháy là an toàn
Lâu rồi không thấy anh Hai Lúa ra câu đố về ADO chắc ít người tham gia nên anh Đỏm buồn (Ai biểu ra câu đố khó quá mà)

Khi sử dụng các truy vấn SQL thì thường người dùng nghĩ tới Access, có lẽ đó cũng là một lý do khiến ADO ít phổ biến trong Excel.
Và qua các bài cơ bản tại topic này, các bài viết của anh HLMT, các bạn hungpecc1, bạn mnhung49... hy vọng mình sẽ tiến bộ hơn nữa ^^.

nmhung49 · 7/8/14

dhn46 đã viết:
Khi sử dụng các truy vấn SQL thì thường người dùng nghĩ tới Access, có lẽ đó cũng là một lý do khiến ADO ít phổ biến trong Excel.
Và qua các bài cơ bản tại topic này, các bài viết của anh HLMT, các bạn hungpecc1, bạn mnhung49... hy vọng mình sẽ tiến bộ hơn nữa ^^.

Mình cũng như các bạn chủ yếu học hỏi từ anh Hai Lúa, cố gắng hiểu câu lệnh SQL, các câu truy vấn nếu ai biết sơ về Access sẽ học dễ hơn mau tiếp thu vì các câu truy vấn giống trong Access là chủ yếu, lâu rồi không đụng lại SQL nên thấy cũng nhớ nhớ --=0

Hai Lúa Miền Tây · 8/8/14

nmhung49 đã viết:
Với khả năng mình biết lỗi ConnectString là do tên folder đặt trùng với ký hiệu bắt buộc của câu lệnh connect đó là dấu ";' nếu folder đặt tên không trùng ký hiệu đó thì lệnh connect vẫn bình thường không có gì thay đổi.
Nếu sửa lại chính xác thì là vày
[GPECODE=vb]
Sub test()
With CreateObject("ADODB.Connection")
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & ThisWorkbook.FullName & "';Extended Properties=""Excel 12.0;HDR=No;"";"
.Open
End With
'Set cnn = Nothing
End Sub
[/GPECODE]
Tức là thêm 1 dấu nháy ' trong Data Source='" và 1 dấu nháy "';Extended. Trong đoạn sub không có biến cnn nên bỏ đi nếu có thì set cnn = nothing

Vậy nếu ta không dùng dấu nháy (') thì làm sao cho nó chạy?

Quang_Hải · 8/8/14

Hai Lúa Miền Tây đã viết:
Vậy nếu ta không dùng dấu nháy (') thì làm sao cho nó chạy?

Vầy chắc được

PHP:

Sub test()
Dim duongdan As String
duongdan = ThisWorkbook.FullName
    With CreateObject("ADODB.Connection")
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= duongdan  ;Extended Properties=""Excel 12.0;HDR=No;"";"
        .Open
    End With
    'Set cnn = Nothing
End Sub

nmhung49 · 8/8/14

quanghai1969 đã viết:

Vầy chắc được

PHP:

Sub test()
Dim duongdan As String
duongdan = ThisWorkbook.FullName
    With CreateObject("ADODB.Connection")
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= duongdan  ;Extended Properties=""Excel 12.0;HDR=No;"";"
        .Open
    End With
    'Set cnn = Nothing
End Sub

Code chạy không lỗi những nó không kết nối với Book1, nói chung là không phải vậy, em cũng chưa tìm ra chắc format hay sao đó

Quang_Hải · 8/8/14

nmhung49 đã viết:
Code chạy không lỗi những nó không kết nối với Book1, nói chung là không phải vậy, em cũng chưa tìm ra chắc format hay sao đó

Mình chưa test vào file nhưng cũng nghi nghi rồi, té ra là không thể kết nối. Để xem xem coi sao.
Tại vì HLMT hỏi làm sao cho chạy thôi. Hic

VetMini · 8/8/14

Dùng kết nối với tham số. Tại các bạn quen dùng chuỗi kết nối chứ trên thực tế, khi kết nối không đơn giản thì người ta dùng tham số dễ kiểm soát hơn:

Mã:

With Cnn
  .Provider = IIF(Application.Version=12, "...", "...")
  .Properties("Data Source") = filePath
  .Properties("Extended Properties) = IIF(Application.Version=12, "...", "...")
  .Open
End With

tb. bài #140 của hungpec1 dừng lại ngay sau khi kết nối, chứ chưa hề đá động đến truy vấn. Cho nên ở đây ta chỉ nói về kết nối và chuỗi kết nối. Kết nối không liên quan gì đến SQL cả. Bạn nào nói SQL là sai.
Sau khi có kết nối rồi người ta mới gởi một lệnh SQL vào kết nối để truy vấn dữ liệu. Tuỳ theo loại máy lúc kết nối mà cách viết lệnh SQL có thể khác nhau. Tuy SQL là môt ngôn ngữ tiêu chuẩn nhưng mỗi cổ máy có thể thêm thắt một chút để tăng hiệu quả.

Hai Lúa Miền Tây · 15/9/14

Lâu quá không ai tham gia đề tài này, hôm nay mình gửi câu đố đơn giản như sau:

==> Tôi viết câu truy vấn như thế nào sẽ cho ra kết quả như trên.

dhn46 · 16/9/14

Hai Lúa Miền Tây đã viết:
Lâu quá không ai tham gia đề tài này, hôm nay mình gửi câu đố đơn giản như sau:

View attachment 129230

==> Tôi viết câu truy vấn như thế nào sẽ cho ra kết quả như trên.

Đợi thêm người tham gia cùng Topic nhưng chưa có ai nên em lại nộp bài đầu tiên anh Hai Lúa Miền Tấy nhé.

Mã:

Sub Tonghop()
    Dim cnn As Object, rst As Object, lSQL As String
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.recordset")
    Filename = Application.ThisWorkbook.FullName
    If Val(Application.Version) < 12 Then
        With cnn
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Filename & ";" & _
                                "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
            .Open
        End With
    Else
        With cnn
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Filename & ";" & _
                                "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
            .Open
        End With
    End If
    lSQL = "Select F2,F3,F4,F5,F6 From " _
         & "(Select F2,F3,F4,F5,F6 from " _
         & "(Select F2,F3,F4,F5,F6,F2 as o " _
         & "From [Dovui$A2:F16] as a " _
         & " Union all " _
         & "Select '',f3 & ' Total','',Sum(F5),Sum(F6),'' " _
         & "From [Dovui$A2:F16] as a " _
         & "Group by F3) as a) " _
         & "Union all " _
         & "Select '','zGrand Total:','',Sum(F5),Sum(F6) " _
         & "From [Dovui$A2:F16]" _
         & "Order by f3,f2"
    rst.Open lSQL, cnn, 3, 1
    Sheet1.[J2:N65536].CurrentRegion.ClearContents
    Sheet2.[J2].CopyFromRecordset rst
    rst.Close: Set rst = Nothing
    cnn.Close: Set cnn = Nothing
End Sub

Hai Lúa Miền Tây · 16/9/14

dhn46 đã viết:

Đợi thêm người tham gia cùng Topic nhưng chưa có ai nên em lại nộp bài đầu tiên anh Hai Lúa Miền Tấy nhé.

Mã:

Sub Tonghop()
    Dim cnn As Object, rst As Object, lSQL As String
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.recordset")
    Filename = Application.ThisWorkbook.FullName
    If Val(Application.Version) < 12 Then
        With cnn
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Filename & ";" & _
                                "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
            .Open
        End With
    Else
        With cnn
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Filename & ";" & _
                                "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
            .Open
        End With
    End If
    lSQL = "Select F2,F3,F4,F5,F6 From " _
         & "(Select F2,F3,F4,F5,F6 from " _
         & "(Select F2,F3,F4,F5,F6,F2 as o " _
         & "From [Dovui$A2:F16] as a " _
         & " Union all " _
         & "Select '',f3 & ' Total','',Sum(F5),Sum(F6),'' " _
         & "From [Dovui$A2:F16] as a " _
         & "Group by F3) as a) " _
         & "Union all " _
         & "Select '','zGrand Total:','',Sum(F5),Sum(F6) " _
         & "From [Dovui$A2:F16]" _
         & "Order by f3,f2"
    rst.Open lSQL, cnn, 3, 1
   [COLOR=#ff0000] Sheet1[/COLOR].[J2:N65536].CurrentRegion.ClearContents
   [COLOR=#ff0000] Sheet2[/COLOR].[J2].CopyFromRecordset rst
    rst.Close: Set rst = Nothing
    cnn.Close: Set cnn = Nothing
End Sub

Đúng rồi bạn à, Không làm khó được bạn rồi.

dhn46 · 16/9/14

Hai Lúa Miền Tây đã viết:
Đúng rồi bạn à, Không làm khó được bạn rồi.

Em lại hấp tấp rồi, Test một đằng lại sửa để post mà không xem lại (chính tả nữa)^^.

Mong anh ra thêm nhiều câu đố để em học hỏi và cũng mong có thêm nhiều bạn tham gia cùng Topic.

nguhucu · 26/3/15

Hai Lúa Miền Tây đã viết:
Đúng rồi bạn à, Không làm khó được bạn rồi.

Nhân viên Lê Đức Trí có mã NV khác và cũng thuộc phòng khác vậy các bác (trùng tên khác mâ số và phòng)
vậy các bác làm sao? __--__

Hai Lúa Miền Tây · 27/3/15

nguhucu đã viết:
Nhân viên Lê Đức Trí có mã NV khác và cũng thuộc phòng khác vậy các bác (trùng tên khác mâ số và phòng)
vậy các bác làm sao?

Đó chỉ là ví dụ, trong thực tế ta phải uyển chuyển để phù hợp với nhu cầu. Trường hợp bạn đưa ra nằm trong cái uyển chuyển đó.

VetMini · 27/3/15

nguhucu đã viết:
Nhân viên Lê Đức Trí có mã NV khác và cũng thuộc phòng khác vậy các bác (trùng tên khác mâ số và phòng)
vậy các bác làm sao?

Cái này thuộc về cách thiết kế CSDL của bạn. Nếu thiết kế chuẩn thì "uyển chuyển" rất dễ. Nếu không chuẩn thì... không nên dùng ADO.

Lý do: ADO lợi dụng tiêu chuẩn của SQL để làm việc cho gọn gàng. SQL chỉ gọn khi CSDL được thiết kế đủ chuẩn (ít nhất là chuẩn bậc 1, tuy lý tưởng thì là bậc 3). Nếu không chuẩn thì thà dùng code cổ điển đọc từng dòng gọn hơn.

quanluu · 2/12/15

Hai Lúa Miền Tây đã viết:
Lâu quá không ai tham gia đề tài này, hôm nay mình gửi câu đố đơn giản như sau:

View attachment 129230

==> Tôi viết câu truy vấn như thế nào sẽ cho ra kết quả như trên.

Em thấy trên SQL có hàm COMPUTE để xử lý ra kq như của bác, nhưng ko biết có áp dụng đc trên ADO ko? Mà em thấy nếu ko dùng ADO thì có thể dùng Pivot để ra kq. hj

Hai Lúa Miền Tây · 2/12/15

quanluu1989 đã viết:
Em thấy trên SQL có hàm COMPUTE để xử lý ra kq như của bác, nhưng ko biết có áp dụng đc trên ADO ko? Mà em thấy nếu ko dùng ADO thì có thể dùng Pivot để ra kq. hj

Bạn hãy thử nhé, nếu được thì mọi người sẽ học hỏi thêm.
Do ở đây là đề tài ADO nên dùng ADO, Pivot thì nói làm gì nữa bạn.

VetMini · 2/12/15

quanluu1989 đã viết:
Em thấy trên SQL có hàm COMPUTE để xử lý ra kq như của bác, nhưng ko biết có áp dụng đc trên ADO ko? Mà em thấy nếu ko dùng ADO thì có thể dùng Pivot để ra kq. hj

Bạn thấy ở đâu vậy? Cái chỗ bạn thấy nó nói áp dụng trên đâu thì áp dụng được trên đấy. ADO chỉ là công cụ truy xuất dữ liệu. Truy xuất như thế nào là tuỳ theo cái nguồn.

doveandrose · 2/12/15

Hai Lúa Miền Tây đã viết:
Bạn hãy thử nhé, nếu được thì mọi người sẽ học hỏi thêm.
Do ở đây là đề tài ADO nên dùng ADO, Pivot thì nói làm gì nữa bạn.

ủa lâu rồi anh không viết bài mới ở đây . Làm câu đố cho tui em học hỏi đi anh --=0

VetMini · 3/12/15

doveandrose đã viết:
ủa lâu rồi anh không viết bài mới ở đây . Làm câu đố cho tui em học hỏi đi anh

Muốn thử hả. Thì thử trả lời câu hỏi ở bài #162, tại sao bạn ấy ấy không làm thử trước khi hỏi/đề nghị.

doveandrose · 3/12/15

VetMini đã viết:
Muốn thử hả. Thì thử trả lời câu hỏi ở bài #162, tại sao bạn ấy ấy không làm thử trước khi hỏi/đề nghị.

excel với ADO đâu có từ khóa Compute đâu mà thử ....

VetMini · 3/12/15

doveandrose đã viết:
excel với ADO đâu có từ khóa Compute đâu mà thử ....

Chịu khó đọc kỹ bài #162, và bài #164
Nếu bạn chịu khó tìm sâu 1 chút sẽ rất có lợi cho kiến thức về CSDL của bạn.

doveandrose · 3/12/15

VetMini đã viết:
Chịu khó đọc kỹ bài #162, và bài #164
Nếu bạn chịu khó tìm sâu 1 chút sẽ rất có lợi cho kiến thức về CSDL của bạn.

trời , trong này còn có ẩn ý sâu xa nữa à . Vậy em không đủ thông minh để hiểu rồi ....

VetMini · 3/12/15

Vấn đề này chỉ thuần lý thuyết. Có hai phần chính.

1. Rất nhiều người lầm cái từ SQL. SQL là viết tắt của Structured Query Language. Ngôn ngữ này được IBM lập ra để truy vấn một dạng CSDL của họ. Về sau, SQL được ANSI tiêu chuẩn hoá thành ngôn ngữ truy vấn CSDL dạng Liên Hệ.

Microsoft chọn cái tên SQL Server cho phần mềm CSDL của mình là cố tình đánh vào cái hay nhầm của người dùng. Vì vậy, người VN khi nói câu SQL hết 99% là họ muốn nói SQL Server. Mỗi CSDL của nhà cung cấp có một phiên bản SQL phác nhau. Ngôn ngữ dùng để truy vấn SQL Server tên là T-SQL (Transact SQL)

Phiên bản T-SQL cho SQL Server 2008 R2 có hàm Compute dùng để tính tổng con tổng mẹ. Tôi nghĩ là bài #162 muốn nói cái này. Lưu ý là qua phiên bản 2012 thì T-SQL tránh Compute và dùng lệnh truy vấn khác.

2. ADO chỉ là một object nằm trong Windows, dùng để đọc dữ liệu. Tuỳ theo dạng chứa của dữ liệu mà ta phải bảo ADO dùng cỗ máy (engine) nào để đọc. Khi phần mềm gởi thông điệp kết nối cho ADO, chuỗi kết nối có chứa lệnh chọn cỗ máy. Vì ở đây, ta chỉ đọc dữ liệu từ file Excel cho nên cỗ máy đọc là Access (đời cũ là Jet).

Access sử dụng lệnh truy vấn chỉ gần giống SQL chứ không hoàn toàn 100%. Và việc sử dụng lệnh Compute còn tuỳ thuộc vào Access có hổ trợ lệnh này hay không. Nên nhớ là công cụ query trong Access rất giới hạn. Những việc trình bày phức tạp như tổng tiếc, người ta dùng report và VBA.

Ngô Khánh An · 3/12/15

Tiện về ADO cho e hỏi các anh chị, vấn để sau. Vì file of a rất nhiều dòng, nên dùng công thức chạy rất chậm. mà ADO e chưa rành lắm. Nhờ các chỉ giùm cách làm việc trong file với ạ!

VetMini · 3/12/15

Ngô Khánh An đã viết:
Tiện về ADO cho e hỏi các anh chị, vấn để sau. Vì file of a rất nhiều dòng, nên dùng công thức chạy rất chậm. mà ADO e chưa rành lắm. Nhờ các chỉ giùm cách làm việc trong file với ạ!

"file of a" nghĩa là gì?
Hỏi thì nói rõ rệt cho người ta hiểu. Bày đặt nói tiếng này nọ chỉ tổ hiểu lầm.

Ngô Khánh An · 5/12/15

Hic, mình đánh nhầm. cụm đó là "file of em"
Có thể mình diển đạt hơi khó hiểu. Ý của mình là trong file đó, có thể dùng ADO để tìm những phần tử có trong cột A mà không có trong cột B (và ngược lại)? Nếu được thì mong bạn và các anh chị chỉ dẩn.

doveandrose · 5/12/15

Ngô Khánh An đã viết:
Hic, mình đánh nhầm. cụm đó là "file of em"
Có thể mình diển đạt hơi khó hiểu. Ý của mình là trong file đó, có thể dùng ADO để tìm những phần tử có trong cột A mà không có trong cột B (và ngược lại)? Nếu được thì mong bạn và các anh chị chỉ dẩn.

theo như tôi được biết thì "file of a" hay "file of em" gì thì đối với anh Vetmini cũng không khác gì mấy

Ngô Khánh An · 5/12/15

E nhờ các a chị nếu biết chỉ cách e cho với. Chứ mấy cái lổi đó nó củng ko đáng để bàn lắm. Lần sau e sẽ khắc phục là được thôi __--__

quanluu · 29/1/16

Gửi bạn file dùng ADO, nhưng ở đây chỉ là bản nháp, chưa trau chuốt, nên bạn cần hoàn thiện hơn. Tiện đây cho mình hỏi, tại sao dùng công thức lọc lại lâu. Mình ko nghĩ thế, vì mình có thể lọc hàng nghìn dòng trong vài giây.

Có 1 mẹo nhỏ là bạn có thể lọc mà ko cần dùng công thức và ADO, chỉ cần sử dụng remove duplicate trong excel, nhưng cần khéo. hi

thec · 9/6/16

Em có 2 file CuaHang và BaoGia
CuaHang có 1 sheet gồm 3 cột: MaHang, ThongTin và DonGia
BaoGia có nhiều sheet A,B,C... mỗi sheet cũng gồm 3 cột MaHang, ThongTin và DonGia
Làm sao để cập nhật đơn giá của file CuaHang mỗi khi nhà cung cấp thay đổi DonGia trong BaoGia
Em thấy ADO hay quá mà chưa biết gì, mong các anh chị ra tay nghĩa hiệp.
Tks!

Hai Lúa Miền Tây · 28/7/16

Tôi xin mượn bài https://docs.google.com/spreadsheets/d/1sJ9vn86-LOqbst-a-h0UqnAiOCHxHU51ap-_kgLrYOQ/edit#gid=0 của anh Hướng để góp vui.
Câu hỏi: Bạn dùng ADO để cho ra kết quả như bên dưới.

quanluu · 28/7/16

Hai Lúa Miền Tây đã viết:
Tôi xin mượn bài https://docs.google.com/spreadsheets/d/1sJ9vn86-LOqbst-a-h0UqnAiOCHxHU51ap-_kgLrYOQ/edit#gid=0 của anh Hướng để góp vui.
Câu hỏi: Bạn dùng ADO để cho ra kết quả như bên dưới.

View attachment 162932

Em không hiểu tại sao bác muốn hiện như vậy, thấy hơi vô lý ("Chân" xuất hiện 1 lần mà dữ liệu cuối hiện 2) Em cố làm theo sao cho giống thui ạ. Bác xem có chấp nhận đc ko?

Mã:

Sub GPE()
    Dim Query As String
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
     With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
        .Open
    End With
  Query = "SELECT A.f1, A.f2, B.f2 " & Chr(10)
  Query = Query & "FROM (select distinct f1, f2 from [D2:E13]) As A " & Chr(10) & "LEFT JOIN [A2:B6] As B ON B.f1 = A.f1 ORDER BY a.f1, b.f2, a.f2"
 
   rs.Open Query, cn
    Range("K2").CopyFromRecordset rs
    rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing


End Sub

Hai Lúa Miền Tây · 28/7/16

quanluu1989 đã viết:
Em không hiểu tại sao bác muốn hiện như vậy, thấy hơi vô lý ("Chân" xuất hiện 1 lần mà dữ liệu cuối hiện 2) Em cố làm theo sao cho giống thui ạ. Bác xem có chấp nhận đc ko?

Mã:

Sub GPE() Dim Query As String Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") With cn .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ ThisWorkbook.FullName _ & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";" .Open End With Query = "SELECT A.f1, A.f2, B.f2 " & Chr(10) Query = Query & "FROM (select distinct f1, f2 from [D2:E13]) As A " & Chr(10) & "LEFT JOIN [A2:B6] As B ON B.f1 = A.f1 ORDER BY a.f1, b.f2, a.f2" rs.Open Query, cn Range("K2").CopyFromRecordset rs rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing End Sub

Cám ơn bạn đã tham gia, tuy nhiên kết quả chưa giống theo như hình mẫu.

quanluu · 28/7/16

Hai Lúa Miền Tây đã viết:
Cám ơn bạn đã tham gia, tuy nhiên kết quả chưa giống theo như hình mẫu.

Tức là ý bác phải giống y chang ạ? có dòng trống ở "Color Detail"? Em dùng thêm code nữa đc ko ạ?(Để trau truốt giống như hìn)

Hai Lúa Miền Tây · 28/7/16

quanluu1989 đã viết:
Tức là ý bác phải giống y chang ạ? có dòng trống ở "Color Detail"? Em dùng thêm code nữa đc ko ạ?(Để trau truốt giống như hìn)

Đúng thế, chỉ dùng ADO, không dùng code thêm nữa bạn.

tan.ngohoangquoc · 28/7/16

Chế lại code của bản kia nhưng chưa tìm ra cách sắp sếp sao cho giống file mẫu

Mã:

Sub GPE()    Dim Query As String
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
     With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
        .Open
    End With
  Query = "SELECT A.f1, A.f2,SWITCH(A.f1 = A.f2, B.f2, A.f1 <> A.f2, null)" & Chr(10)
  Query = Query & "FROM (select distinct f1, f2 from [D2:E13]) As A " & Chr(10) & "RIGHT JOIN [A2:B6] As B ON B.f1 = A.f1 "
   rs.Open Query, cn
    Range("K2").CopyFromRecordset rs
    rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing
End Sub

quanluu · 28/7/16

tan.ngohoangquoc đã viết:

Chế lại code của bản kia nhưng chưa tìm ra cách sắp sếp sao cho giống file mẫu

Mã:

Sub GPE()    Dim Query As String
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
     With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
        .Open
    End With
  Query = "SELECT A.f1, A.f2,SWITCH(A.f1 = A.f2, B.f2, A.f1 <> A.f2, null)" & Chr(10)
  Query = Query & "FROM (select distinct f1, f2 from [D2:E13]) As A " & Chr(10) & "RIGHT JOIN [A2:B6] As B ON B.f1 = A.f1 "
   rs.Open Query, cn
    Range("K2").CopyFromRecordset rs
    rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing
End Sub

Thế này thì lại càng sai so với mẫu (lúc đầu mình dùng iff rùi, thấy ko ổn nên để kiểu đó)

tan.ngohoangquoc · 28/7/16

Kết quả mình có dc từ code

[TABLE="class: grid, width: 303"]
[TR]
[TD]FWT166.1[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT166.1[/TD]
[TD]FWT166.1[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]FWT166.1[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]FWT034[/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]FWT034[/TD]
[TD]Grey[/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]WCF015[/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Mặt ngồi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Chân dài[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]WCF015[/TD]
[TD]Grey[/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Mặt ngồi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Chân dài[/TD]
[TD][/TD]
[/TR]
[/TABLE]

quanluu · 28/7/16

tan.ngohoangquoc đã viết:
Kết quả mình có dc từ code

[TABLE="class: grid, width: 303"]
[TR]
[TD]FWT166.1[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT166.1[/TD]
[TD]FWT166.1[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]FWT166.1[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]FWT034[/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]FWT034[/TD]
[TD]Grey[/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]WCF015[/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Mặt ngồi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Chân dài[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]WCF015[/TD]
[TD]Grey[/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Mặt ngồi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Chân dài[/TD]
[TD][/TD]
[/TR]
[/TABLE]

Mình biết là nó sẽ ntn, nhưng bạn so sánh với bản mẫu là khác nhau nhiều, nó theo quy luật sắp xếp, còn kết quả này chỉ là có đủ dòng và hiện màu với những dòng có f1 = f2 thui, chứ ko theo quy luật của Color detail

doveandrose · 28/7/16

quanluu1989 đã viết:
Mình biết là nó sẽ ntn, nhưng bạn so sánh với bản mẫu là khác nhau nhiều, nó theo quy luật sắp xếp, còn kết quả này chỉ là có đủ dòng và hiện màu với những dòng có f1 = f2 thui, chứ ko theo quy luật của Color detail

thì mình thêm mấy cái Order by zô nữa cho giống . hi hi /-*+/

quanluu · 28/7/16

doveandrose đã viết:
thì mình thêm mấy cái Order by zô nữa cho giống . hi hi

Không đc pro ơi, oder nó theo vần mà bác. Mà em cũng order rùi đấy chứ. Hoặc là em ko hiểu ý bác.

tan.ngohoangquoc · 28/7/16

quanluu1989 đã viết:
Không đc pro ơi, oder nó theo vần mà bác. Mà em cũng order rùi đấy chứ. Hoặc là em ko hiểu ý bác.

Hình như ADO excel không support ROW_NUMBER phải hem bác.

quanluu · 28/7/16

tan.ngohoangquoc đã viết:
Hình như ADO excel không support ROW_NUMBER phải hem bác.

Mình nghĩ là không, ngay cả "case when" nó cũng không support.

doveandrose · 28/7/16

quanluu1989 đã viết:
Không đc pro ơi, oder nó theo vần mà bác. Mà em cũng order rùi đấy chứ. Hoặc là em ko hiểu ý bác.

cái bảng đâu có nhiều cột lắm đâu , cứ nghịch dại vài cột thử xem sao ? xếp vài ba cột 1 lúc xem sao ? ;;;;;;;;;;;

Hai Lúa Miền Tây · 28/7/16

tan.ngohoangquoc đã viết:
Kết quả mình có dc từ code

[TABLE="class: grid, width: 303"]
[TR]
[TD]FWT166.1[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT166.1[/TD]
[TD]FWT166.1[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]FWT166.1[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]FWT034[/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Khung mặt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]FWT034[/TD]
[TD]Grey[/TD]
[/TR]
[TR]
[TD]FWT034[/TD]
[TD]Chân[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]WCF015[/TD]
[TD]white[/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Mặt ngồi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Chân dài[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]WCF015[/TD]
[TD]Grey[/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Mặt ngồi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WCF015[/TD]
[TD]Chân dài[/TD]
[TD][/TD]
[/TR]
[/TABLE]

RÁng chút nữa đi bạn, gần được rồi đó.

quanluu · 29/7/16

doveandrose đã viết:
cái bảng đâu có nhiều cột lắm đâu , cứ nghịch dại vài cột thử xem sao ? xếp vài ba cột 1 lúc xem sao ?

Em vẫn chưa nghĩ ra cách order, nhưng em dùng mẹo 1 chút để ra kết quả giống đề bài vậy.

Mong 2 pro @Hai Ly Mien Tay và @doveandrose chỉ giáo thêm.

Mã:

Sub GPE()
    Dim Query As String
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
     With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName _
        & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
        .Open
    End With
  Query = "select f1,f2, iif(f1=f2,f3,null) from (SELECT A.f1, A.f2, iif(a.f1 = a.f2,B.f2, b.f2 + '1') as f3 " & Chr(10)
  Query = Query & "FROM (select distinct f1, f2 from [D2:E13]) As A " & Chr(10) & "LEFT JOIN [A2:B6] As B ON B.f1 = A.f1) order by f1,f3"
   rs.Open Query, cn
    Range("K2").CopyFromRecordset rs
    rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing
End Sub

Hai Lúa Miền Tây · 29/7/16

quanluu1989 đã viết:

Em vẫn chưa nghĩ ra cách order, nhưng em dùng mẹo 1 chút để ra kết quả giống đề bài vậy.

Mong 2 pro @Hai Ly Mien Tay và @doveandrose chỉ giáo thêm.

Mã:

Sub GPE()
    Dim Query As String
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
     With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName _
        & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
        .Open
    End With
  Query = "select f1,f2, iif(f1=f2,f3,null) from (SELECT A.f1, A.f2, iif(a.f1 = a.f2,B.f2, b.f2 + '1') as f3 " & Chr(10)
  Query = Query & "FROM (select distinct f1, f2 from [D2:E13]) As A " & Chr(10) & "LEFT JOIN [A2:B6] As B ON B.f1 = A.f1) order by f1,f3"
   rs.Open Query, cn
    Range("K2").CopyFromRecordset rs
    rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing
End Sub

Kết quả đúng rồi, tuy nhiên nên tìm cách nào đó dùng 1 sub query thôi.

quanluu · 29/7/16

Hai Lúa Miền Tây đã viết:
Kết quả đúng rồi, tuy nhiên nên tìm cách nào đó dùng 1 sub query thôi.

Hóa ra order cũng dùng đc công thức. Em sửa lại ntn ạ.

Mã:

Sub GPE()
    Dim Query As String
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
     With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
        .Open
    End With
  Query = "SELECT A.f1, A.f2, iif(a.f1=a.f2,B.f2,null) " & Chr(10)
  Query = Query & "FROM (select distinct f1, f2 from [D2:E13]) As A " & Chr(10) & "LEFT JOIN [A2:B6] As B ON B.f1 = A.f1 ORDER BY a.f1, IIF(A.f1 = a.f2,b.f2,b.f2 + '1')"
 
   rs.Open Query, cn
    Range("K2").CopyFromRecordset rs
    rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing


End Sub

Hai Lúa Miền Tây · 29/7/16

quanluu1989 đã viết:

Hóa ra order cũng dùng đc công thức. Em sửa lại ntn ạ.

Mã:

Sub GPE()
    Dim Query As String
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
     With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
        .Open
    End With
  Query = "SELECT A.f1, A.f2, iif(a.f1=a.f2,B.f2,null) " & Chr(10)
  Query = Query & "FROM (select distinct f1, f2 from [D2:E13]) As A " & Chr(10) & "LEFT JOIN [A2:B6] As B ON B.f1 = A.f1 ORDER BY a.f1, IIF(A.f1 = a.f2,b.f2,b.f2 + '1')"
 
   rs.Open Query, cn
    Range("K2").CopyFromRecordset rs
    rs.Close: cn.Close: Set rs = Nothing: Set cn = Nothing


End Sub

Chính xác là như vậy, cảm ơn bạn đã tham gia, mong rằng đề tài này tiếp tục có nhiều thành viên quan tâm giống như bạn.

quanluu · 29/7/16

Tiện đây em cũng xin góp vui 1 bài toán về Tính tiền điện.
Các bạn coi file để rõ bài toán.

sealand · 29/7/16

quanluu1989 đã viết:
Tiện đây em cũng xin góp vui 1 bài toán: Tính tiền điện bằng ADO.
Các bạn coi file để rõ bài toán.

Mình thấy cách đặt vấn đề có gì đó không ổn?

ADO là công cụ quản trị dữ liệu kết nối và khai thác CSDL từ các nguồn có hỗ trợ chứ đâu có tham gia tính toán.
Cái mà bạn nói là kết quả của SQL đấy chứ. Vậy nên, 1 chủ đề thảo luận nó cũng cần có mức độ chính xác nhất định kẻo "thiên hạ" đánh giá nhà ta.

quanluu · 29/7/16

sealand đã viết:
Mình thấy cách đặt vấn đề có gì đó không ổn?

ADO là công cụ quản trị dữ liệu kết nối và khai thác CSDL từ các nguồn có hỗ trợ chứ đâu có tham gia tính toán.
Cái mà bạn nói là kết quả của SQL đấy chứ. Vậy nên, 1 chủ đề thảo luận nó cũng cần có mức độ chính xác nhất định kẻo "thiên hạ" đánh giá nhà ta.

Em cảm ơn anh đã góp ý, em đã sửa bài rùi ạ.

Hai Lúa Miền Tây · 29/7/16

quanluu1989 đã viết:
Em cảm ơn anh đã góp ý, em đã sửa bài rùi ạ.

Cái này cho vào hàm iif, kết hợp phép toán +,-,* là ra.
Đại loại ví dụ như sau:

IIf([SoDien]<=100,[SoDien]*2000,IIf([SoDien]<=200,100*2000+([SoDien]-100)*1600,IIf([SoDien]<=300,100*2000+100*1600+([SoDien]-200)*1200,IIf([SoDien]<=500,100*2000+100*1600+100*1200+([SoDien]-300)*1000,100*2000+100*1600+100*1200+200*1000+([SoDien]-500)*700))))

BẠn test thử nhé, xin lỗi vì chưa kiểm tra.

Đố vui về ADO, DAO. (2 người xem)

Người dùng đang xem chủ đề này

Đang đi tìm hòn đá

❆❆❆❆❆❆❆❆

File đính kèm

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Đang đi tìm hòn đá

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

Hướng tới tương lai

File đính kèm

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

File đính kèm

Đang đi tìm hòn đá

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Thành viên gắn bó

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Hướng tới tương lai

File đính kèm

❆❆❆❆❆❆❆❆

File đính kèm

Hướng tới tương lai

File đính kèm

Thành viên thường trực

Hướng tới tương lai

Thành viên thường trực

Thành viên chính thức

Hướng tới tương lai

Thành viên chính thức

❆❆❆❆❆❆❆❆

File đính kèm

Hướng tới tương lai

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

File đính kèm

Hướng tới tương lai

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

Thành viên gắn bó

File đính kèm

Hướng tới tương lai

Thành viên gắn bó

Thành viên gạo cội

Thành viên tích cực

Thành viên gạo cội

Thành viên gắn bó

Thành viên tích cực

Hướng tới tương lai

Thành viên tích cực

❆❆❆❆❆❆❆❆

Thành viên gạo cội

Thành viên tích cực

Thành viên gạo cội

Đang đi tìm hòn đá

❆❆❆❆❆❆❆❆

File đính kèm

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Hướng tới tương lai

Thành viên mới

❆❆❆❆❆❆❆❆

Đang đi tìm hòn đá

Thành viên gắn bó

❆❆❆❆❆❆❆❆

Đang đi tìm hòn đá

hello

Đang đi tìm hòn đá

hello

Đang đi tìm hòn đá