Thảo luận về bài: ADO căn bản "Kết nối truy vấn CSDL từ file Excel đến file Access."

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

ADO trong Excel không hố trợ việc xoá dữ liệu.

 

[ptm0412]

Xin cho hỏi, dùng ADO để xóa dữ liệu (clearcontents) của:

1) Một hoặc vài cột trong Excel

2) Một bảng hoặc nhiều bảng trong 1 sheet của Excel

3) Một sheet hoặc nhiều sheet trong Excel

Thì phải làm như thế nào?

Cám ơn rất nhiều!

Ưu điểm, sức mạnh và mục đích của ADO là truy xuất, lọc, truy vấn, thêm record, ... từ 1 hoặc nhiều bảng, từ nguồn nội tại hoặc nguồn khác.

Xóa cột, xóa dòng, xóa bảng, xóa sheet, trong chính Excel .... lại là sức mạnh của Excel (common).

 

[Hoàng Trọng Nghĩa]

ADO trong Excel không hố trợ việc xoá dữ liệu.

Hèn chi, dùng thủ tục Delete * From ... bị báo lỗi liên tục! Thì ra nó không hỗ trợ! hic hic.

 

[Hoàng Trọng Nghĩa]

Copy dữ liệu từ nhiều file Excel

Tôi thấy nhiều khi kết nối với nhiều file Excel để lấy dữ liệu (từ các bảng có cùng cấu trúc hoặc tên cột). Vì thế làm một hàm kết nối tổng quát cho nhiều file khác nhau ở các đường dẫn khác nhau nhưng vẫn hoạt động một lần trong 1 thủ tục.

Hàm ExcelConnect

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

Thông qua hàm này nó vừa có tính chất kiểm tra file tồn tại, vừa tạo kết nối giữa các file, vì vậy ứng dụng cho nó như sau:

Giả sử tôi có file chính (KeyUp2_THU_NGHIEM_3) và 4 file khác cần kết nối, mỗi file chứa trong các thư mục khác nhau, tôi sẽ lần lượt lấy dữ liệu của 4 file tập trung về 1 bảng ở file chính, tôi làm thủ tục như sau:

Sub TongHopDuLieu()

      Dim FileName As String, SheetName As String, FieldName As String, _
          AppPath1 As String, AppPath2  As String, AppPath3  As String, _
          AppPath4 As String, sSQL As String, _
          PathArr() As Variant, FileArr() As Variant, SheetArr() As Variant, _
          c As Long, r As Long, _
          ObjRcs As Object
      
      FieldName = "[MAT HANG], [GIA GOC] "
    
      AppPath1 = ThisWorkbook.Path
      AppPath2 = ThisWorkbook.Path & "\Level1"
      AppPath3 = ThisWorkbook.Path & "\Level2"
      AppPath4 = ThisWorkbook.Path & "\Level3"
      
      PathArr = Array(AppPath1, AppPath2, AppPath3, AppPath4)
      
      FileArr = Array("BLM", "BLM2", "RONP", "ROUSS")

      SheetArr = Array("GOC", "GOCDDH", "ronpo,duphac,opv", "GOC RS")
      
      With ThisWorkbook.Sheets("DanhSachTongHop")
            
            .AutoFilterMode = False
            .UsedRange.EntireRow.Hidden = False
            .Cells.ClearContents
            
            For r = 0 To UBound(SheetArr)
                  
                  FileName = FileArr(r) & ".xls"
                  
                  SheetName = "[" & SheetArr(r) & "$] "
                  
                  If ExcelConnect(PathArr(r), FileName) = False Then
                        MsgBox "Không kêt nôi", vbOKOnly + vbExclamation, "Thông báo"
                        GoTo ExitSub
                  Else
                        sSQL = "SELECT " & FieldName & "FROM " & SheetName & "WHERE [MAT HANG] <> NULL"
                        
                        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
                              With .Range("A65536").End(xlUp)
                                    .Offset(IIf(.Row = 1, 0, 1)).CopyFromRecordset ObjRcs
                              End With
                        End If
                  End If
            Next
      
            r = .Range("A65536").End(xlUp).Row
            Set ComboList = .Range("A1:B" & r)
            ComboList.Sort .Range("A1"), xlAscending
      End With
      
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

Các bạn tải file về, giải nén và kiểm tra trên file KeyUp2_THU_NGHIEM_3 nhé!

Tôi rất mong nhận được sự thảo luận, đánh giá, góp ý của các bạn!

 

[Hoàng Trọng Nghĩa]

