Bài tập về ADO căn bản. (2 người xem)

[Hai Lúa Miền Tây]

Chẳng hiểu sao nữa, do bị lỗi vậy nên khi chạy code lần 2 cũng không được luôn
Cảm ơn Anh nhiều

Vậy anh test thử theo cách sau coi có lỗi không nhé.

[GPECODE=sql]Sub Them1()
Dim lsSQL As String, cnn As Object, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & ThisWorkbook.Path & "\Database.xls" & _
";Extended Properties=Excel 8.0;"
.Open
End With
lsSQL = "select * from [tblData$]"
With lrs
.Open lsSQL, cnn, 1, 3
.AddNew
![ID] = 415
![W_HDATE] = #10/1/2013#
![PONO] = "DW12WQ009"
![MATERIAL NAME] = "POLY ZIPPER #5"
![COLOR NAME] = "BEIGE"
![UNIT] = "M"
![SUPPLIER] = "HHH VIETNAM"
.Update
.Close
End With
Set lrs = Nothing
cnn.Close: Set cnn = Nothing

End Sub

[/GPECODE]

 

[viehoai]

Vậy anh test thử theo cách sau coi có lỗi không nhé.

[GPECODE=sql]Sub Them1()
Dim lsSQL As String, cnn As Object, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & ThisWorkbook.Path & "\Database.xls" & _
";Extended Properties=Excel 8.0;"
.Open
End With
lsSQL = "select * from [tblData$]"
With lrs
.Open lsSQL, cnn, 1, 3
.AddNew
![ID] = 415
![W_HDATE] = #10/1/2013#
![PONO] = "DW12WQ009"
![MATERIAL NAME] = "POLY ZIPPER #5"
![COLOR NAME] = "BEIGE"
![UNIT] = "M"
![SUPPLIER] = "HHH VIETNAM"
.Update
.Close
End With
Set lrs = Nothing
cnn.Close: Set cnn = Nothing

End Sub

[/GPECODE]

Oh.. Chạy ngon Anh a
Cảm ơn Anh nhiều, cũng là thêm một cái mới. Nhưng với câu lệnh trên tạo thêm 1 record mới thì tại sao lại không Insert mà lại Update nhỉ

 

[Hai Lúa Miền Tây]

Oh.. Chạy ngon Anh a
Cảm ơn Anh nhiều, cũng là thêm một cái mới. Nhưng với câu lệnh trên tạo thêm 1 record mới thì tại sao lại không Insert mà lại Update nhỉ

Máy em thì chạy ngon và trước giờ em đều sử dụng 2 cách trên nhưng không thấy lỗi.

 

[Hai Lúa Miền Tây]

[TIP]Bài tập 6: Cập nhật 1 mẫu tin vào cơ sở dữ liệu[/TIP]

1./ Cú pháp:

[GPECODE=sql]UPDATE [Tên Bảng] SET [Cột 1]=GiaTri_1, [Cột 2]=GiaTri_2, ...,[Cột n]=GiaTri_n
WHERE [Tên_Cột_Và_Điều_Kiện_Update][/GPECODE]

Lưu ý phần GiaTri:

• Cập nhật ngày thì phải thêm # bao quanh ngày (VD:#15/06/1977#)
• Cập nhật số thì chỉ ghi số, không cần thêm gì cả
• Cập nhật dạng chuổi thì phải thêm dấu nháy bao quanh chuổi đó(VD: 'HLMT')

2./ Bài Tập:

[INFO1]Anh, chị hãy Cập nhật 1 mẫu tin sau vào cơ sở dữ liệu với điều kiện là cột ID=415

• TP=C
• POQTY=1000
• INPUTQTY=600
• BALANCE=-400
• PRICE=0.1
• M_UNIT=USD.
• AMOUNT=60
• ORIGIN=VIETNAM

[/INFO1]

Lưu Ý: Với csdl bên dưới, bạn phải cập nhật được 1 mẫu tin theo bài tập số 5

Tập tin đính kèm

• 
  Database.rar (23.1 KB, 13 lần tải)

 

[viehoai]

[TIP]Bài tập 6: Cập nhật 1 mẫu tin vào cơ sở dữ liệu[/TIP]

1./ Cú pháp:

[GPECODE=sql]UPDATE [Tên Bảng] SET [Cột 1]=GiaTri_1, [Cột 2]=GiaTri_2, ...,[Cột n]=GiaTri_n
WHERE [Tên_Cột_Và_Điều_Kiện_Update][/GPECODE]

Lưu ý phần GiaTri:

• Cập nhật ngày thì phải thêm # bao quanh ngày (VD:#15/06/1977#)
• Cập nhật số thì chỉ ghi số, không cần thêm gì cả
• Cập nhật dạng chuổi thì phải thêm dấu nháy bao quanh chuổi đó(VD: 'HLMT')

2./ Bài Tập:

[INFO1]Anh, chị hãy Cập nhật 1 mẫu tin sau vào cơ sở dữ liệu với điều kiện là cột ID=415

• TP=C
• POQTY=1000
• INPUTQTY=600
• BALANCE=-400
• PRICE=0.1
• M_UNIT=USD.
• AMOUNT=60
• ORIGIN=VIETNAM

[/INFO1]

Lưu Ý: Với csdl bên dưới, bạn phải cập nhật được 1 mẫu tin theo bài tập số 5

Tập tin đính kèm

• 
  Database.rar (23.1 KB, 13 lần tải)

Nhờ Anh test thử giúp em xem có lỗi không nhé
[GPECODE=vb]Sub Update_Data_ADO()
Dim lsSQL As String, cnn As Object, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\Database.xls" & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
lsSQL = "UPDATE [tblData$] " & _
"SET [TP]='C', [POQTY] = 1000, [INPUTQTY] = 600, [BALANCE] = -400, [PRICE] = 0.1, [M_UNIT]='USD', [AMOUNT] = 60, [ORIGIN] = 'VIETNAM' " & _
"WHERE ID = 415"
lrs.Open lsSQL, cnn, 3, 1
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub


[/GPECODE]

 

[Hai Lúa Miền Tây]

Nhờ Anh test thử giúp em xem có lỗi không nhé
[GPECODE=vb]Sub Update_Data_ADO()
Dim lsSQL As String, cnn As Object, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\Database.xls" & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
lsSQL = "UPDATE [tblData$] " & _
"SET [TP]='C', [POQTY] = 1000, [INPUTQTY] = 600, [BALANCE] = -400, [PRICE] = 0.1, [M_UNIT]='USD', [AMOUNT] = 60, [ORIGIN] = 'VIETNAM' " & _
"WHERE ID = 415"
lrs.Open lsSQL, cnn, 3, 1
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub


[/GPECODE]

Code chạy ngon, nhưng giá trị 'USD' của bài tập là 'USD.' anh thiếu mất dấu chấm

 

[viehoai]

Đến đây em cũng đã biết được từ ADO
1. Lấy dữ liệu từ file đang đóng
2. Truyền dữ liệu vào file đang đóng
- Thêm record
- Cập nhật
- Xóa (học thêm từ ADO toán tập)
Bây giờ mong được học các hàm
Trước đây em có tự đọc ADO toàn tập của anh Duyệt nhưng không hiểu nổi, nhờ Topic này em đọc lại sách do Anh Duyệt viết có hiểu được phần nào
Cảm ơn Anh Hai Lúa Miền Tây rất nhiều.
-
Ah, code bài 101 của Anh máy ở cơ quan chạy vẫn bị như code của em viết (csdl bị lỗi sau khi chạy code), máy ở nhà chạy ngon. Không biết lý do gì nhỉ? Trong lúc code bài 105 của em chạy máy ở nhà bị lỗi thì Anh test lại chạy khon ???

 

[Hai Lúa Miền Tây]

Đến đây em cũng đã biết được từ ADO
1. Lấy dữ liệu từ file đang đóng
2. Truyền dữ liệu vào file đang đóng
- Thêm record
- Cập nhật
- Xóa (học thêm từ ADO toán tập)
Bây giờ mong được học các hàm
Trước đây em có tự đọc ADO toàn tập của anh Duyệt nhưng không hiểu nổi, nhờ Topic này em đọc lại sách do Anh Duyệt viết có hiểu được phần nào
Cảm ơn Anh Hai Lúa Miền Tây rất nhiều.
-
Ah, code bài 101 của Anh máy ở cơ quan chạy vẫn bị như code của em viết (csdl bị lỗi sau khi chạy code), máy ở nhà chạy ngon. Không biết lý do gì nhỉ? Trong lúc code bài 105 của em chạy máy ở nhà bị lỗi thì Anh test lại chạy khon ???

1./ Sắp xếp lại các trường theo thứ tự

*/Cú pháp:

[TIP]SELECT Cot_1,Cot_2,...,Cot_n
FROM TenBang
ORDER BY TenCotCanSapXep ASC|DESC[/TIP]

* Chú ý:

• Asc: Sắp xếp theo thứ tự tăng dần
• Desc: Sắp xếp theo thứ tự giảm dần

2./ Xóa 1 mẫu tin trong csdl


* Cú pháp:

[TIP]DELETE FROM TenBangCanXoa
WHERE DieuKienCacCotCanXoa[/TIP]

* Chú ý: Việc xóa 1 mẫu tin trong csdl là excel thì ado không hổ trợ nên em chưa đề cập.


Đến đây là phần căn bản đã xong, nếu có thời gian chúng ta sẽ thảo luận về 2 phần sau:

1. Các hàm căn bản.
2. Phần nâng cao.

 

[nmhung49]

[GPECODE=vb]
lsSQL = "UPDATE [tblData$] " & "SET [TP]='C', [POQTY] = 1000, [INPUTQTY] = 600,
[BALANCE] = -400, [PRICE] = 0.1, [M_UNIT]='USD',
[AMOUNT] = 60, [ORIGIN] = 'VIETNAM' " & "WHERE ID = 415"
[/GPECODE]

[NOTE1]
Nếu chúng ta bỏ mệnh đề Where trong câu lệnh SQL Update thì mặc định cú pháp UPDATE sẽ Update toàn bộ các giá trị trường [TP], [POQTY].... được Set trước mệnh đề Where
[/NOTE1]

 

[Hai Lúa Miền Tây]

[GPECODE=vb]
lsSQL = "UPDATE [tblData$] " & "SET [TP]='C', [POQTY] = 1000, [INPUTQTY] = 600,
[BALANCE] = -400, [PRICE] = 0.1, [M_UNIT]='USD',
[AMOUNT] = 60, [ORIGIN] = 'VIETNAM' " & "WHERE ID = 415"
[/GPECODE]

[NOTE1]
Nếu chúng ta bỏ mệnh đề Where trong câu lệnh SQL Update thì mặc định cú pháp UPDATE sẽ Update toàn bộ các giá trị trường [TP], [POQTY].... được Set trước mệnh đề Where
[/NOTE1]

Đó là điều đương nhiên thôi bạn. Where là điều kiện để chọn lọc ra những dữ liệu thỏa điều kiện để cập nhật. Do vậy điều kiện where thì không thể thiếu nếu như bạn muốn chỉnh sửa mẫu tin nào đó.

 

[ndu96081631]

Lọc duy nhất khi dữ liệu vượt quá 65536 dòng

Tôi xây dựng code lọc duy nhất dùng ADO như sau:

