Đố vui về ADO, DAO.

Liên hệ QC
Hai Lúa Miền Tây đã viết:
Chính xác là em muốn nói cái này, dùng DAO để lấy, vì ADO nó sẽ tự động sắp xếp theo thứ tự nên không thể lấy chính xác.

Mã: 
Sub TestDAO()
    Dim Dbs  As Object, db As Object
    Set Dbs = CreateObject("DAO.DBEngine.36")
    Set db = Dbs.OpenDatabase"E:\VBA\LuongTH.xls", False, True, "Excel 8.0;")
    MsgBox db.TableDefs(0).Name
    db.Close
    Set Dbs = Nothing: Set db = Nothing
    
End Sub

Nhân tiện đây xin hỏi là làm sao ta lấy được tất cà tên sheet = DAO?
Nhấp chuột vào đây để mở rộng...

Ráp vào file bị báo lỗi tại dòng
Mã: 
Set db = Dbs.OpenDatabase(FileName, False, True, "Excel 8.0;")
Tình nghi có liên quan đến version office (tôi dùng office 2010) nên sửa số 8.0 thành 12.0 vẫn lỗi!
 
ndu96081631 đã viết:
Ráp vào file bị báo lỗi tại dòng
Mã: 
Set db = Dbs.OpenDatabase(FileName, False, True, "Excel 8.0;")
Tình nghi có liên quan đến version office (tôi dùng office 2010) nên sửa số 8.0 thành 12.0 vẫn lỗi!
Nhấp chuột vào đây để mở rộng...

Em dùng Office 2013 test nó cho kết quả bình thường.
 
Dựa vào trang này em đưa ra Code sau để lấy tên tất cả các sheet dựa theo DAO
Mã: 
Public Sub GetSheetName()
Dim db As Database
Dim tdf As TableDef
   Set db = OpenDatabase("D:\test.xls", False, False, "Excel 8.0;HDR=NO;IMEX=2;")
    For Each tdf In db.TableDefs
      MsgBox tdf.Name
   Next
   Set db = Nothing
End Sub
 
2 cách dùng ADO mà tôi biết (có thể có các cách khác tôi không biết)

1. Dùng ADO Exlensions của DDLS (ADOX) để đọc Catalog

Mã: 
Sub GetSheetNames1()

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim t As ADOX.Table

Set cn = New ADODB.Connection
cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
& "Initial Catalog=C:\Junks\Test Book.xls"
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
For Each t In cat.Tables
Debug.Print t.Name
Next t
Set cat = Nothing
cn.Close
Set cn = Nothing
End Sub

2. Dùng hàm OpenSchema và tham số adSchemaTables để load schema vào một string rồi parse string đó để lấy tên sheet

Mã: 
Sub GetSheetNames2()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = GetExcelConnection("C:\Junks\Test Book.xls")
Set rs = cn.OpenSchema(adSchemaTables)

Do While Not rs.EOF
    strTable = rs.Fields("table_name").Value
    If Right$(strTable, 1) = "$" Then ' sheet name
        strWorksheetList = strWorksheetList & vbCrLf & strTable
    Else ' range name
        strRangeList = strRangeList & vbCrLf & strTable
    End If
    rs.MoveNext
Loop
Debug.Print "Worksheets:" & strWorksheetList & vbCrLf & vbCrLf & "Ranges:" & strRangeList
End Sub