Trong thủ tục này:

    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & FullPath & ";" & _
    "Extended Properties=""Excel 8.0;[COLOR=#ff0000][B]HDR=Yes[/B][/COLOR];IMEX=1"";"

Tôi thấy có: HDR=Yes

Theo tôi nghĩ nó là HEADER, vậy nó được dùng vào mục đích gì? Nó có phải là tiêu đề cột hay không? Khi nào thì dùng? Và dùng HDR=No sẽ như thế nào?

 

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

Trong thủ tục này:

    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & FullPath & ";" & _
    "Extended Properties=""Excel 8.0;[COLOR=#ff0000][B]HDR=Yes[/B][/COLOR];IMEX=1"";"

Tôi thấy có: HDR=Yes

Theo tôi nghĩ nó là HEADER, vậy nó được dùng vào mục đích gì? Nó có phải là tiêu đề cột hay không? Khi nào thì dùng? Và dùng HDR=No sẽ như thế nào?

HDR=Yes thì các câu lệnh truy vấn sau này anh phải gọi nó rõ ràng, còn No thì mặc định của nó tự nhận là F1 cho trường đầu tiên, F2 cho trường số 2...
Dùng No cho những dữ liệu có tên trường không rõ ràng, hoặc cho những vùng dữ liệu không có tiêu đề cột.

 

[ptm0412]

Tôi thấy có: HDR=Yes
Theo tôi nghĩ nó là HEADER, vậy nó được dùng vào mục đích gì? Nó có phải là tiêu đề cột hay không? Khi nào thì dùng? Và dùng HDR=No sẽ như thế nào?

Nó liên quan đến DataSource.
Mặc định khi connect đến 1 file Excel:
- Lấy nguyên sheet đầu tiên (UsedRange)
- Nếu khai báo sheet, nó lấy UsedRange của sheet
- Nếu khai báo range name hoặc List 2003 hoặc table 2007, nó lấy vừa đúng Range đó
- Mặc định dòng đầu tiên là Header

Đó là dữ liệu đúng chuẩn: Dòng 1 header, các dòng sau là data. nếu là sheet thì dòng header là dòng 1, dữ liệu bắt đầu dòng 2.
Tuy nhiên cũng có dữ liệu không đúng chuẩn, có khi chẳng có tiêu đề, hoặc tiêu đề trùng,... Thậm chí table name range của 2010 sẽ không có bao gồm tiêu đề.
Do đó, tùy theo tình hình dữ liệu thực mà ta chọn HDR = Yes hay No

 

[Hoàng Trọng Nghĩa]

HDR=Yes thì các câu lệnh truy vấn sau này anh phải gọi nó rõ ràng, còn No thì mặc định của nó tự nhận là F1 cho trường đầu tiên, F2 cho trường số 2...
Dùng No cho những dữ liệu có tên trường không rõ ràng, hoặc cho những vùng dữ liệu không có tiêu đề cột.

Có nghĩa rằng khi mình gọi từ một bảng dữ liệu, nếu chọn Yes thì nó lấy dòng đầu tiên là tiêu đề cột và không tính toán gì với tiêu đề này, còn khi là No thì nó bao gồm dòng đầu tiên và nó tự gán tiêu đề từ cột 1 là F1 cho đến cột n là Fn phải vậy không?

 

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

Có nghĩa rằng khi mình gọi từ một bảng dữ liệu, nếu chọn Yes thì nó lấy dòng đầu tiên là tiêu đề cột và không tính toán gì với tiêu đề này, còn khi là No thì nó bao gồm dòng đầu tiên và nó tự gán tiêu đề từ cột 1 là F1 cho đến cột n là Fn phải vậy không?

Chính xác là như thế, tuy nhiên với vùng dữ liệu anh chọn bao gồm tiêu đề mà anh chọn HDR là No, thì lưu ý là các trường sẽ nhận dữ liệu là chuổi, vì tiêu đề là dạng chuổi, cho nên thường thì nếu chọn HDR là No thì không nên chọn vùng dữ liệu bao gồm dòng có tiêu đề.

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Hèn chi, dùng thủ tục Delete * From ... bị báo lỗi liên tục! Thì ra nó không hỗ trợ! hic hic.

Ta có thể dùng phương thức khác để thay thế câu lệnh không được hổ trợ này, bạn thử xem sao nhé.

 

[Hoàng Trọng Nghĩa]

Ta có thể dùng phương thức khác để thay thế câu lệnh không được hổ trợ này, bạn thử xem sao nhé.