Sub ADO_Unique(ByVal DataRange As Range, ByVal FieldName As String, ByVal HasTitle As Boolean, ByVal Target As Range)
  Dim cnn As Object
  Dim rsData As Object
  
  Dim szConn As String, szSQL As String
  Dim wkb As Workbook, wks As Worksheet
  
  Set wks = DataRange.Parent
  Set wkb = wks.Parent

  If Val(Application.Version) < 12 Then
      szConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & wkb.FullName & ";" & _
                "Extended Properties=""Excel 8.0;HDR=" & IIf(HasTitle, "Yes", "No") & """;"
  Else
      szConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & wkb.FullName & ";" & _
                "Extended Properties=""Excel 12.0;HDR=" & IIf(HasTitle, "Yes", "No") & """;"
  End If
  
  szSQL = "SELECT DISTINCT " & FieldName & " FROM [" & wks.Name & "$" & DataRange.Address(0, 0) & "]"
     
  Set cnn = CreateObject("ADODB.Connection")
  Set rsData = CreateObject("ADODB.Recordset")
  cnn.Open szConn
  rsData.Open szSQL, cnn, 3, 3
  Target.CopyFromRecordset rsData
  rsData.Close: Set rsData = Nothing
  cnn.Close: Set cnn = Nothing
End Sub

Đương nhiên là dùng SELECT DISTINCT để lấy Unique rồi
Tiếp theo là code chính để lọc:

Sub Main()
  Dim t As Double
  Dim rng As Range, Target As Range
  t = Timer
  Set rng = Range("A2:A[COLOR=#ff0000]65536[/COLOR]")
  Set Target = Range("E2")
  Range(Target.Offset(1), Target.Offset(1).End(xlDown)).ClearContents
  ADO_Unique rng, Target.Value, True, Target.Offset(1)
  MsgBox Format(Timer - t, "0.000")
End Sub

Vấn đề ở đây là nếu sửa số 65536 thành số lớn hơn thì code lập tức bảo lỗi
Xin hỏi:
- Có phải code tôi có sai gì đó chăng?
- Hay SELECT DISTINCT có giới hạn?
- Nếu là code của tôi bị sai thì phải sửa lại thế nào mới đúng
-------------------
Ngoài ra cũng xin hỏi thêm: Nếu biến FieldName được gán từ 1 chuổi là tiếng Việt có dấu thì code cũng lỗi. Trường hợp này ta xử lý sao?

 

[Nguyễn Duy Tuân]

Sub Main()
  Dim t As Double
  Dim rng As Range, Target As Range
  t = Timer
  Set rng = Range("A2:A[COLOR=#ff0000]65536[/COLOR]")
  Set Target = Range("E2")
  Range(Target.Offset(1), Target.Offset(1).End(xlDown)).ClearContents
  ADO_Unique rng, Target.Value, True, Target.Offset(1)
  MsgBox Format(Timer - t, "0.000")
End Sub

Vấn đề ở đây là nếu sửa số 65536 thành số lớn hơn thì code lập tức bảo lỗi
Xin hỏi:
- Có phải code tôi có sai gì đó chăng?
- Hay SELECT DISTINCT có giới hạn?
- Nếu là code của tôi bị sai thì phải sửa lại thế nào mới đúng
-------------------
Ngoài ra cũng xin hỏi thêm: Nếu biến FieldName được gán từ 1 chuổi là tiếng Việt có dấu thì code cũng lỗi. Trường hợp này ta xử lý sao?

Có lẽ chỉ lỗi trên Excel 2003 vì 65536 là giới hạn dòng cuối.

 

[Hoàng Trọng Nghĩa]

Tôi xây dựng code lọc duy nhất dùng ADO như sau:

Vấn đề ở đây là nếu sửa số 65536 thành số lớn hơn thì code lập tức bảo lỗi
Xin hỏi:
- Có phải code tôi có sai gì đó chăng?
- Hay SELECT DISTINCT có giới hạn?
- Nếu là code của tôi bị sai thì phải sửa lại thế nào mới đúng
-------------------
Ngoài ra cũng xin hỏi thêm: Nếu biến FieldName được gán từ 1 chuổi là tiếng Việt có dấu thì code cũng lỗi. Trường hợp này ta xử lý sao?

Có lẽ cấu trúc của Thầy bị lỗi tại dòng này:

rsData.Open szSQL, cnn, 3, 3

Nhưng vì trình độ em không biết giải thích như thế nào, nhưng em có giải pháp!

Thầy chép hàm ExcelConnect này vô file đó, thêm vài ngàn dòng ở cột A1 cũng OK:

Option Explicit
Public ObjConnect As Object
Global Const adStateOpen = 1

Function ExcelConnect(ByVal AppPath As String, ByVal ExcelFileName As String) As Boolean
    On Error GoTo ErrorHandle
    
    Dim FullPath As String, ConnString As String
    
    FullPath = AppPath & "\" & ExcelFileName
    
    If Val(Application.Version) < 12 Then
        ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & FullPath & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Else
        ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & FullPath & ";" & _
        "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    End If
    
    Set ObjConnect = CreateObject("ADODB.Connection")
    
    With ObjConnect
        .Mode = 3
        .ConnectionTimeout = 30
        .CursorLocation = 3
        .ConnectionString = ConnString
        .Open
    End With
    
    ExcelConnect = True
    
    ObjConnect.Close
    Exit Function
    
ErrorHandle:
    ExcelConnect = False
    Err.Clear
End Function

Sau đó thực hiện thủ tục sau:

Sub LocDuyNhat()

    Dim FileName As String, SheetName As String, FieldName As String, _
        AppPath As String, sSQL As String, ObjRcs As Object
    
    AppPath = ThisWorkbook.Path
    FileName = "ADO_Unique.xlsm"
    SheetName = "[Sheet1$] "
    FieldName = "[Title] "
    
    If ExcelConnect(AppPath, FileName) = False Then
        MsgBox "Không kêt nôi", vbOKOnly + vbExclamation, "Thông báo"
        GoTo ExitSub
    Else
        sSQL = "SELECT DISTINCT " & FieldName & " FROM " & SheetName
        'sSQL = "SELECT " & FieldName & " FROM " & SheetName & "GROUP BY " & FieldName
        
        Set ObjRcs = CreateObject("ADODB.Recordset")
        
        On Error GoTo SheetFieldNameErr
        
        ObjConnect.Open
        
        ObjRcs.Open sSQL, ObjConnect, 0, 1, 1
        
        If ObjRcs.EOF Then
            MsgBox "Không có dieu kien này", vbOKOnly + vbInformation, "THÔNG BÁO"
            GoTo ExitSub
        Else
            Sheet1.Range("E3").CopyFromRecordset ObjRcs
        End If
    End If
      
ExitSub:
      
    Set ObjRcs = Nothing
    
    If Not ObjConnect Is Nothing Then
        If (ObjConnect.State And adStateOpen) = adStateOpen Then ObjConnect.Close
        Set ObjConnect = Nothing
    End If
    
    Exit Sub
    
SheetFieldNameErr:
    
    MsgBox "Ten Sheet hoac ten Tieu de cot chua dung, xin kiem tra lai!", vbCritical, "THÔNG BÁO"
    Resume ExitSub
End Sub

Thầy kiểm tra xem còn lỗi không!

 

[ndu96081631]

Có lẽ chỉ lỗi trên Excel 2003 vì 65536 là giới hạn dòng cuối.

File của mình là xlsm mà Tuân
----------------------------------

Có lẽ cấu trúc của Thầy bị lỗi tại dòng này:

rsData.Open szSQL, cnn, 3, 3

Tôi không nghĩ thế, vì thay 3, 3 thành:
0, 1
0, 2
0, 3
0, 4
....
....
3, 1
3, 2
3, 3
3, 4
Hết các trường hợp cũng vẫn lỗi
------------------
Để dùng code mới nghiên cứu xem

 

[Hoàng Trọng Nghĩa]

File của mình là xlsm mà Tuân
----------------------------------

Tôi không nghĩ thế, vì thay 3, 3 thành:
0, 1
0, 2
0, 3
0, 4
....
....
3, 1
3, 2
3, 3
3, 4
Hết các trường hợp cũng vẫn lỗi
------------------
Để dùng code mới nghiên cứu xem

Đã thử dữ liệu 500,000 dòng (năm trăm ngàn dòng), thời gian của DISTINCT là 74s, của GROUP BY là 66s (sao em này lại lẹ hơn nhỉ).

 

[ndu96081631]

Đã thử dữ liệu 500,000 dòng (năm trăm ngàn dòng), thời gian của DISTINCT là 74s, của GROUP BY là 66s (sao em này lại lẹ hơn nhỉ).

Máy tôi chạy mất 10s cho dữ liệu 600,000 dòng
------------
Nhưng vấn đề không phải nằm ở đó, nếu tôi sửa đoạn:

szSQL = "SELECT DISTINCT " & FieldName & " FROM [" & wks.Name & "$" & DataRange.Address(0, 0) & "]"

Thành:

szSQL = "SELECT DISTINCT " & FieldName & " FROM [" & wks.Name & "$]"

Tức không chỉ chính xác đến vùng nào, cứ trong sheet có cái gì thì lọc hết ---> Code sẽ không lỗi
Nhưng như thế lại không hay lắm vì có phải lúc nào tôi cũng muốn lấy UserdRange đâu. Ví dụ UsedRange tại Sheet 1 là vùng A1:V100, thế nhưng tôi lại muốn lọc chính xác vùng C5:C50 thôi thì tính sao?

 

[hungpecc1]

Để tự động hoá công nhiều hơn công việc đang làm , em có chập chững đoc tài liệu ADO trên diễn đàn và test thử 1 đoạn code ----> nhưng không hiểu sao ADO không lấy được hết dữ liệu trên sheets cụ thể như file đính kèm :

Kết quả là [E2:H3] không giống với giữ liệu file nguồn,

Các anh có thể chỉ giúp code mình sai và thiếu ở chỗ nào không?

 

[Hoàng Trọng Nghĩa]

Để tự động hoá công nhiều hơn công việc đang làm , em có chập chững đoc tài liệu ADO trên diễn đàn và test thử 1 đoạn code ----> nhưng không hiểu sao ADO không lấy được hết dữ liệu trên sheets cụ thể như file đính kèm :

Kết quả là [E2:H3] không giống với giữ liệu file nguồn,

Các anh có thể chỉ giúp code mình sai và thiếu ở chỗ nào không?

Bạn nói không đúng là không đúng cái gì hả bạn? Hay chỉ thiếu cái tiêu đề?

 

[Hai Lúa Miền Tây]

Để tự động hoá công nhiều hơn công việc đang làm , em có chập chững đoc tài liệu ADO trên diễn đàn và test thử 1 đoạn code ----> nhưng không hiểu sao ADO không lấy được hết dữ liệu trên sheets cụ thể như file đính kèm :

Kết quả là [E2:H3] không giống với giữ liệu file nguồn,

Các anh có thể chỉ giúp code mình sai và thiếu ở chỗ nào không?

Bạn thử lại hàm sau:

[GPECODE=sql]Function GetData(FileName As String, SheetName As String)
Dim cnn As Object, rst As Object, lSQL As String
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.recordset")
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 * FROM [" & SheetName & "];"
rst.Open lSQL, cnn, 3, 1
GetData = rst.GetRows
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Function

[/GPECODE]

 

[ndu96081631]

Để tự động hoá công nhiều hơn công việc đang làm , em có chập chững đoc tài liệu ADO trên diễn đàn và test thử 1 đoạn code ----> nhưng không hiểu sao ADO không lấy được hết dữ liệu trên sheets cụ thể như file đính kèm :

Kết quả là [E2:H3] không giống với giữ liệu file nguồn,

Các anh có thể chỉ giúp code mình sai và thiếu ở chỗ nào không?

Thí nghiệm thì thấy hình như vấn đề nằm ở dữ liệu tại E4:H447 của file kquaSAP.xlsx thì phải... Vì nếu chuyển dữ liệu vùng này thành text thì code chạy chính xác
Cũng lạ ghê! Không hiểu

 

[Hai Lúa Miền Tây]

Thí nghiệm thì thấy hình như vấn đề nằm ở dữ liệu tại E4:H447 của file kquaSAP.xlsx thì phải... Vì nếu chuyển dữ liệu vùng này thành text thì code chạy chính xác
Cũng lạ ghê! Không hiểu