Private Function GetExcelConnection(ByVal Path As String, _
    Optional ByVal Headers As Boolean = True) As Connection
    Dim strConn As String
    Dim objConn As ADODB.Connection
    Set objConn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Path & ";" & _
              "Extended Properties=""Excel 8.0;HDR=" & _
              IIf(Headers, "Yes", "No") & """"
    objConn.Open strConn
    Set GetExcelConnection = objConn
End Function

Cả 2 cách đều lấy tên sheet theo thứ tự abc. Làm thế nào để biết sheet đầu tiên thì tôi lười quá chưa nghĩ ra.

Theo tôi biết, Catalog và Schema đọc tên sheet từ ISAM table cho nên bắt buộc phải theo thứ tự index của ISAM.
 
dhn46 đã viết:
Dựa vào trang này em đưa ra Code sau để lấy tên tất cả các sheet dựa theo DAO
Mã: 
Public Sub GetSheetName()
Dim db As Database
Dim tdf As TableDef
   Set db = OpenDatabase("D:\test.xls", False, False, "Excel 8.0;HDR=NO;IMEX=2;")
    For Each tdf In db.TableDefs
      MsgBox tdf.Name
   Next
   Set db = Nothing
End Sub
Nhấp chuột vào đây để mở rộng...

Còn phải xử lý bỏ qua name, vùng lọc, vùng in, tiêu đề cần lặp lại...
 
Tôi biết vấn đề nằm ở đâu rồi
Code lấy Sheet đầu tiên dùng DAO phải đặt trước dòng sCon.Open szConnect mới xong
 
VetMini đã viết:
2 cách dùng ADO mà tôi biết (có thể có các cách khác tôi không biết)

1. Dùng ADO Exlensions của DDLS (ADOX) để đọc Catalog

Mã: 
Sub GetSheetNames1()

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim t As ADOX.Table

Set cn = New ADODB.Connection
cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
& "Initial Catalog=C:\Junks\Test Book.xls"
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
For Each t In cat.Tables
Debug.Print t.Name
Next t
Set cat = Nothing
cn.Close
Set cn = Nothing
End Sub

2. Dùng hàm OpenSchema và tham số adSchemaTables để load schema vào một string rồi parse string đó để lấy tên sheet

Mã: 
Sub GetSheetNames2()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = GetExcelConnection("C:\Junks\Test Book.xls")
Set rs = cn.OpenSchema(adSchemaTables)

Do While Not rs.EOF
    strTable = rs.Fields("table_name").Value
    If Right$(strTable, 1) = "$" Then ' sheet name
        strWorksheetList = strWorksheetList & vbCrLf & strTable
    Else ' range name
        strRangeList = strRangeList & vbCrLf & strTable
    End If
    rs.MoveNext
Loop
Debug.Print "Worksheets:" & strWorksheetList & vbCrLf & vbCrLf & "Ranges:" & strRangeList
End Sub

Private Function GetExcelConnection(ByVal Path As String, _
    Optional ByVal Headers As Boolean = True) As Connection
    Dim strConn As String
    Dim objConn As ADODB.Connection
    Set objConn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Path & ";" & _
              "Extended Properties=""Excel 8.0;HDR=" & _
              IIf(Headers, "Yes", "No") & """"
    objConn.Open strConn
    Set GetExcelConnection = objConn
End Function

Cả 2 cách đều lấy tên sheet theo thứ tự abc. Làm thế nào để biết sheet đầu tiên thì tôi lười quá chưa nghĩ ra.

Theo tôi biết, Catalog và Schema đọc tên sheet từ ISAM table cho nên bắt buộc phải theo thứ tự index của ISAM.
Nhấp chuột vào đây để mở rộng...

ADO sẽ không lấy tên sheet như DAO, đó là vấn đề mình muốn nói ở đây.
 
Hai Lúa Miền Tây đã viết:
Còn phải xử lý bỏ qua name, vùng lọc, vùng in, tiêu đề cần lặp lại...
Nhấp chuột vào đây để mở rộng...
ADO phức tạp thậy đấy anh Hai lúa ah, e sửa lại 1 chút anh xem như thế nào nhé
Mã: 
Public Sub GetSheetName()
Dim db As DAO.Database
Dim tdf As TableDef
   Set db = OpenDatabase("D:\test.xls", False, True, "Excel 8.0;")
    For Each tdf In db.TableDefs
      If Right(tdf.Name, 1) = "$" Or Right(tdf.Name, 2) = "$'" Then
        MsgBox tdf.Name
      End If
   Next
   Set db = Nothing
End Sub
 
dhn46 đã viết:
ADO phức tạp thậy đấy anh Hai lúa ah, e sửa lại 1 chút anh xem như thế nào nhé
Mã: 
Public Sub GetSheetName()
Dim db As DAO.Database
Dim tdf As TableDef
   Set db = OpenDatabase("D:\test.xls", False, True, "Excel 8.0;")
    For Each tdf In db.TableDefs
      If Right(tdf.Name, 1) = "$" Or Right(tdf.Name, 2) = "$'" Then
        MsgBox tdf.Name
      End If
   Next
   Set db = Nothing