Nếu trên Excel thì tôi chỉ cần dùng VBA là xong. Có chăng tôi hỏi là tìm xem có cách xóa bằng ADO thôi mà. Mục đích hỏi này là tôi dùng một chương trình đã kích hoạt và tôi không muốn mở cái file cần lấy dữ liệu hoặc xóa dữ liệu lên rồi xóa mà thông qua ADO.
Và bây giờ tôi biết ADO không thể thực thi chức năng đó nên tôi chỉ sử dụng ADO nếu tôi cần truy xuất dữ liệu mà thôi. Ngoài việc đó ra thì tôi dùng code VBA để thực thi những gì mà ADO không thể làm.

Tôi cũng mới tập tành ADO thôi nên còn nhiều khó khăn trong con đường học vấn này, tôi có bài đầu tay làm tại đây, nếu có góp ý xin bạn hướng dẫn thêm cho tôi nên và không nên làm gì ở bài có đường link dưới này nhé. Cám ơn bạn rất nhiều!

http://www.giaiphapexcel.com/forum/...ile-Excel-đến-file-Access&p=452144#post452144

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Nếu trên Excel thì tôi chỉ cần dùng VBA là xong. Có chăng tôi hỏi là tìm xem có cách xóa bằng ADO thôi mà. Mục đích hỏi này là tôi dùng một chương trình đã kích hoạt và tôi không muốn mở cái file cần lấy dữ liệu hoặc xóa dữ liệu lên rồi xóa mà thông qua ADO.
Và bây giờ tôi biết ADO không thể thực thi chức năng đó nên tôi chỉ sử dụng ADO nếu tôi cần truy xuất dữ liệu mà thôi. Ngoài việc đó ra thì tôi dùng code VBA để thực thi những gì mà ADO không thể làm.

Tôi cũng mới tập tành ADO thôi nên còn nhiều khó khăn trong con đường học vấn này, tôi có bài đầu tay làm tại đây, nếu có góp ý xin bạn hướng dẫn thêm cho tôi nên và không nên làm gì ở bài có đường link dưới này nhé. Cám ơn bạn rất nhiều!

http://www.giaiphapexcel.com/forum/showthread.php?69630-Th%E1%BA%A3o-lu%E1%BA%ADn-v%E1%BB%81-b%C3%A0i-ADO-c%C4%83n-b%E1%BA%A3n-K%E1%BA%BFt-n%E1%BB%91i-truy-v%E1%BA%A5n-CSDL-t%E1%BB%AB-file-Excel-%C4%91%E1%BA%BFn-file-Access&p=452144#post452144

Dĩ nhiên là ý tôi muốn nói là dùng ADO để clearcontents bảng dữ liệu, ở đây thảo luận ADO mà, như trước kia bạn thách đố tôi vậy.

 

[Hoàng Trọng Nghĩa]

Dĩ nhiên là ý tôi muốn nói là dùng ADO để clearcontents bảng dữ liệu, ở đây thảo luận ADO mà, như trước kia bạn thách đố tôi vậy.

Tôi tưởng bạn hướng dẫn, giúp đỡ, hoàn thiện cái bài đó cho tôi thì tôi cám ơn, chứ trả lời kiểu này tôi chẳng biết phải cám ơn bạn thế nào đây nữa???!!!

Nếu không có vấn đề gì thì đừng đưa ra những luận điệu như cái nick của bạn nhé!

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Tôi tưởng bạn hướng dẫn, giúp đỡ, hoàn thiện cái bài đó cho tôi thì tôi cám ơn, chứ trả lời kiểu này tôi chẳng biết phải cám ơn bạn thế nào đây nữa???!!!

Nếu không có vấn đề gì thì đừng đưa ra những luận điệu như cái nick của bạn nhé!

Bạn nói luận điệu là sao? Bài bạn hỏi ADO có cách nào xóa bảng giống như clearcontents không? Tôi trả lời là có, là người lớn và biết suy nghĩ thì bạn đừng phát biểu với tôi như thế.

 

[Hoàng Trọng Nghĩa]

Bạn nói luận điệu là sao? Bài bạn hỏi ADO có cách nào xóa bảng giống như clearcontents không? Tôi trả lời là có, là người lớn và biết suy nghĩ thì bạn đừng phát biểu với tôi như thế.

Nếu bạn thật sự có những phương thức về xóa dữ liệu trong Excel bằng ADO thì bạn dạy cho tôi đi, chứ đừng hỏi những câu cắc cớ khiến một người mới biết về ADO như tôi không biết đường đâu trả lời.

Vấn đề "thách đố" nghe hơi quá, nhưng dễ để trả lời nếu là tôi thì tôi nói "Tôi không biết" hoặc "Theo kiến thức của tôi, tôi cho rằng nó không hỗ trợ hành động đó". Đơn giản vậy thôi.

Nhưng tôi sẽ thật sự ghi nhận và trân trọng cột mốc sự việc bạn hướng dẫn này trên con đường học ADO đó bạn.

 

[xuan.nguyen82]

Tôi tưởng bạn hướng dẫn, giúp đỡ, hoàn thiện cái bài đó cho tôi thì tôi cám ơn, chứ trả lời kiểu này tôi chẳng biết phải cám ơn bạn thế nào đây nữa???!!!

Nếu không có vấn đề gì thì đừng đưa ra những luận điệu như cái nick của bạn nhé!

sì pam. sì pam.
Nếu anh Chàng Ngốc mà xóa được thì câu của anh Nghĩa phải sửa sao đây?

 

[Hoàng Trọng Nghĩa]

sì pam. sì pam.
Nếu anh Chàng Ngốc mà xóa được thì câu của anh Nghĩa phải sửa sao đây?

Thì tôi là cái nick của anh chàng đó! Vậy thôi hen!

Nếu được thì đưa ra những ví dụ như tôi đã hỏi nhé:

Xin cho hỏi, dùng ADO để xóa dữ liệu (clearcontents) của:

1) Một hoặc vài cột trong Excel

2) Một bảng hoặc nhiều bảng trong 1 sheet của Excel

3) Một sheet hoặc nhiều sheet trong Excel

Thì phải làm như thế nào?

Cám ơn rất nhiều!

 

[Excel Công Cụ Tuyệt Vời Của Bạn]

Sửa tag

Nếu bạn thật sự có những phương thức về xóa dữ liệu trong Excel bằng ADO thì bạn dạy cho tôi đi, chứ đừng hỏi những câu cắc cớ khiến một người mới biết về ADO như tôi không biết đường đâu trả lời.

Vấn đề "thách đố" nghe hơi quá, nhưng dễ để trả lời nếu là tôi thì tôi nói "Tôi không biết" hoặc "Theo kiến thức của tôi, tôi cho rằng nó không hỗ trợ hành động đó". Đơn giản vậy thôi.

Nhưng tôi sẽ thật sự ghi nhận và trân trọng cột mốc sự việc bạn hướng dẫn này trên con đường học ADO đó bạn.

Tôi không dám dạy ai, bạn quá lời rồi, ngốc như tôi chỉ làm được như sau:
[GPECODE=vb]
Sub XoaBang()
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.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;"";"
.Open
End With
lsSQL = "UPDATE [ChiFi$] " & _
"set NoiDi='', DienGiai='',NTT=''"
lrs.Open lsSQL, cnn, 3, 1
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
Exit Sub
Handle:
MsgBox Err.Description


End Sub
[/GPECODE]



*/ Áp dụng cho bài trên bạn hãy xóa sheet Data thử nhé.

P/S: Xin được phép mượn file dữ liệu của chị HYen17

 

[ndu96081631]

P/S: Xin được phép mượn file dữ liệu của chị HYen17

Nhìn code thấy quen quen ---> Chắc ở "gần nhà" anh Hai Lúa đây?
Ẹc... Ec..

 

[Hoàng Trọng Nghĩa]

Tôi không dám dạy ai, bạn quá lời rồi, ngốc như tôi chỉ làm được như sau:

Sub XoaBang()
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.FullName & _
                        ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
    .Open
End With
    lsSQL = "UPDATE [ChiFi$] " & _
                 "set NoiDi='', DienGiai='',NTT=''"
    lrs.Open lsSQL, cnn, 3, 1
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
Exit Sub
Handle:
MsgBox Err.Description


End Sub

*/ Áp dụng cho bài trên bạn hãy xóa sheet Data thử nhé.

P/S: Xin được phép mượn file dữ liệu của chị HYen17

Mặc dù không như ý tôi muốn học hỏi:

Hèn chi, dùng thủ tục Delete * From ... bị báo lỗi liên tục! Thì ra nó không hỗ trợ! hic hic.

Tuy nhiên tôi rất trân trọng những gì bạn đã giới thiệu cho tôi cái cách gọi là "cái khó ló cái khôn". Cám ơn bạn rất nhiều!