Vấn đề nằm tại chổ IMEX=1
Nếu chọn IMEX=1 thì sẽ nhập toàn bộ dữ liệu, trương hợp trên là do IMEX =1 dư 1 khoảng cách nên nó chỉ lấy dữ liệu là dạng số, lý do dòng dầu tiên của dữ liệu là dạng số nên nó hiểu kiểu trường đó là số, nên kiểu text không vào là điều dể hiểu.

 

[ndu96081631]

Bạn thử lại hàm sau:

[GPECODE=sql]Function GetData(FileName As String, SheetName As String)
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"";"

Vậy hổng lẽ vấn đề chỉ nằm ở chổ viết IMEX=1 sai thành IMEX =1 sao ta? (dư 1 khoảng trắng?)

 

[hungpecc1]

Bạn nói không đúng là không đúng cái gì hả bạn? Hay chỉ thiếu cái tiêu đề?

ý mình là lấy toàn bộ dữ liệu nhưng các ô E1 3 không lấy được dữ liệu

 

[Hai Lúa Miền Tây]

Vậy hổng lẽ vấn đề chỉ nằm ở chổ viết IMEX=1 sai thành IMEX =1 sao ta? (dư 1 khoảng trắng?)

Chính xác là như vậy Thầy à.

 

[hungpecc1]

Chính xác là như vậy Thầy à.

em mất cả buổi google tìm được cái này mà vẫn không ra <-- Cảm ơn anh ndu và a Hai lúa miền tây nhiều
Như vậy IMEX = 1 --->
To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base:
http://support.microsoft.com/kb/194124/EN-US

 

[Hai Lúa Miền Tây]

em mất cả buổi google tìm được cái này mà vẫn không ra -

Không ra là sao bạn, mình thấy bỏ khoảng trắng thừa "IMEX =1" THÀNH "IMEX=1" là ok mà bạn.

 

[hungpecc1]

Không ra là sao bạn, mình thấy bỏ khoảng trắng thừa "IMEX =1" THÀNH "IMEX=1" là ok mà bạn.

Ah là trước khi post bài lên diễn đàn em tìm trên google mà ko ra, chứ bh nhờ anh giúp đỡ thì ok rồi,

 

[nmhung49]

Vấn đề ở đây là nếu sửa số 65536 thành số lớn hơn thì code lập tức bảo lỗi
Xin hỏi:
- Có phải code tôi có sai gì đó chăng?
- Hay SELECT DISTINCT có giới hạn?
- Nếu là code của tôi bị sai thì phải sửa lại thế nào mới đúng

Theo em không phải lỗi do vượt quá 65536 dòng mà là do anh để trống dòng đầu tiên anh xóa bới dòng đầu tiên và thay đổi

szSQL = "SELECT DISTINCT " & FieldName & " FROM [" & wks.Name & "$" & DataRange.Address(0, 0) & "]"

Thành

szSQL = "SELECT DISTINCT [" & FieldName & "] FROM [" & wks.Name & "$][" & DataRange.Address(0, 0) & "]"

Khi đó sub Main

Sub Main()
  Dim t As Double
  Dim rng As Range, Target As Range
  t = Timer
  Set rng = Range("A1:A650000")
  Set Target = Range("E1)
  Range(Target.Offset(1), Target.Offset(1).End(xlDown)).ClearContents
  ADO_Unique rng, Target.Value, True, Target.Offset(1)
  MsgBox Format(Timer - t, "0.000")
End Sub

Ngoài ra cũng xin hỏi thêm: Nếu biến FieldName được gán từ 1 chuổi là tiếng Việt có dấu thì code cũng lỗi. Trường hợp này ta xử lý sao?

Còn vấn đề tiếng Việt là em nghĩ nó không hỗ trỡ tiếng Việt có dấu. Mong giúp ích được anh

 

[ndu96081631]

Theo em không phải lỗi do vượt quá 65536 dòng mà là do anh để trống dòng đầu tiên anh xóa bới dòng đầu tiên và thay đổi

Thành

szSQL = "SELECT DISTINCT [" & FieldName & "] FROM [" & wks.Name & "$][" & DataRange.Address(0, 0) & "]"

Khi đó sub Main

Sub Main()
  Dim t As Double
  Dim rng As Range, Target As Range
  t = Timer
  Set rng = Range("A1:A650000")
  Set Target = Range("E1)
  Range(Target.Offset(1), Target.Offset(1).End(xlDown)).ClearContents
  ADO_Unique rng, Target.Value, True, Target.Offset(1)
  MsgBox Format(Timer - t, "0.000")
End Sub

Đã thử nghiệm thành công, chỉ cần thay đổi dòng lệnh như bạn đã nói ở trên (không cần xóa dòng trống)
Cảm ơn nhé

 

[ndu96081631]

Một kiểu viết code khác

Hôm nay tình cờ lục tìm trong đống tài liệu mà tôi đã thu thập được lâu nay, phát hiện có 1 kiểu dùng ADO như thế này:

Function GetData(ByVal FileName As String, ByVal RangeAddress As String, Optional ByVal SheetName As String)
  Dim sRef As String, FileType As String
  On Error GoTo ExitFunc
  sRef = IIf(Len(SheetName), SheetName & "$", "") & RangeAddress & """"
  FileType = "*.xls, *.xlsx, *.xlsm, *.xlsb"
  With CreateObject("ADODB.Connection")
    .Open "DRIVER={Microsoft Excel Driver (" & FileType & ")};ReadOnly=1;DBQ=" & FileName
     GetData = .Execute("Select * from """ & sRef).GetRows
ExitFunc:
    .Close
  End With
End Function

Code áp dụng để lấy dữ liệu:

Sub Main()
  Dim Arr
  Dim FileName As String, RangeAddress As String, SheetName As String
  Dim lUb As Long, lLb As Long
  FileName = ThisWorkbook.Path & "\Source.xls"
  RangeAddress = "A1:C22"
  SheetName = "Anh Tuan"
  Arr = GetData(FileName, RangeAddress, SheetName)
  If IsArray(Arr) Then
    Arr = WorksheetFunction.Transpose(Arr)
    lUb = UBound(Arr, 1): lLb = UBound(Arr, 2)
    Range("A1").Resize(lUb, lLb).Value = Arr
  End If
End Sub

Rất ngắn gọn... Đã test và thấy cũng rất ổn
-----------------------
Xin các cao thủ cho ý kiến về code trên!

 

[Hai Lúa Miền Tây]

Còn vấn đề tiếng Việt là em nghĩ nó không hỗ trỡ tiếng Việt có dấu. Mong giúp ích được anh

Thì mình không chọn tiêu đề của nó. Ngoài ra nó cũng không hổ trợ việc đặt tên field là dạng số.

 

[ndu96081631]

Thì mình không chọn tiêu đề của nó..

Nghĩa là sao ta?
Ở đây mình muốn nói đến tiếng Việt trong TÊN FILE nha!

 

[Hoàng Trọng Nghĩa]

Hôm nay tình cờ lục tìm trong đống tài liệu mà tôi đã thu thập được lâu nay, phát hiện có 1 kiểu dùng ADO như thế này:

Function GetData(ByVal FileName As String, ByVal RangeAddress As String, Optional ByVal SheetName As String)
  Dim sRef As String, FileType As String
  On Error GoTo ExitFunc
  sRef = IIf(Len(SheetName), SheetName & "$", "") & RangeAddress & """"
  FileType = "*.xls, *.xlsx, *.xlsm, *.xlsb"
  With CreateObject("ADODB.Connection")
    .Open "DRIVER={Microsoft Excel Driver (" & FileType & ")};ReadOnly=1;DBQ=" & FileName
     [COLOR=#ff0000][B]GetData = .Execute("Select * from """ & sRef).GetRows[/B][/COLOR]
ExitFunc:
    .Close
  End With
End Function

Code áp dụng để lấy dữ liệu:

Sub Main()
  Dim Arr
  Dim FileName As String, RangeAddress As String, SheetName As String
  Dim lUb As Long, lLb As Long
  FileName = ThisWorkbook.Path & "\Source.xls"
  RangeAddress = "A1:C22"
  SheetName = "Anh Tuan"
  Arr = GetData(FileName, RangeAddress, SheetName)
  If IsArray(Arr) Then
    [B][COLOR=#ff0000]Arr = WorksheetFunction.Transpose(Arr)[/COLOR][/B]
    lUb = UBound(Arr, 1): lLb = UBound(Arr, 2)
    Range("A1").Resize(lUb, lLb).Value = Arr
  End If
End Sub

Rất ngắn gọn... Đã test và thấy cũng rất ổn
-----------------------
Xin các cao thủ cho ý kiến về code trên!

Nếu Thầy xuất dữ liệu và nhận giá trị là mảng và tiếp tục xử lý trên mảng rồi gán xuống sheet thì được, tuy nhiên với dữ liệu lớn thì Thầy dùng For để duyệt mảng này sẽ nhanh hơn dùng hàm TRANSPOSE (em đã thử trước nhiều lần).

Riêng nếu xuất dữ liệu mà không xử lý gì thêm nữa thì gán xuống sheet theo em nghĩ Thầy đừng GetRows để làm gì mà dùng CopyFromRecordset sẽ nhanh hơn rất nhiều.

Chỉ là ý kiến cá nhân, xin các cao thủ chỉ giáo thêm.

 

[Hoàng Trọng Nghĩa]

Nghĩa là sao ta?
Ở đây mình muốn nói đến tiếng Việt trong TÊN FILE nha!

Theo em nghĩ tiêu đề mình đặt ra, mà tiêu đề thì thường không có dấu, không dùng tiêu đề chính, nhưng có thể dùng CAPTION của nó (với Access); hoặc tự mình đặt ra và gán thêm khi xuất dữ liệu ở vùng khác.

 

[Hai Lúa Miền Tây]

Nghĩa là sao ta?
Ở đây mình muốn nói đến tiếng Việt trong TÊN FILE nha!

Vì em thấy

Ngoài ra cũng xin hỏi thêm: Nếu biến FieldName được gán từ 1 chuổi là tiếng Việt có dấu thì code cũng lỗi. Trường hợp này ta xử lý sao?

Nên tưởng nó là tên cột
Còn tên file là TV thì mình có thể cho nó vào 1 cell nào đó rồi tham chiếu đến cell có chứa tên file TV là được.

 

[Hai Lúa Miền Tây]

Riêng nếu xuất dữ liệu mà không xử lý gì thêm nữa thì gán xuống sheet theo em nghĩ Thầy đừng GetRows để làm gì mà dùng CopyFromRecordset sẽ nhanh hơn rất nhiều.

Chỉ là ý kiến cá nhân, xin các cao thủ chỉ giáo thêm.

Không phải thế, đã qua sử dụng 2 cách, cách gán vào mãng thì sẽ nhanh hơn đó.

 

[ndu96081631]

Nếu Thầy xuất dữ liệu và nhận giá trị là mảng và tiếp tục xử lý trên mảng rồi gán xuống sheet thì được, tuy nhiên với dữ liệu lớn thì Thầy dùng For để duyệt mảng này sẽ nhanh hơn dùng hàm TRANSPOSE (em đã thử trước nhiều lần).

Riêng nếu xuất dữ liệu mà không xử lý gì thêm nữa thì gán xuống sheet theo em nghĩ Thầy đừng GetRows để làm gì mà dùng CopyFromRecordset sẽ nhanh hơn rất nhiều.

Chỉ là ý kiến cá nhân, xin các cao thủ chỉ giáo thêm.

Chỉ thí nghiệm thôi mà (với 1 kiểu code hơi lạ), còn khi dùng đương nhiên sẽ không TRANSPOSE đâu (điều này tôi biết)
Còn vụ CopyFromRecordset thì tôi không khoái vì nó chỉ hoạt động trên Range (không dùng được khi gán vào các control như ComboBox)
Thêm nữa nó cũng không nhanh hơn GetRows đâu
------------------------

Còn tên file là TV thì mình có thể cho nó vào 1 cell nào đó rồi tham chiếu đến cell có chứa tên file TV là được.

Tôi đã thử nhiểu lần rồi mà chẳng được gì cả! Hai Lúa đã thử chưa?

 

[Hoàng Trọng Nghĩa]

Không phải thế, đã qua sử dụng 2 cách, cách gán vào mãng thì sẽ nhanh hơn đó.

Trên máy của Nghĩa thì lại CopyFromRecordset nhanh hơn, thử dữ liệu vài chục ngàn dòng đi sẽ thấy liền!

Mình nhìn hàm TRANSPOSE thấy rất gọn, nhưng thật ra bên trong hàm đó có thể cũng có 2 vòng lặp For như mình viết thôi. Chính vì vậy mà lại chậm, thậm chí số liệu cũng chưa chắc ổn định với dữ liệu lớn.

 

[Hai Lúa Miền Tây]

Tôi đã thử nhiểu lần rồi mà chẳng được gì cả! Hai Lúa đã thử chưa?

Em đã test và thành công mà Thầy

 

[ndu96081631]

Trên máy của Nghĩa thì lại CopyFromRecordset nhanh hơn, thử dữ liệu vài chục ngàn dòng đi sẽ thấy liền!

Mình nhìn hàm TRANSPOSE thấy rất gọn, nhưng thật ra bên trong hàm đó có thể cũng có 2 vòng lặp For như mình viết thôi. Chính vì vậy mà lại chậm, thậm chí số liệu cũng chưa chắc ổn định với dữ liệu lớn.

Thì tôi đã nói rồi mà: Khi dùng thật sự tôi đâu có dùng TRANSPOSE...
Đồng thời cũng đã nói ở trên: tôi không khoái CopyFromRecordset vì nó chỉ hoạt động trên Range ---> Tôi dùng code để Add Item vào ComboBox cơ mà
---------------------

Em đã test và thành công mà Thầy

Tức thật, lúc nãy cũng thí nghiệm mà sai không đâu vào đâu. Lý ra phải là:

FileName = ThisWorkbook.Path & [COLOR=#ff0000]"\"[/COLOR] & Range("F1").Value

Lại ghi thành:

FileName = ThisWorkbook.Path & Range("F1").Value

Thiếu cha nó dấu "\" của người ta lấy đâu mà chạy
Ẹc... Ẹc... (nhiều khi cũng phát khùng)

 

[asturias]

[TIP]Bài tập 5: Chèn 1 mẫu tin vào cơ sở dữ liệu[/TIP]

1./ Cú pháp:

[GPECODE=sql]INSERT INTO [Tên Bảng] ([Cột 1], [Cột 2], ...,[Cột n])
VALUES (GiaTri_1, GiaTri_2, ...,GiaTri_n)[/GPECODE]

Lưu ý phần GiaTri:

• Nhập ngày thì phải thêm # bao quanh ngày (VD:#15/06/1977#)
• Nhập số thì chỉ ghi số, không cần thêm gì cả
• Nhập dạng chuổi thì phải thêm dấu nháy bao quanh chuổi đó(VD: 'HLMT')

2./ Bài Tập:

[INFO1]Anh, chị hãy nhập liệu 1 mẫu tin sau vào cơ sở dữ liệu

• ID=415
• W_HDATE=10/01/2013
• PONO=DW12WQ009
• MATERIAL NAME=POLY ZIPPER #5
• COLOR NAME=BEIGE
• UNIT=M
• SUPPLIER=HHH VIETNAM

[/INFO1]

Cám ơn Anh vì Chủ đề rất hữu ích.
Em có 02 file khác nhau (1 file Main, 1 file DataBase) cùng 1 thư mục, e đã thực hiện được việc truy suất dữ liệu từ file DataBase sang file Main, nhưng còn Insert, Update (theo như chỉ dẫn), Delete e không thực hiện được từ file Main để tác động tới file DataBase. Hệ thống thường báo lỗi "Sytax error..." hoặc "Cannot Update, database/object is Read-only".
Em rất mong Anh giải đáp lỗi phát sinh này. (nếu em thực hiện lệnh trên 1 file hiện hành thì lại thực hiện được)

 

[Hoàng Trọng Nghĩa]

Cám ơn Anh vì Chủ đề rất hữu ích.
Em có 02 file khác nhau (1 file Main, 1 file DataBase) cùng 1 thư mục, e đã thực hiện được việc truy suất dữ liệu từ file DataBase sang file Main, nhưng còn Insert, Update (theo như chỉ dẫn), Delete e không thực hiện được từ file Main để tác động tới file DataBase. Hệ thống thường báo lỗi "Sytax error..." hoặc "Cannot Update, database/object is Read-only".
Em rất mong Anh giải đáp lỗi phát sinh này. (nếu em thực hiện lệnh trên 1 file hiện hành thì lại thực hiện được)

Khi sử dụng ADO trong EXCEL, UPDATE thì được, nhưng DELETE thì không nha bạn! Nếu xóa bằng cách update các khoảng trắng thì CSDL của bạn trở thành các dòng trắng xen kẻ trông rất xấu. Cái này đã nói trong các bài trước nha bạn.

 

[asturias]

Khi sử dụng ADO trong EXCEL, UPDATE thì được, nhưng DELETE thì không nha bạn! Nếu xóa bằng cách update các khoảng trắng thì CSDL của bạn trở thành các dòng trắng xen kẻ trông rất xấu. Cái này đã nói trong các bài trước nha bạn.

Cám ơn Anh, hôm nay đến cơ quan thì code INSERT, UPDATE lại chạy được, hic. Vậy Anh cho em hỏi, nếu em muốn xóa 1 dòng ở file excel B bằng cách chạy Code từ file A (file B có thể mở hoặc không) thì có cách nào không ạ. Rất mong được các Anh, Chị giải đáp. Em cám ơn.

 

[Hai Lúa Miền Tây]

Cám ơn Anh, hôm nay đến cơ quan thì code INSERT, UPDATE lại chạy được, hic. Vậy Anh cho em hỏi, nếu em muốn xóa 1 dòng ở file excel B bằng cách chạy Code từ file A (file B có thể mở hoặc không) thì có cách nào không ạ. Rất mong được các Anh, Chị giải đáp. Em cám ơn.

Việc xóa = ado thì sẽ không hổ trợ, có thể dùng cách khác = vba là mở file đó lên tìm dòng thỏa điều kiện rồi xóa nó đi, hoặc nếu dùng ado thì dùng cau lệnh update để clear trống dòng đó đi, tuy nhiên theo cách này thì dữ liệu của bạn sẽ không được liên tục.

 

[asturias]

Việc xóa = ado thì sẽ không hổ trợ, có thể dùng cách khác = vba là mở file đó lên tìm dòng thỏa điều kiện rồi xóa nó đi, hoặc nếu dùng ado thì dùng cau lệnh update để clear trống dòng đó đi, tuy nhiên theo cách này thì dữ liệu của bạn sẽ không được liên tục.

Em cám ơn Anh.
Em có 2 file dữ liệu mẫu theo đính kèm.
Nếu có thể Anh và các Anh, Chị trên diễn đàn dùng VBA để em tham khảo xóa đi những dòng dữ liệu ở file B với điều kiện field Stxt có chứa chuỗi "tgt" được không ạ?
Em cám ơn các Anh, Chị!

 

[Hai Lúa Miền Tây]

Em cám ơn Anh.
Em có 2 file dữ liệu mẫu theo đính kèm.
Nếu có thể Anh và các Anh, Chị trên diễn đàn dùng VBA để em tham khảo xóa đi những dòng dữ liệu ở file B với điều kiện field Stxt có chứa chuỗi "tgt" được không ạ?
Em cám ơn các Anh, Chị!

Đây là bài thảo luận về ado nên tôi trả lời về ado nhé.

[GPECODE=sql]Sub XoaDong()
On Error GoTo Handle
Dim cnn As Object, lsSQL As String, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & _
"\B.xls;Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
lsSQL = "UPDATE [General$] " & _
"set [po]=null, [material]=null,[stxt]=null,[order]=null," & _
"[quant]=null,[date]=null where ucase(stxt) like '%TGT%'"
lrs.Open lsSQL, cnn, 3, 1
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
Exit Sub
Handle:
MsgBox Err.Description


End Sub

[/GPECODE]

 

[VetMini]

Vì tựa tô bít này có từ "căn bản", và bài mở đầu có câu "cho người mới học" nên tôi mạn phép góp ý về việc "xoá" dòng dữ liệu trong Excel:

Có lẽ cần nhắc nhở mọi người rằng chính Microsoft khi cung cấp ADO cho sử dụng với Excel có câu giới thiệu như sau:

The rows and columns of a Microsoft Excel spreadsheet closely resemble the rows and columns of a database table. As long as users keep in mind that Microsoft Excel is not a relational database management system, and recognize the limitations that this fact imposes, it often makes sense to take advantage of Excel and its tools to store and analyze data.
​

Chỗ xanh lá: cách thức tình bày dòng và cột của Excel gần giống với hình thức bảng trong CSDL.
Chỗ bôi đỏ: nên ghi nhớ Excel không phải là công cụ quản lý CSDL liên hệ
Chỗ bôi xanh: (dịch thoáng) vì vậy phải chấp nhận rằng có những điều không thể làm được như CSDL thông thường.

Công cụ tiện ích ADO này được đưa vào VBA với mục đich giứp cho việc truy cập dữ liệu theo dạng bảng được thuận lợi. Một khi có thể truy cập dữ liệu theo dạng bảng, nguời ta cũng có thể lợi dụng tính năng của CSDL Liên Hệ và phân tích dữ liệu qua ngôn ngữ SQL.

Sức mạnh của CSDL LH (Relational Database) là phân tích dữ liệu qua sự liên hệ giữa các bảng, các cột... SQL (Structured Query Language) là ngôn ngữ chính thức được đặt ra để hổ trợ việc truy vấn và phân tích dữ liệu. Ngoài công việc đó, chuyện sửa đổi dữ liệu là việc phụ.

Khi dùng ADO như một công cụ quản lý và sửa đổi dữ liệu trong Excel thì ngưòi sử dụng đã cố tính chơi tò "không có chó bắt mèo c..". Điều đó cũng chẳng sao, chỉ hiềm nổi, tục ngữ ta cũng có câu "được đằng chân, lân đằng đâu". Sau một thời gian, người ta quên mất là nhà mình nuôi con mèo chứ không phải con chó.

Trong CSDL LH, các dòng được chứa theo dạng rời rạc, khái niệm dòng đi trước và dòng đi sau hầu như không có (nếu có thì 99% là thiết kế dỏm). Trong Excel, các dòng rõ ràng được ghi theo thứ tự. Nói theo ngôn ngữ CSDL thì là dạng dữ liệu liên tục (sequential) Để thực hiện việc truy vấn theo bảng của SQL, ADO phải miễn cưỡng sử dụng kỹ thuật ISAM (Indexed Sequential Access Method). Nhưng đến đó là giới hạn rồi. Xoá bỏ (delete) một dòng ở lưng chừng là xâm phạm tính chất liên tục. Và hiện tại, ADO không cho phép làm vậy - có thể một phiên bản nào đó trong tương lai sẽ được - nhưng hiện tại thì không.

Ngay cả trong CSDL LH thực tiễn, người ta cũng rất ít khi dùng lệnh DELETE. Thông thường thì người ta đặt một cái cờ (flag) cho mỗi dòng. Nếu cờ ở một trị số quy ước nào đó thì dòng (record) ấy coi như đã bị xoá.

 

[leonguyenz]

Em xin code bằng ADO về tổng hợp dữ liệu từ nhiều sheet, được phép lựa chọn cột để tổng hợp. Trong file ví dụ em làm 2 sheet, dữ liệu 2 cột khác nhau, yêu cầu tổng hợp 4 cột (tùy ý chọn cột) với cột đầu tiên (Mscn) duy nhất, 3 cột sau là tổng lại.
Chân thành cảm ơn !

 

[Lê Duy Thương]

Em xin code bằng ADO về tổng hợp dữ liệu từ nhiều sheet, được phép lựa chọn cột để tổng hợp. Trong file ví dụ em làm 2 sheet, dữ liệu 2 cột khác nhau, yêu cầu tổng hợp 4 cột (tùy ý chọn cột) với cột đầu tiên (Mscn) duy nhất, 3 cột sau là tổng lại.
Chân thành cảm ơn !

không hiểu là chọn cột như thế nào .chứ dữ liệu giống dữ liệu mẫu thì consolidate là xong mà Thảo

 

[leonguyenz]

không hiểu là chọn cột như thế nào .chứ dữ liệu giống dữ liệu mẫu thì consolidate là xong mà Thảo

Em muốn viết code mà anh. Consolidate phải cùng số cột dữ liệu, ở đây mỗi sheet có số cột khác nhau, dữ liệu thật có trên 2 sheet.

 

[Lê Duy Thương]

em muốn viết code mà anh. Consolidate phải cùng số cột dữ liệu, ở đây mỗi sheet có số cột khác nhau, dữ liệu thật có trên 2 sheet.

không biết tôi có nhầm không ?.tôi vẫn thường dùng consolidate đâu cần cùng cột dữ liệu. Chỉ cần tiêu đề và cột mã giống nhau là được mà. Ah mà quang hải đã viết code consolidate ngon lành rồi đấy . Tìm đi

 

[VetMini]

Lập một câu truy vấn tổng quát:

qString = "SELECT Mscn, SUM(<cot1>) AS <cot1>, SUM(<cot2>) AS <cot2>, SUM(<cot3>) AS <cot3> FROM (" _
& " SELECT Mscn, <cotA1> AS <cot1>, <cotA2> AS <cot2>, <cotA3> AS <cot3> FROM BangA WHERE Mscn > 0 " _
& " UNION ALL " _
& " SELECT Mscn, <cotB1> AS <cot1>, <cotB2> AS <cot2>, <cotB3> AS <cot3> FROM BangB WHERE Mscn > 0 " _
& " ) GROUP BY Mscn"

Dùng hàm REPLACE hoặc SUBSTITUTE để đổi các chuỗi <cotN> theo đúng tên cột trước khi truy vấn.

 

[Quang_Hải]

Em xin code bằng ADO về tổng hợp dữ liệu từ nhiều sheet, được phép lựa chọn cột để tổng hợp. Trong file ví dụ em làm 2 sheet, dữ liệu 2 cột khác nhau, yêu cầu tổng hợp 4 cột (tùy ý chọn cột) với cột đầu tiên (Mscn) duy nhất, 3 cột sau là tổng lại.
Chân thành cảm ơn !

ADO thì mình thua rồi. Bài này chắc không dùng consolidate được nên thử làm vầy coi đúng không.

Sub tong_hop()
Dim dl(), kq(1 To 10000, 1 To 4), d As Object, tieude()
Dim sh As Worksheet, i As Long, k As Long, cot As Byte, n
Set d = CreateObject("scripting.dictionary")
tieude = Sheets("Tong Hop").[A4:D4].Value
For Each sh In ThisWorkbook.Worksheets
   If sh.Name <> "Tong Hop" Then
      cot = sh.[IV3].End(1).Column - 5
      dl = sh.Range(sh.[F3], sh.[F65536].End(3)).Resize(, cot)
      For i = 2 To UBound(dl)
         If dl(i, 1) <> 0 Then
            dl(i, cot) = dl(i, cot) * -1
            If Not d.exists(dl(i, 1)) Then
               k = k + 1
               d.Add dl(i, 1), k
               kq(k, 1) = dl(i, 1)
               For n = 2 To cot
                  For m = 2 To 4
                     If dl(1, n) = tieude(1, m) Then
                        kq(k, m) = dl(i, n)
                     End If
                  Next
               Next
            Else
               For n = 2 To cot
                  For m = 2 To 4
                     If dl(1, n) = tieude(1, m) Then
                        kq(d.Item(dl(i, 1)), m) = kq(d.Item(dl(i, 1)), m) + dl(i, n)
                     End If
                  Next
               Next
            End If
         End If
      Next i
   End If
Next sh
Sheets("Tong hop").[A5:D65536].ClearContents
Sheets("Tong hop").[A5].Resize(k, 4) = kq
End Sub

 

[Hai Lúa Miền Tây]

Em xin code bằng ADO về tổng hợp dữ liệu từ nhiều sheet, được phép lựa chọn cột để tổng hợp. Trong file ví dụ em làm 2 sheet, dữ liệu 2 cột khác nhau, yêu cầu tổng hợp 4 cột (tùy ý chọn cột) với cột đầu tiên (Mscn) duy nhất, 3 cột sau là tổng lại.
Chân thành cảm ơn !

Lâu quá mới về thăm nhà, cột để ra kết quả ở sheet3 có điều kiện như thế nào vậy Thảo? theo anh cộng thì nó sẽ là 30.008 thay vì 23.980 của em

 

[leonguyenz]

Lâu quá mới về thăm nhà, cột để ra kết quả ở sheet3 có điều kiện như thế nào vậy Thảo? theo anh cộng thì nó sẽ là 30.008 thay vì 23.980 của em

Dạ, đúng rồi anh, 30,008. Lúc đầu em dùng Consolidate để tổng số liệu, sau đó lại gõ giá trị 0 ở 2 sheet kia để ví dụ mà quên tính tổng lại 1 lần nữa.

 

[Hai Lúa Miền Tây]

Dạ, đúng rồi anh, 30,008. Lúc đầu em dùng Consolidate để tổng số liệu, sau đó lại gõ giá trị 0 ở 2 sheet kia để ví dụ mà quên tính tổng lại 1 lần nữa.

VẬy em dùng code sau nhé.

[GPECODE=sql]Sub Tong_HLMT()
Dim lsSQL As String, cnn As Object, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
.Open
End With
lsSQL = "select F1 as Mscn, sum(f10) as Gbloi,sum(f11) as Other,-sum(f12) as [Total] from " & _
"(SELECT F1, F10, F11, F12 FROM [Sheet1$F4:Q65536] " & _
"union all " & _
"SELECT F1, F4, F5, F6 FROM [Sheet2$F4:K65536]) " & _
"group by f1 having f1>0"
lrs.Open lsSQL, cnn, 3, 1
With Sheet3
.[a:d].Clear
For i = 1 To lrs.Fields.Count
.Cells(4, i).Value = lrs.Fields(i - 1).Name
Next
.[A5].CopyFromRecordset lrs
End With
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing

End Sub
[/GPECODE]

 

[leonguyenz]

Em đã test, code chạy rất tuyệt!
Lúc trước em dùng ADO tổng, nhưng tổng được 1 cột thôi, giờ học thêm được cái mới. Cảm ơn anh nhiều!

 

[Hai Lúa Miền Tây]

Dùng hàm REPLACE hoặc SUBSTITUTE để đổi các chuỗi <cotN> theo đúng tên cột trước khi truy vấn.

Cho mình hỏi là mục đích chuyển đổi để làm gì vậy bạn?

 

[Hoàng Trọng Nghĩa]

Xin cho hỏi, nếu dùng ADO thì mình truy xuất các cột, chẳng hạn 10 cột của một bảng, mình lấy cột 1, 3, 5 và 7 đến 10 (tức là không lấy cột 2, 4, 6) thì mình phải làm sao? Hay phải liệt kê Select theo tên cột ngoại trừ tên của 3 cột kia?

 

[Hai Lúa Miền Tây]

Xin cho hỏi, nếu dùng ADO thì mình truy xuất các cột, chẳng hạn 10 cột của một bảng, mình lấy cột 1, 3, 5 và 7 đến 10 (tức là không lấy cột 2, 4, 6) thì mình phải làm sao? Hay phải liệt kê Select theo tên cột ngoại trừ tên của 3 cột kia?

Chắc chắn là như thế, anh chọn cột nào thì phải select cột đó theo thứ tự anh đặt ra, còn muốn chọn hết là dấu sao (*)

 

[Hoàng Trọng Nghĩa]

Chắc chắn là như thế, anh chọn cột nào thì phải select cột đó theo thứ tự anh đặt ra, còn muốn chọn hết là dấu sao (*)

Mình tưởng đâu chọn 7 to 10 được chứ!

 

[Hai Lúa Miền Tây]

Mình tưởng đâu chọn 7 to 10 được chứ!

Hihi, vậy thì anh cứ test thử nhé.

 

[Hoàng Trọng Nghĩa]

Hihi, vậy thì anh cứ test thử nhé.

Biết làm không được mới hỏi vậy mà.

 

[VetMini]

Nguyên văn bởi VetMini
Dùng hàm REPLACE hoặc SUBSTITUTE để đổi các chuỗi <cotN> theo đúng tên cột trước khi truy vấn.

Cho mình hỏi là mục đích chuyển đổi để làm gì vậy bạn?

2 lý do:

1. Trong đề bài có đặt điều kiện: tuỳ ý chọn cột. Vì vậy ta cứ đặt câu tổng quát rồi đến khi cần truy vấn thì chuyển đổi.

2. Thường thường dùng SQL động, viết câu tổng quát cho dễ nhìn. Khi thực sự truy vấn thì thay tham số vào.
Tôi dùng thuật này cho cả câu chuỗi kết nối.
Const CHUOIKN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<WBPathAndName>;Extended Properties=""Excel 8.0;HDR=No;"";"
.ConnectionString = Replace(CHUOIKN,"<WBPathAndName>",ThisWorkbook.FullName)

 

[Hai Lúa Miền Tây]

2 lý do:

1. Trong đề bài có đặt điều kiện: tuỳ ý chọn cột. Vì vậy ta cứ đặt câu tổng quát rồi đến khi cần truy vấn thì chuyển đổi.

2. Thường thường dùng SQL động, viết câu tổng quát cho dễ nhìn. Khi thực sự truy vấn thì thay tham số vào.
Tôi dùng thuật này cho cả câu chuỗi kết nối.
Const CHUOIKN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<WBPathAndName>;Extended Properties=""Excel 8.0;HDR=No;"";"
.ConnectionString = Replace(CHUOIKN,"<WBPathAndName>",ThisWorkbook.FullName)

Vậy bạn cho 1 ví dụ về việc mà bạn chọn cột tùy ý rồi replace nó xem thử nhé.

 

[Hai Lúa Miền Tây]

Sự khác biệt giữa inner join, left join và right join.

Ví dụ mình có đoạn code như bên dưới, các bạn cho hỏi là giữa inner join, left join và right join có sự khác biệt hay là không? Xin giải thích.

[GPECODE=sql]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
Dim adoConn As Object, adoRS As Object
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 T1.MS,T2.ten,T2.mau,T1.soluong " _
& "from [Data1$] T1 " _
& Sheet3.Range("C4").Value & " [Data2$] T2 " _
& "on T1.ms = T2.ms"
End With
With Sheet3
.[B8:E120].ClearContents
.[B8].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing

End If

End Sub

[/GPECODE]
P/s: Do các phần trên chưa nói đến phần căn bản này nên xin bổ sung thêm, mong các bạn hưởng ứng.

 

[hungpecc1]

Ví dụ mình có đoạn code như bên dưới, các bạn cho hỏi là giữa inner join, left join và right join có sự khác biệt hay là không? Xin giải thích.

P/s: Do các phần trên chưa nói đến phần căn bản này nên xin bổ sung thêm, mong các bạn hưởng ứng.

Vấn đề sử dụng các phép nối bảng trên SQL , em cũng từng ngâm cứu qua( nhưng mà vẫn lơ mơ lù mù ),cụ thể sự khác nhau giữa các phương thức có thể tham khảo ở trang web này :http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
***Nếu sử dụng các phép nối chuỗi, ta có thể áp dụng bài toán cập nhật dữ liệu mà không cần dùng vòng lặp !
ví dụ như bài test Ado của 1 bạn đã hỏi trên GPE ( trong file đính kèm )!
Nếu dùng vòng lặp thì em viết như sau :
[GPECODE=vb]
Sub Update_Data_ADO()
Dim lsSQL As String, cnn As Object, lrs As Object
Dim Tmparr(), Item, iR As Long, Val1, val2
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\DATA.xls" & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
Tmparr = Range("A2", [A65536].End(3)).Value
iR = 1
For Each Item In Tmparr
iR = iR + 1
If Len(Item) Then
Val1 = CDbl(Range("C" & iR & "")): val2 = CStr(Trim(Range("D" & iR & "")))
Debug.Print Val1; val2
lsSQL = "UPDATE [DATA$] " & _
"SET [Thanhtoan]= " & Val1 & ", [Phuong Thuc] = '" & val2 & "'" & _
"WHERE [Bill] Like '" & Item & "'"
lrs.Open lsSQL, cnn, 3, 1
End If
Next
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
Còn nếu dùng Inner Joint thì em viết như sau :
[GPECODE=vb]
Sub Update_HLMT()
On Error GoTo Handle
Set Cn = CreateObject("ADODB.Connection")
Dim mySQL As String, strFile As Variant
strFile = Application.GetOpenFilename()
If strFile <> False Then
With Cn
mySQL = "UPDATE [Data$A2:F11] a " _
& "INNER JOIN " _
& "[Excel 8.0;HDR=No;IMEX=2;DATABASE=" _
& ThisWorkbook.FullName & "].[FORM$A2:E3] b " _
& "ON a.F1=b.F1 " _
& "SET a.F4=b.F3,a.F5=b.F4"
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
.Execute mySQL
.Close
End With
End If
Set Cn = Nothing
Exit Sub
Handle:
MsgBox Err.Description
End Sub
[/GPECODE]

 

[VetMini]

Bạn hỏi trên thực hành hay lý thuyết?

Theo lý thuyết, khi nối hai bảng với nhau, người ta dùng quy ước T1 là bên trái và T2 là bên phải.

Phép INNER JOIN là phép join có đối xứng, không phân biệt trái phải. (T1 INNER JOIN T2 = T2 INNER JOIN T1). Các dòng kết quả bắt buộc phải có cả bên T1 lẫn T2.

Phép LEFT JOIN là phép join không đối xứng, nó chú trọng lấy đủ hết dòng của T1, và bỏ trống phía phải nếu bên T2 không có dòng tương ứng.

Phép RIGHT JOIN ngược với phép left join, nó chú trọng lấy đủ hết dòng của T2, và bỏ trống phía trái nếu bên T1 không có dòng tương ứng

Phép FULL JOIN lấy tất cả T1 và T2, và bỏ trống bên trái hoặc bên phải nếu cần. Phép join này cũng đối xứng, tức là T1 FULL JOIN T2 = T2 FULL JOIN T1

 

[Hai Lúa Miền Tây]

Vấn đề sử dụng các phép nối bảng trên SQL , em cũng từng ngâm cứu qua( nhưng mà vẫn lơ mơ lù mù ),cụ thể sự khác nhau giữa các phương thức có thể tham khảo ở trang web này :http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
***Nếu sử dụng các phép nối chuỗi, ta có thể áp dụng bài toán cập nhật dữ liệu mà không cần dùng vòng lặp !
ví dụ như bài test Ado của 1 bạn đã hỏi trên GPE ( trong file đính kèm )!
Nếu dùng vòng lặp thì em viết như sau :
[GPECODE=vb]
Sub Update_Data_ADO()
Dim lsSQL As String, cnn As Object, lrs As Object
Dim Tmparr(), Item, iR As Long, Val1, val2
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\DATA.xls" & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
Tmparr = Range("A2", [A65536].End(3)).Value
iR = 1
For Each Item In Tmparr
iR = iR + 1
If Len(Item) Then
Val1 = CDbl(Range("C" & iR & "")): val2 = CStr(Trim(Range("D" & iR & "")))
Debug.Print Val1; val2
lsSQL = "UPDATE [DATA$] " & _
"SET [Thanhtoan]= " & Val1 & ", [Phuong Thuc] = '" & val2 & "'" & _
"WHERE [Bill] Like '" & Item & "'"
lrs.Open lsSQL, cnn, 3, 1
End If
Next
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
Còn nếu dùng Inner Joint thì em viết như sau :
[GPECODE=vb]
Sub Update_HLMT()
On Error GoTo Handle
Set Cn = CreateObject("ADODB.Connection")
Dim mySQL As String, strFile As Variant
strFile = Application.GetOpenFilename()
If strFile <> False Then
With Cn
mySQL = "UPDATE [Data$A2:F11] a " _
& "INNER JOIN " _
& "[Excel 8.0;HDR=No;IMEX=2;DATABASE=" _
& ThisWorkbook.FullName & "].[FORM$A2:E3] b " _
& "ON a.F1=b.F1 " _
& "SET a.F4=b.F3,a.F5=b.F4"
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
.Execute mySQL
.Close
End With
End If
Set Cn = Nothing
Exit Sub
Handle:
MsgBox Err.Description
End Sub
[/GPECODE]

Cám ơn bạn trả lời, mình chỉ muốn biết ngắn gọn là 3 trường hợp trên có gì khác.

 

[hungpecc1]

Cám ơn bạn trả lời, mình chỉ muốn biết ngắn gọn là 3 trường hợp trên có gì khác.

Dạ , anh cho em hỏi nếu bây giờ em muốn inner Join lớn hơn 2 bảng , thì phải dùng câu lệnh nào tổng quát nhất ? Phép hợp Inner Jonin có hạn chế số lượng table không anh ?

 

[Hai Lúa Miền Tây]

Dạ , anh cho em hỏi nếu bây giờ em muốn inner Join lớn hơn 2 bảng , thì phải dùng câu lệnh nào tổng quát nhất ? Phép hợp Inner Jonin có hạn chế số lượng table không anh ?

Cũng còn tùy thuộc vào csdl của bạn nữa.

 

[Hai Lúa Miền Tây]

[TIP]Với dữ liệu bài #166, bạn hãy liệt kê những mặt hàng không xuất hiện trong sheet Data2[/TIP].

 

[hungpecc1]

[TIP]Với dữ liệu bài #166, bạn hãy liệt kê những mặt hàng không xuất hiện trong sheet Data2[/TIP].

Đầu tiên em xin chân thành cảm ơn anh Hai Lúa Miền Tây đã xây dựng topic này, nhờ topic này mà em đã biết thêm về ADO và em đang cố gắng học tập để áp dụng ADO vào công việc của mình !
Em xin post cách làm của em với bài tập này như sau :
[GPECODE=vb]
Sub BT_ADO()
Dim adoConn As Object, adoRS As Object
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 T1.MS,T1.soluong " _
& "from [Data1$] T1 LEFT OUTER JOIN " _
& " [Data2$] T2 " _
& "on T1.ms = T2.ms" _
& " WHERE T2.ms is null"
End With
With Sheet3
.[B8:E120].ClearContents
.[B8].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End Sub
[/GPECODE]

 

[Hai Lúa Miền Tây]

Không làm khó được bạn hungpecc1: Câu trả lời của bạn hoàn toàn chính xác.
Tiếp theo nhé:

[TIP]Bạn hãy liệt kê (Ms, Ten, Mau) ở sheet Data2 mà không xuất hiện ở sheet Data1[/TIP]

 

[hungpecc1]

[TIP]Bạn hãy liệt kê (Ms, Ten, Mau) ở sheet Data2 mà không xuất hiện ở sheet Data1[/TIP][/QUOTE]

Chết rồi , em có vẻ nghiền ADO rồi ( càng vọc càng thấy hay), kiểu này lại thích ADO hơn Advanced Filter mất,^^
bài dự thi của em như sau :
[GPECODE=vb]
Sub BT_ADO()
Dim adoConn As Object, adoRS As Object
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 T2.MS,T2.ten,T2.mau " _
& "from [Data1$] T1 RIGHT OUTER JOIN " _
& " [Data2$] T2 " _
& "on T1.ms = T2.ms" _
& " WHERE T1.ms is null"
End With
With Sheet3
.[B8:E120].ClearContents
.[B8].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End Sub
[/GPECODE]

 

[Hai Lúa Miền Tây]

Hihi, vậy là phần join xem như kết thúc, xin cảm ơn mọi người, cảm ơn bạn hungpecc1 đã tích cực hưởng ứng phần join này.

 

[viehoai]

Hihi, vậy là phần join xem như kết thúc, xin cảm ơn mọi người, cảm ơn bạn hungpecc1 đã tích cực hưởng ứng phần join này.

Anh Hai Lúa Miền Tây à, vì số đông chưa biết cả lý thuyết nên xin anh trình qua cơ bản lý thuyết, hoặc anh gợi ý một tí, như bài vừa rồi thì em như botay.com, không biến lần theo đường này. Như những bài trước đây vậy
Em hỏi thêm: Dạng bài toán này so sánh với cách dùng Array thì tốc độ hơn không?
Cảm ơn Anh rất nhiều

 

[Hai Lúa Miền Tây]

Anh Hai Lúa Miền Tây à, vì số đông chưa biết cả lý thuyết nên xin anh trình qua cơ bản lý thuyết, hoặc anh gợi ý một tí, như bài vừa rồi thì em như botay.com, không biến lần theo đường này. Như những bài trước đây vậy
Cảm ơn Anh rất nhiều

Đúng là cách trình bày đợt này có khác với đợt trước, em muốn đưa ra kết quả để tự mọi người tự tìm hiểu, sau đó có bài tập sẽ giúp cho mọi người nhớ dai hơn. Cảm ơn anh đã góp ý.
Em ít dùng Arr nhưng theo như so sánh thì Arr sẽ có có tốc độ xử lý cao hơn ado, mỗi cái đều có thế mạnh riêng anh à. Nếu kết hợp giữa ado và arr sẽ cho tốc độ rất tuyệt.

 

[VetMini]

[TIP]Với dữ liệu bài #166, bạn hãy liệt kê những mặt hàng không xuất hiện trong sheet Data2[/TIP].

Trong phạm vi của câu hỏi về cách sử dụng LEFT JOIN thì câu trả lời của hungpecc1 là đúng.

Trong phạm vi mở rộng của ngôn ngữ SQL thì đề này nếu giải bằng lệnh WHERE NOT EXISTS thì hợp lô gic hơn - và dễ lồng trong câu truy vấn phụ hơn.

Select trường1, trường2, vv
From Bảng1
Where Not Exists (Select * From Bảng2 Where Khoá = Bảng1.Khoá)

Về vấn đề tốc độ truy vấn thì vì ở đây bảng là worksheet nên hiệu quả cũng gần như nhau, không đáng kể. Nếu bảng nằm trên CSDL nghiêm túc (như SQL Server,...) thì hiệu quả có thể khác.

 

[Hai Lúa Miền Tây]

Trong phạm vi của câu hỏi về cách sử dụng LEFT JOIN thì câu trả lời của hungpecc1 là đúng.

Trong phạm vi mở rộng của ngôn ngữ SQL thì đề này nếu giải bằng lệnh WHERE NOT EXISTS thì hợp lô gic hơn - và dễ lồng trong câu truy vấn phụ hơn.

Select trường1, trường2, vv
From Bảng1
Where Not Exists (Select * From Bảng2 Where Khoá = Bảng1.Khoá)

Về vấn đề tốc độ truy vấn thì vì ở đây bảng là worksheet nên hiệu quả cũng gần như nhau, không đáng kể. Nếu bảng nằm trên CSDL nghiêm túc (như SQL Server,...) thì hiệu quả có thể khác.

Ở đây mình đang muốn nói đến join, nếu dùng như bạn thì nó thuộc về vấn đề khác.

 

[leonguyenz]

Em cũng hay xem Topic này, nhưng chỉ xem để dùng thôi, chưa học được gì cả, topic cực kỳ bổ ích, có lần hứa với anh HL là sẽ học nhưng em bận quá nên chưa học được. Thật sự xin lỗi anh!
Cảm ơn anh đã mở Topic này!

 

[vodoi2x]

Nên nhìn đại cục hơn

[TIP]Bạn hãy liệt kê (Ms, Ten, Mau) ở sheet Data2 mà không xuất hiện ở sheet Data1[/TIP]

Chết rồi , em có vẻ nghiền ADO rồi ( càng vọc càng thấy hay), kiểu này lại thích ADO hơn Advanced Filter mất,^^

so sánh thế khập khiễng
Advanced Filter chỉ là 1 chức năng nhỏ của excel thôi
trong khi đó SQL là hẳn 1 ngôn ngữ xử lý dữ liệu

Thực ra áp dụng ADO vào cho excel kiểu như trong bóng đá dùng tiền vệ cánh phải mà thuận chân trái (giỏi) để đi vào và ghi bàn đó (như kiểu Robben - Bayer).

Ví von chút cho vui, nhưng qua đó thấy khai thác ADO cho excel thì cũng chỉ là 1 góc mạnh trong nhiều giải pháp khác nữa

Tuy thế, các bài tập ở đây đã có ích nào đó cho người đã biết sơ sơ về áp dụng SQL cho excel - nó sẽ có ích hơn nhiều nếu chủ topic trình bày bài bản theo góc độ hướng cho người mới học.

 

[hungpecc1]

so sánh thế khập khiễng
Advanced Filter chỉ là 1 chức năng nhỏ của excel thôi
trong khi đó SQL là hẳn 1 ngôn ngữ xử lý dữ liệu

Thực ra áp dụng ADO vào cho excel kiểu như trong bóng đá dùng tiền vệ cánh phải mà thuận chân trái (giỏi) để đi vào và ghi bàn đó (như kiểu Robben - Bayer).

Ví von chút cho vui, nhưng qua đó thấy khai thác ADO cho excel thì cũng chỉ là 1 góc mạnh trong nhiều giải pháp khác nữa

Tuy thế, các bài tập ở đây đã có ích nào đó cho người đã biết sơ sơ về áp dụng SQL cho excel - nó sẽ có ích hơn nhiều nếu chủ topic trình bày bài bản theo góc độ hướng cho người mới học.

Ặc ,em nói như vầy là dựa trên quan điểm chủ quan của em thôi ^^, là em đang so sánh về cách dùng ADO và Advanced Filter trong việc trích lọc dữ liệu : việc dùng công cụ hay phương pháp nào là tùy quan điểm của mỗi cá nhân thôi mà !
cụ thể :
ADO: code dài nhưng dùng tiện hơn với File đóng
Advanced : thì code ngắn hơn, nhưng chỉ làm việc được trên worksheet
Tốc đô: thì 2 cách em thấy đều có tốc độ rất ổn < = giá trị thỏa mãn

 

[Hai Lúa Miền Tây]

so sánh thế khập khiễng
Advanced Filter chỉ là 1 chức năng nhỏ của excel thôi
trong khi đó SQL là hẳn 1 ngôn ngữ xử lý dữ liệu

Thực ra áp dụng ADO vào cho excel kiểu như trong bóng đá dùng tiền vệ cánh phải mà thuận chân trái (giỏi) để đi vào và ghi bàn đó (như kiểu Robben - Bayer).

Ví von chút cho vui, nhưng qua đó thấy khai thác ADO cho excel thì cũng chỉ là 1 góc mạnh trong nhiều giải pháp khác nữa

Tuy thế, các bài tập ở đây đã có ích nào đó cho người đã biết sơ sơ về áp dụng SQL cho excel - nó sẽ có ích hơn nhiều nếu chủ topic trình bày bài bản theo góc độ hướng cho người mới học.

Rất ghi nhận lời góp ý từ bạn, vì mình cũng mới bắt đầu học, kiến thức còn chấp vá, biết được cái nào mình chia sẻ cái đó nên chưa thể hệ thống lại được. Mong bạn giúp 1 tay nhé.

 

[VetMini]

so sánh thế khập khiễng
Advanced Filter chỉ là 1 chức năng nhỏ của excel thôi
trong khi đó SQL là hẳn 1 ngôn ngữ xử lý dữ liệu

Thực ra áp dụng ADO vào cho excel kiểu như trong bóng đá dùng tiền vệ cánh phải mà thuận chân trái (giỏi) để đi vào và ghi bàn đó (như kiểu Robben - Bayer).

Ví von chút cho vui, nhưng qua đó thấy khai thác ADO cho excel thì cũng chỉ là 1 góc mạnh trong nhiều giải pháp khác nữa

Tuy thế, các bài tập ở đây đã có ích nào đó cho người đã biết sơ sơ về áp dụng SQL cho excel - nó sẽ có ích hơn nhiều nếu chủ topic trình bày bài bản theo góc độ hướng cho người mới học.

trong khi đó SQL là hẳn 1 ngôn ngữ xử lý dữ liệu...
Không hẳn đúng.
SQL chỉ được đặt ra để sử lý dữ liệu theo đường lối CSDL Liên Hệ mà thôi.
Để SQL hoạt động hữu hiệu CSDL Liên Hệ phải được đưa về dạng chuẩn. Tối thiểu là bậc 2, tuy lý tưởng hơn thì nên là bậc 3 (xin xem lý thuyết CSDL Liên Hệ)
Vì các bảng tính của Excel thường được trình bày theo dạng dòng và cột cho nên nếu đã thiết kế đúng đắn thì những bảng tính này có thể tạm coi như CSDL đã đạt chuẩn bậc 2 hay 3. Và người ta có thể lợi dụng ADO để gọi cổ máy SQL của Access ra để làm việc.
Những cơ cấu về join trong tiêu đề này là căn bản của SQL qua áp dụng toán đại số tổ hợp. Qua đó ta thấy rằng sử dụng chúng chỉ là một hình thức lợi dụng chức năng liên hệ dữ liệu và các con toán thống kê của ngôn ngữ SQL.

Nếu gặp loại dữ liệu sắp xếp theo chiều hướng khác thì đem SQL ra cũng như bắt chó kéo cày. (xin xem lý thuyết các loại CSDL khác, điển hình là No SQL, Big Data)

Thực ra áp dụng ADO vào...
Tôi hoàn toàn không thấy vậy. Chẳng có tiền đạo hậu vệ gì cả. Thực tế rất giản dị: nếu bảng tính không được sắp xếp thành bảng một cách hợp lý thì SQL gần như vô hiệu.

 

[VetMini]

...
cụ thể :
ADO: code dài nhưng dùng tiện hơn với File đóng
...

Code dài hay ngắn tuỳ thuộc vào cách viết và tuỳ thuộc vào yêu cầu. Với những con toán phù hợp với CSDL Liên Hệ thì thực ra code một đoạn SQL động có thể ngắn hơn.

Vì ngôn ngữ VBA theo nguyên tắc xuống hàng là qua một lệnh mới cho nên viết một câu SQL phải nối tới nối lui cực khổ và trông xấu ỉn. Gặp những ngôn ngữ không coi xuống hàng là dứt lệnh (chính SQL không xem xuống hàng là dứt lệnh) thì câu SQL có thể được viết rất đẹp dạng.

 

[dhn46]

Các anh chị cho DHN46 hỏi: làm cách nào để đếm số lượng các mã số của 4 Sheet rồi hiển thị kết quả tại Sheet1, không đảo thứ tự sắp xếp các mã trên sheet1.
Xin cảm ơn GPE!

 

[thanhtratt]

Thì tôi đã nói rồi mà: Khi dùng thật sự tôi đâu có dùng TRANSPOSE...
Đồng thời cũng đã nói ở trên: tôi không khoái CopyFromRecordset vì nó chỉ hoạt động trên Range ---> Tôi dùng code để Add Item vào ComboBox cơ mà
---------------------
Sẳn đây cho mình hỏi mình muốn lấy kết quả của ADO gán vào Cobobox luôn thì viết code như thế nào? Hiện tại thì mình dùng cách gán xuống Sheet đặt Name rồi mới đưa vào Combobox.
Xin cám ơn!

 

[Hai Lúa Miền Tây]

Thì tôi đã nói rồi mà: Khi dùng thật sự tôi đâu có dùng TRANSPOSE...
Đồng thời cũng đã nói ở trên: tôi không khoái CopyFromRecordset vì nó chỉ hoạt động trên Range ---> Tôi dùng code để Add Item vào ComboBox cơ mà
---------------------
Sẳn đây cho mình hỏi mình muốn lấy kết quả của ADO gán vào Cobobox luôn thì viết code như thế nào? Hiện tại thì mình dùng cách gán xuống Sheet đặt Name rồi mới đưa vào Combobox.
Xin cám ơn!

Bạn dùng code sau nhé:

[GPECODE=sql]Private Sub UserForm_Initialize()
Dim cnn As New ADODB.Connection, lrs As New ADODB.Recordset, i As Integer
cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
lrs.Open "select * From [Temp$] ", cnn
With Me.ComboBox1
.Clear
Do
.AddItem lrs![LastName]
lrs.MoveNext
Loop Until lrs.EOF
End With
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing

End Sub[/GPECODE]

 

[viehoai]

Bạn dùng code sau nhé:

[GPECODE=sql]Private Sub UserForm_Initialize()
Dim cnn As New ADODB.Connection, lrs As New ADODB.Recordset, i As Integer
cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
lrs.Open "select * From [Temp$] ", cnn
With Me.ComboBox1
.Clear
Do
.AddItem lrs![LastName]
lrs.MoveNext
Loop Until lrs.EOF
End With
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing

End Sub[/GPECODE]

Anh cho em hỏi tại sao em dùng

rstArr = lrs.GetRows
GetData = TransArr(rstArr)
Me.ComboBox1.List() = GetData

(TransArr là hàm chuyển cột <->hàng)
Nhưng không được nhỉ
A.. Em test được rồi.
[GPECODE=vb]Private Sub UserForm_Initialize()
Dim cnn As New ADODB.Connection, lrs As New ADODB.Recordset, i As Integer
Dim rstArr(), GetData()
cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
lrs.Open "select [LastName] From [Temp$] ", cnn
rstArr = lrs.GetRows
GetData = TransArr(rstArr)
Me.ComboBox1.List() = GetData
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]

 

[hungpecc1]

Anh cho em hỏi tại sao em dùng

rstArr = lrs.GetRows
GetData = TransArr(rstArr)
Me.ComboBox1.List() = GetData

(TransArr là hàm chuyển cột <->hàng)
Nhưng không được nhỉ
A.. Em test được rồi.
[GPECODE=vb]Private Sub UserForm_Initialize()
Dim cnn As New ADODB.Connection, lrs As New ADODB.Recordset, i As Integer
Dim rstArr(), GetData()
cnn.Open "Provider= Microsoft.Jet.OLEDB.4.0; data source=" & _
ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
lrs.Open "select [LastName] From [Temp$] ", cnn
rstArr = lrs.GetRows
GetData = TransArr(rstArr)
Me.ComboBox1.List() = GetData
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]

Em nghĩ anh nên bổ sung HDR = yes , IMEX = 1 thì hoàn chỉnh hơn anh ah !

 

[Hai Lúa Miền Tây]

Các anh chị cho DHN46 hỏi: làm cách nào để đếm số lượng các mã số của 4 Sheet rồi hiển thị kết quả tại Sheet1, không đảo thứ tự sắp xếp các mã trên sheet1.
Xin cảm ơn GPE!

Gộp, đếm các mã đó đưa vào vùng tạm, dùng vùng tạm này cập nhật dữ liệu theo yêu cầu, cuối cùng xóa vùng tạm đi.

[GPECODE=sql]Sub GopSheet_HLMT()
Dim cn As Object, rst As Object, cat As Object, tbl As Object, str$, str1 As String, i As Integer
Set cn = CreateObject("ADODB.Connection")
Set cat = CreateObject("ADOX.Catalog")
Set tbl = CreateObject("ADOX.Table")
Set rst = CreateObject("ADODB.Recordset")
With cn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
cat.ActiveConnection = cn
For Each tbl In cat.Tables
If Right(Replace(tbl.Name, "'", ""), 1) = "$" Then
str = str & " union all SELECT * from [" & _
Replace(Replace(tbl.Name, "$", ""), "'", "") & "$A1:A1000] where ma is not null"
str1 = Right(str, Len(str) - 10)
End If
Next
str1 = "select ma as Ma1, count(ma) as Dem from (" & str1 & ") group by ma"
With rst
.ActiveConnection = cn
.Open str1
End With
For Each fld In rst.Fields
i = i + 1
Cells(1, 254 + i) = fld.Name
Next
With Sheets("Sheet1")
.[IU2:IV5000].ClearContents
.[IU2].CopyFromRecordset rst
cn.Execute "UPDATE [Sheet1$A1:B6000] a " _
& "INNER JOIN " _
& "[Excel 8.0;HDR=Yes;IMEX=2;DATABASE=" & ThisWorkbook.FullName & "].[Sheet1$IU1:IV6000] b " _
& "ON a.ma=b.Ma1 " _
& "SET a.Dem=b.Dem"
.[IU1:IV5000].ClearContents
End With
rst.Close: Set rst = Nothing
cn.Close: Set cn = Nothing
Set cat = Nothing: Set tbl = Nothing

End Sub

[/GPECODE]

 

[dhn46]

Gộp, đếm các mã đó đưa vào vùng tạm, dùng vùng tạm này cập nhật dữ liệu theo yêu cầu, cuối cùng xóa vùng tạm đi.
........................

Cảm ơn anh HLMT. Em cũng đã thực hiện được việc tổng hợp các Sheet với việc sử dụng Union All, nhưng nếu không sử dụng vùng tạm, chỉ sử dụng 1 câu truy vấn duy nhất để giải quyết vấn đề này thì như thế nào? Em chưa tìm ra phương pháp để giải quyết vấn đề này. Cảm ơn anh!

 

[VetMini]

Theo lô gic, bạn có thể dùng 1 trong những cách sau:

1. Dùng lệnh UPDATE Sheet 1, LEFF JOIN với Sheet 2, LEFF JOIN với Sheet 3, LEFF JOIN với Sheet 4,...

2. Gói câu lệnh UNION các Sheet 2, 3, 4,... vào một subquery và chèn vào lênh UPDATE ... INNER JOIN trên

3. UPDATE Sheet 1, set cái field cần tổng thành 0. Sau đó dùng vòng lặp UPDATE Sheet 1 bằng cách tuần tự cộng thêm trị từ các Sheets 2, 3, 4...

Lô gic thì như vậy, nhưng khi tôi thử thì mới cách 1 đã bị sai tá hoả, tôi không có thì giờ thử tiếp.

 

[dhn46]

Dhn46 cảm ơn các anh chị quan tâm. Do muốn tìm hiểu về ADO nên mong các anh chị giúp đỡ
Trước khi đặt câu hỏi dhn46 đã thử nghiệm với Code sau

Sub Tonghop()
    Dim lsSQL As String, cnn As Object, lrs As Object, Str As String
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    With cnn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & _
                            ";Extended Properties=""Excel 8.0;HDR=No;"";"
        .Open
    End With
    
    For Each Wks In ThisWorkbook.Worksheets
        Str = Str & "UNION ALL SELECT F1 FROM [" & Wks.Name & "$A2:A65536] Where f1 Is Not Null "
    Next
    
    lsSQL = "SELECT * FROM [" & ActiveSheet.Name & "$A2:A65536]" & _
            "LEFT JOIN (select f1, COUNT(f1) FROM (" & Right(Str, Len(Str) - 9) & ") group by f1) as SubTable " & _
            "ON [" & ActiveSheet.Name & "$A2:A65536].f1 = SubTable.f1"
    lrs.Open lsSQL, cnn, 3, 1
    
    With Sheet1
        .[D2:D1000].ClearContents
        .[D2].CopyFromRecordset lrs
    End With
    
    lrs.Close: Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
End Sub

(Chưa xét tới trường hợp có Name hay tên Sheet dạng số để bắt lỗi)
Thì kết quả thu được gồm 3 cột: 2 cột đầu là mã số trong Sheet1, cột thứ 3 là kết quả đếm được. DHN46 chưa tìm được cách để "Select f3" gán vào cột Đếm.
Mong anh chị trợ giúp cho trường hợp trên.

To VetMini: cảm ơn anh đã quan tâm, qua các bài viết có lẽ anh rất giỏi trong việc tạo dựng CSDL. Nếu anh có nhiều thời gian hơn mong anh cùng chung tay xây dựng GPE, và "bật mí" nhiều hơn nữa cho những người không chuyên như dhn46. Chúc anh sức khỏe, thành công!

Cảm ơn GPE!

 

[VetMini]

...
Theo lô gic, bạn có thể dùng 1 trong những cách sau:

1. Dùng lệnh UPDATE Sheet 1, LEFF JOIN với Sheet 2, LEFF JOIN với Sheet 3, LEFF JOIN với Sheet 4,...

2. Gói câu lệnh UNION các Sheet 2, 3, 4,... vào một subquery và chèn vào lênh UPDATE ... INNER JOIN trên

3. UPDATE Sheet 1, set cái field cần tổng thành 0. Sau đó dùng vòng lặp UPDATE Sheet 1 bằng cách tuần tự cộng thêm trị từ các Sheets 2, 3, 4...

Lô gic thì như vậy, nhưng khi tôi thử thì mới cách 1 đã bị sai tá hoả, tôi không có thì giờ thử tiếp.

Hỏi lòng vòng thì được một người bạn trả lời như sau:

Excel ADO sử dụng cỗ máy SQL của Access. Theo luật Access, nếu JOIN nhiều hơn 2 bảng thì phải đóng trong dấu ngoặc

UPDATE (((Sheet 1), LEFF JOIN với Sheet 2), LEFF JOIN với Sheet 3), LEFF JOIN với Sheet 4

Access, khổ vì mày!!!!

 

[dhn46]

Hỏi lòng vòng thì được một người bạn trả lời như sau:

Excel ADO sử dụng cỗ máy SQL của Access. Theo luật Access, nếu JOIN nhiều hơn 2 bảng thì phải đóng trong dấu ngoặc

UPDATE (((Sheet 1), LEFF JOIN với Sheet 2), LEFF JOIN với Sheet 3), LEFF JOIN với Sheet 4

Access, khổ vì mày!!!!

Chào anh VetMini.
Cảm ơn anh đã gợi ý cách giải quyết vấn đề nhưng dhn46 vẫn chưa áp dụng được gợi ý của anh. Nếu có thể anh cho 1 lệnh truy vấn mẫu để dhn46 suy đoán và áp dụng.

 

[VetMini]

Nếu chỉ đếm xem ở sheet2, 3, 4 có bao nhiêu lần xuất hiện của mã số và cập nhật Sheet1 (viết tắt Sheet1 lá S1, Sheet2 là S2... cho nhanh):

Update S1 set Dem=tt2.Dem From S1 as tt1 Inner Join (
Select t1.MS, count(t2.MS)+count(t3.MS)+count(t4.MS) As Dem
From S1 As t1 Left Join S2 As t2 on t1.MS=t2.MS Left Join S3 As t3 on t1.MS=t3.MS Left Join S4 As t4 on t1.MS=t4.MS
Group By t1.MS ) As tt2 on tt2.MS = tt1.MS

Hàm count tự động loại bỏ null nên dùng vời Left Join không có vấn đề

Lưu ý là câu truy vấn trên viết cho SQL tiêu chuẩn. Access không thích lệnh join nhiều hơn 2 bảng nên phải dùng dấu ngoặc

From S1 As t1 Left Join S2 As t2 on t1.MS=t2.MS Left Join S3 As t3 on t1.MS=t3.MS Left Join S4 As t4 on t1.MS=t4.MS

phải viết là

From ((S1 As t1 Left Join S2 As t2 on t1.MS=t2.MS) Left Join S3 As t3 on t1.MS=t3.MS) Left Join S4 As t4 on t1.MS=t4.MS

 

[nhanvvGPE]

Copy range từ csv file lên excel, vẽ đồ thị sau đó lưu vào file excel khác!

Hi All,

Em là thành viên mới mong mọi người giúp đỡ, em có công việc như sau:
1/ Kết nối đến file csv (chứa dữ liệu được xuất ra tự động từ máy đo), copy range B19:B26.
2/ Paste dữ liệu lên range C4 sheet 1 của file xlsm "SourceFile", vẽ đồ thị ( phần vẽ đồ thị em làm được).
3/ Lưu dữ liệu vào file excel xlsm DesFile đang đóng.

Em đã làm được theo cách Open file, thao tác sau đó đóng file tuy nhiên tốc độ chậm.
Mong mọi người giúp đỡ em code để thực hiện thao tác trên!
Hiện tại em tham khảo được module copy data từ excel:
Public Sub GetDataADO(SourceFile As Variant, SourceSheet As String, SourceRange As String, desRange As Range)
Dim rsCon As Object: Dim rsData As Object: Dim szConnect As String: Dim szSQL As String
' Create the connection string
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
On Error GoTo SomethingWrong
Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
rsCon.Open szConnect: rsData.Open szSQL, rsCon, 0, 1, 1
' Check to make sure we received data and copy the data
If Not rsData.EOF Then
desRange.CopyFromRecordset rsData
Else: MsgBox "No records returned from : " & SourceFile, vbCritical
End If
' Clean up our Recordset object.
rsData.Close: Set rsData = Nothing
rsCon.Close: Set rsCon = Nothing
Exit Sub
SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, vbExclamation, "Error"
On Error GoTo 0
End Sub

Em đang cần một module tương tự cho việc lấy dữ liệu từ csv file và đẩy xuống xlsx file.

Thanks!
BestRegard!

 

[hungpecc1]

Hi All,

Em là thành viên mới mong mọi người giúp đỡ, em có công việc như sau:
1/ Kết nối đến file csv (chứa dữ liệu được xuất ra tự động từ máy đo), copy range B19:B26.
2/ Paste dữ liệu lên range C4 sheet 1 của file xlsm "SourceFile", vẽ đồ thị ( phần vẽ đồ thị em làm được).
3/ Lưu dữ liệu vào file excel xlsm DesFile đang đóng.

Em đã làm được theo cách Open file, thao tác sau đó đóng file tuy nhiên tốc độ chậm.
Mong mọi người giúp đỡ em code để thực hiện thao tác trên!

Em đang cần một module tương tự cho việc lấy dữ liệu từ csv file và đẩy xuống xlsx file.

Thanks!
BestRegard!

Để đọc dữ liệu từ file cvs , bạn có thể dùng nhiều cách , còn muốn dùng ADO thì tham khảo code sau :
[GPECODE=vb]
Sub GPE()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
With cnn
.CursorLocation = 3
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
End With

rst.Open "SELECT * FROM 001.csv", cnn, 3, 3, adCmdText
Range("A1100").ClearContents
Range("A1").CopyFromRecordset rst
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]