End Sub
Nhấp chuột vào đây để mở rộng...
Chính xác là vậy rồi, nhưng nên bỏ tham chiếu đến nó.
Mã: 
Sub LayTenSheet()
    Dim Dbs  As Object, db As Object, tbl As Object
    Set Dbs = CreateObject("DAO.DBEngine.36")
    Set db = Dbs.OpenDatabase("D:\test.xls", False, True, "Excel 8.0;")
    For Each tbl In db.TableDefs
        If Right(tbl.Name, 1) = "$" Or Right(tbl.Name, 2) = "$'" Then
            MsgBox tbl.Name
        End If
    Next tbl
    db.Close
    Set Dbs = Nothing: Set db = Nothing: Set tbl = Nothing
    
End Sub
 
dhn46 đã viết:
ADO phức tạp thậy đấy anh Hai lúa ah, e sửa lại 1 chút anh xem như thế nào nhé
Mã: 
Public Sub GetSheetName()
Dim db As DAO.Database
Dim tdf As TableDef
   Set db = OpenDatabase("D:\test.xls", False, True, "Excel 8.0;")
    For Each tdf In db.TableDefs
      If Right(tdf.Name, 1) = "$" Or Right(tdf.Name, 2) = "$'" Then
        MsgBox tdf.Name
      End If
   Next
   Set db = Nothing
End Sub
Nhấp chuột vào đây để mở rộng...

Bài 57 có xử lý rồi đấy
Mã: 
If Right(SheetName, 1) = "'" Then SheetName = Mid(SheetName, 2, Len(SheetName) - 2)
  If Right(SheetName, 1) <> "$" Then SheetName = SheetName & "$"
------------------------
Bạn lưu ý đừng bao giờ khai báo biến theo kiểu db As DAO.Database, tdf As TableDef... vì chỉ máy bạn mới chạy đuọc còn máy người khác thì... thua (ai biết bạn đã check References nào chứ)
Khai báo kiểu vầy mới chắc ăn:
Mã: 
Dim Dbs  As Object, db As Object
Set Dbs = [COLOR=#ff0000]CreateObject("DAO.DBEngine.36")[/COLOR]
Set db = Dbs.OpenDatabase(FileName, False, False, "Excel 8.0;")
SheetName = db.TableDefs(0).Name
Bảo đảm máy nào cũng chạy được
 
Hai Lúa Miền Tây đã viết:
ADO sẽ không lấy tên sheet như DAO, đó là vấn đề mình muốn nói ở đây.
Nhấp chuột vào đây để mở rộng...

Đồng ý.
Theo lý thuyết CSDL Liên Hệ thì thứ tự của dòng không quan trọng. Khi cần theo thứ tự nào thì người ta ORDER BY the thứ tự nấy. Nếu không có ORDER thì hầu hết các CSDL LH mặc định thứ tự theo khoá chính.
ADO là đối tượng bậc cao (high level) nên nó tuỳ thuộc vào engine vòng trong (low level)
DAO là đối thượng bậc thấp (low level) nên nó theo sát với dữ liệu thô hơn.

Vì vậy, có lẽ DAO là cách duy nhất để biệt thực sự sheet nào được đặt ở đầu.
 
Bạn lưu ý đừng bao giờ khai báo biến theo kiểu db As DAO.Database, tdf As TableDef... vì chỉ máy bạn mới chạy đuọc còn máy người khác thì... thua (ai biết bạn đã check References nào chứ)
Khai báo kiểu vầy mới chắc ăn:
Code:

Mã: 
Dim Dbs  As Object, db As Object 
Set Dbs = [COLOR=#ff0000]CreateObject("DAO.DBEngine.36")[/COLOR]
 Set db = Dbs.OpenDatabase(FileName, False, False, "Excel 8.0;")
 SheetName = db.TableDefs(0).Name
Bảo đảm máy nào cũng chạy được
Nhấp chuột vào đây để mở rộng...

Mình thấy rằng việc khai báo đến thư viện của DAO là cần thiết, nhất là đối với những người đang học tập hay tìm tòi như chúng ta. Chính Topic này cũng theo mục đích đó. Như vậy, nó sẽ hỗ trợ chúng ta trong viết Code chính xác, tham vấn trực tiếp được đến các thuộc tính, phương pháp hay cú pháp chuẩn của nó.
Để khắc phục tình trạng sang máy khác không hiểu ta thêm 1 đoạn Code tự xác định tham chiếu đến thư viện nếu nhỡ trong file nào đó không tham chiếu như sau:

Mã: 
Public Sub AutRef()
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile _
"C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
End Sub

Còn đương nhiên cách viết của Ndu là gọn nhưng nó phù hợp cho ai nắm chắc cú pháp viết Code hoặc chép Code , còn người đang học mất hẳn tính năng "nha nhá' gợi ý.
Hoặc ta cứ tham chiếu để viết Code, bao giờ xong ta chuyển sang dạng CreateObject như Ndu.
 
Lần chỉnh sửa cuối:
sealand đã viết:
Mình thấy rằng việc khai báo đến thư viện của DAO là cần thiết, nhất là đối với những người đang học tập hay tìm tòi như chúng ta. Chính Topic này cũng theo mục đích đó. Như vậy, nó sẽ hỗ trợ chúng ta trong viết Code chính xác, tham vấn trực tiếp được đến các thuộc tính, phương pháp hay cú pháp chuẩn của nó.
Để khắc phục tình trạng sang máy khác không hiểu ta thêm 1 đoạn Code tự xác định tham chiếu đến thư viện nếu nhỡ trong file nào đó không tham chiếu như sau:

Mã: 
Public Sub AutRef()
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile _
"C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
End Sub

Còn đương nhiên cách viết của Ndu là gọn nhưng nó phù hợp cho ai nắm chắc cú pháp viết Code hoặc chép Code , còn người đang học mất hẳn tính năng "nha nhá' gợi ý.
Hoặc ta cứ tham chiếu để viết Code, bao giờ xong ta chuyển sang dạng CreateObject như Ndu.
Nhấp chuột vào đây để mở rộng...

Đề mục này thuộc về dữ liệu, ADO và DAO đều là công cụ kết nối dữ liệu (data linking), và nếu mở rộng hơn thì sẽ bao gồm chuyển đổi dữ liệu (data migration).

Trên tinh thần đó, chuyện sử dụng dữ liệu trong dataset/recordset mới là chính. Chuyện đoạn code thực hiện lệnh kết nối là chuyện phụ, nó chỉ dùng để test code, cú pháp hầu như không quan trọng. Người sử dụng chỉ cần biết điều kiện kết nối để có thể thực hiện truy vấn hiệu quả.

Thực tế trong môi trường làm việc, người ta đặt những đoạn code hạ tầng cơ sở này vào một wrapper class/module (xem chú thích 1). Gặp môi trường khác nhau thì chỉ việc sửa đổi code trong các class/module này. Code làm việc với dữ liệu đưa tham số nguồn (vd tên file) và gọi hàm kết nối với nguồn, đưa tham số truy vấn (ví dụ câu lệnh SQL) và gọi hàm đọc dữ liệu.

Chính vì có điểm lợi tách rời code sử dụng dũ liệu riêng với code hạ tầng cơ sở này mà ADO mới ra đời. Trên nguyên tắc, DAO (xem chú thích 2) làm việc với Access và Jet engines hữu hiệu hơn. Mặt khác, ADO có khả năng kết nối rộng hơn với các loại nguồn khác.

Chú thích:
(1) theo ngôn ngữ tổng quát thì các code modules đều có thể gọi là class - module chúng ta hay dùng là class tĩnh, sử dụng các hàm và phương thức không cần phải qua đối tuợng.
(2) hình như khi nói đến DAO, chúng ta dùng phiên bản 3.6; code của các bạn có nêu như vậy.
 
Ví dụ tôi có 2 file có đường dẫn là chung 1 folder, file A.xls là file chứa dữ liệu, file còn lại là B.xls.
Xin hỏi: Làm thế nào không mở file B.xls mà ta tạo ra 14 sheet và chuyển dữ liệu từ sheet Data của file A.xls với điều kiện là số liệu của sheet 1 đến 14 được lọc tương ứng ở cột STT vào file B.xls (Tức là sheet 1 thì chỉ chứa STT là 1, sheet 2 thì chỉ chứa STT là 2... sheet 14 thì chỉ chứa STT là 14)
 

File đính kèm

  • Copy.rar
    17.1 KB · Đọc: 65
Lần chỉnh sửa cuối:
Hai Lúa Miền Tây đã viết:
Ví dụ tôi có 2 file có đường dẫn là chung 1 folder, file A.xls là file chứa dữ liệu, file còn lại là B.xls.
Xin hỏi: Làm thế nào không mở file B.xls mà ta tạo ra 14 sheet và chuyển dữ liệu từ sheet Data của file A.xls với điều kiện là số liệu của sheet 1 đến 14 được lọc tương ứng ở cột STT vào file B.xls (Tức là sheet 1 thì chỉ chứa STT là 1, sheet 2 thì chỉ chứa STT là 2... sheet 14 thì chỉ chứa STT là 14)
Nhấp chuột vào đây để mở rộng...
Em cũng muốn tìm hiểu ADO nên mạnh dạn đưa lời giải đầu tiên, có gì sai sót anh góp ý nhé.
Mã: 
Option Explicit
Sub CopyDL()
Dim i As Long
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 & "\A.XLS" & _
                        ";Extended Properties=""Excel 8.0;HDR=YES;"";"
    .Open
End With
    
For i = 1 To 14
    lsSQL = "select [Data$A1:D30].* INTO Data" & i & " IN '" & ThisWorkbook.Path & _
                 "\B.xls ' 'Excel 8.0;'  FROM [Data$A1:D30] where Stt=" & i
                 
    lrs.Open lsSQL, cnn, 3, 1
Next
    
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
 
dhn46 đã viết:
Em cũng muốn tìm hiểu ADO nên mạnh dạn đưa lời giải đầu tiên, có gì sai sót anh góp ý nhé.
Mã: 
Option Explicit
Sub CopyDL()
Dim i As Long
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 & "\A.XLS" & _
                        ";Extended Properties=""Excel 8.0;HDR=YES;"";"
    .Open
End With
    
For i = 1 To 14
    lsSQL = "select [Data$A1:D30].* INTO Data" & i & " IN '" & ThisWorkbook.Path & _
                 "\B.xls ' 'Excel 8.0;'  FROM [Data$A1:D30] where Stt=" & i
                 
    lrs.Open lsSQL, cnn, 3, 1
Next
    
Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
Nhấp chuột vào đây để mở rộng...

Đúng là như vậy, không làm khó được bạn. Thật ra nó nằm ở #9
 
Hai Lúa Miền Tây đã viết:
Đúng là như vậy, không làm khó được bạn. Thật ra nó nằm ở #9
Nhấp chuột vào đây để mở rộng...

Nếu bây giờ muốn xác định được giá trị 14 ( trong câu lệnh for next )thì phải dùng câu lệnh SQL như thế nào anh nhỉ ?
Không lẽ lại viết:
PHP: 
Lrs.Open "SELECT DISTINCT STT FROM [DATA$],cnn,3,1
While i < = Lrs.RecordCount
.......................
Wend
Trong SQL có hàm Count mà em không biết đưa câu lệnh : "SELECT DISTINCT COUNT(STT) FROM [DATA$] vào VBA như thế nào để lấy giá trị 14?<------------+-+-+-+
 
hungpecc1 đã viết:
Nếu bây giờ muốn xác định được giá trị 14 ( trong câu lệnh for next )thì phải dùng câu lệnh SQL như thế nào anh nhỉ ?
Không lẽ lại viết:
PHP: 
Lrs.Open "SELECT DISTINCT STT FROM [DATA$],cnn,3,1
While i < = Lrs.RecordCount
.......................
Wend
Trong SQL có hàm Count mà em không biết đưa câu lệnh : "SELECT DISTINCT COUNT(STT) FROM [DATA$] vào VBA như thế nào để lấy giá trị 14?<------------+-+-+-+
Nhấp chuột vào đây để mở rộng...

Trong trường hợp muốn lấy giá trị cao nhất của cột STT thì bạn có thể dùng
"select top 1 stt From [Data$] order by stt desc"
Nhấp chuột vào đây để mở rộng...
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

Bich2B
lỗi định dạng ngày không đọc được công thức excel
Trả lời
6
Đọc
83
Bich2B
Bich2B
P
Finest Сasual Dating - Verified Damsels
Trả lời
0
Đọc
15
phuoctho2009
P
A
Đếm dữ liệu với điều kiện
Trả lời
12
Đọc
133
adambaylin
A
S
Nhờ giúp đỡ code in toàn bộ danh sách có điều kiện
Trả lời
2
Đọc
77
S.Ha
S
A
Excel thiếu dữ liệu
Trả lời
4
Đọc
123
Mr.hieudoanxd
Mr.hieudoanxd
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom