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

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

Làm sao ta có thể dùng 1 điều kiện duy nhất mà có thể lấy ra dữ liệu với họ bắt đầu lần lượt 3 ký tự (N,L,T). Kết quả như hình bên dưới.

hungpecc1 · 7/5/13

Hai Lúa Miền Tây đã viết:
Làm sao ta có thể dùng 1 điều kiện duy nhất mà có thể lấy ra dữ liệu với họ bắt đầu lần lượt 3 ký tự (N,L,T). Kết quả như hình bên dưới.

View attachment 101583

Em viết như thế này :
[GPECODE=vb]
Sub Loc_HLMT()
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=No;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "select f1,f2 from [Sheet1$A2:B1000] " _
& "where f1 like '[NLT]%'"
End With
With Sheet1
.[H2:I65000].ClearContents
.[H2:I2].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End Sub[/GPECODE]
Em đoán bài này anh muốn nhấn mạnh đến toán tử Like trong SQL ( --> vì thông thường để tách họ ,tên ta hay phải dùng một hàm, hay thủ tục để tách tên như hàm sau :
[GPECODE=vb]
Function GetName(Hovaten As String) As String
GetName = Trim(Right(Replace(Hovaten, " ", Space(255)), 255))
End Function
[/GPECODE]

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

Đúng thế bạn, bạn cũng là cao thủ rồi.

hungpecc1 · 7/5/13

Hai Lúa Miền Tây đã viết:
Đúng thế bạn, bạn cũng là cao thủ rồi.

Hix anh khen làm em thấy "xấu hổ" quá .
Code trên cũng là code em tổng hợp từ các đoạn code " ăn trộm "của anh mà ^^

VetMini · 7/5/13

Có hai cách:
- Cách dùng từ khoá LIKE (như trên)
- Cách dùng mảng: WHERE LEFT(f1,1) IN ('N','L','T')
Nếu CSDL có đặt mục lục (index) trên trường f1 thì cách LIKE hiệu quả nhất. Nếu không thì hai cách coi như không khác nhau mấy.

hungpecc1 · 27/5/13

để khuấy động phong trào cho các thành viên yêu thích ADO,Em có câu hỏi như vầy:
Tại sao trong câu lệnh ADO, mặc dù IMEX=1 --> tuy nhiên Recordset lại không lấy được hết vùng dữ liệu đã định ?
Cụ thể trong file đính kèm !

[GPECODE=vb]
Sub Loc_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.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 12.0;IMEX =1;HDR=Yes"";"
.Open
End With

lsSQL = "SELECT * " & _
"FROM [Du Lieu$A1:A1000] "
lrs.Open lsSQL, cnn
[B2].CopyFromRecordset lrs
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]

dhn46 · 27/5/13

- Dhn46 cũng chỉ dám "ngắm" ADO chứ chưa bước chân vào, nhưng thấy ADO hay quá nên Dhn46 cũng tìm hiểu và xin trả lời câu hỏi của bạn hungpecc1 như sau:
- Với IMEX=1: ADO sẽ kiểm tra 1 số dòng dữ liệu đầu để kiểm tra Data Type cho việc chọn lựa Type để truy vấn (1 số dòng cụ thể ở đây là 8 http://support.microsoft.com/kb/194124/EN-US)
=> Với dữ liệu trong File thì 23 dòng dữ liệu đầu là dạng Date còn 6 dòng sau là dạng Text do đó ADO sẽ kiểm tra 8 dòng dữ liệu đầu cho kết quả là dạng date do đó các loại dữ liệu khác không được truy vấn, dẫn tới sai kết quá

Xin hết ạ!

hungpecc1 · 27/5/13

dhn46 đã viết:
- Dhn46 cũng chỉ dám "ngắm" ADO chứ chưa bước chân vào, nhưng thấy ADO hay quá nên Dhn46 cũng tìm hiểu và xin trả lời câu hỏi của bạn hungpecc1 như sau:
- Với IMEX=1: ADO sẽ kiểm tra 1 số dòng dữ liệu đầu để kiểm tra Data Type cho việc chọn lựa Type để truy vấn (1 số dòng cụ thể ở đây là 8 http://support.microsoft.com/kb/194124/EN-US)
=> Với dữ liệu trong File thì 23 dòng dữ liệu đầu là dạng Date còn 6 dòng sau là dạng Text do đó ADO sẽ kiểm tra 8 dòng dữ liệu đầu cho kết quả là dạng date do đó các loại dữ liệu khác không được truy vấn, dẫn tới sai kết quá

Xin hết ạ!

^^ câu trả lời của bạn hoàn toàn giống ý kiến của mình định đưa ra ,<------- Không biết a Hai Lúa có bổ sung góp ý gì cho bọn em không ?

* Mình xin bổ sung thêm 1 điểm nữa cho câu hỏi bài trên: trong đoạn code trên mình viết :
[GPECODE=vb] IMEX = 1[/GPECODE] ( thừa 1 khoảng trắng) --> nếu viết như vầy thì coi như mục đích cái IMEX=1 là vô tác dụng : cụ thể bạn xem link bên dưới : ( bài #117 page 12)
http://www.giaiphapexcel.com/forum/showthread.php?75143-Bài-tập-về-ADO-căn-bản/page12

VetMini · 27/5/13

Làm việc với Excel Data Migration (OLEDB) thì cũng nên biết luật "định dạng bằng 8 dòng đầu", và phải kiểm soát trước khi import/export

Thứ nhất:
Luôn luôn kiểm soát xem những dòng có kiểu dữ liệu nào bị trái dạng (không giống những dòng còn lại) hay không - nhất là trường hợp bị hiểu sai.

Thứ hai:
Luôn luôn kiểm soát xem có dữ liệu text nào dài quá hay không. Nếu có thì phải đặt thêm một dòng tạm ở đầu bảng với ô text thật dài, ít nhất là dài bằng hoặc hơn ô dài nhất trong bảng.

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

Buồn đọc lại #58 xin các bạn hãy cho biết dùng cách nào ngoại trừ VBA để lấy tên sheet đầu tiên của 1 file nào đó.

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

Hai Lúa Miền Tây đã viết:
Buồn đọc lại #58 xin các bạn hãy cho biết dùng cách nào ngoại trừ VBA để lấy tên sheet đầu tiên của 1 file nào đó.

Cũng buồn vì không có người hưởng ứng.

hungpecc1 · 29/5/13

Hai Lúa Miền Tây đã viết:
Cũng buồn vì không có người hưởng ứng.

Không phải là không có người hưởng ứng anh ah, hix vì câu hỏi của anh vừa khó vừa khoai!
--> nếu mà không dùng VBA ; (tức là không dùng Sub + End sub) thì em cũng chẳng khác gì 2 tay bị trói anh ah --> đành phải pó tay thôi

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

hungpecc1 đã viết:
Không phải là không có người hưởng ứng anh ah, hix vì câu hỏi của anh vừa khó vừa khoai!
--> nếu mà không dùng VBA ; (tức là không dùng Sub + End sub) thì em cũng chẳng khác gì 2 tay bị trói anh ah --> đành phải pó tay thôi

Bạn hiểu lầm câu hỏi rồi, dùng ADO hay dùng VBA... đều bắt đầu = Sub và kết thúc = End Sub, nhưng không dùng VBA để lấy thôi. Hy vọng sẽ rõ ràng cho bạn và những bạn khác.

ndu96081631 · 29/5/13

Hai Lúa Miền Tây đã viết:
Bạn hiểu lầm câu hỏi rồi, dùng ADO hay dùng VBA... đều bắt đầu = Sub và kết thúc = End Sub, nhưng không dùng VBA để lấy thôi. Hy vọng sẽ rõ ràng cho bạn và những bạn khác.

Vậy không dùng VBA nghĩa là dùng công thức à?
Vì dù dùng ADO thì cũng được viết bằng VBA thôi (trừ phi dùng ADO nhưng được viết trên nền của VB6 hay các ngôn ngữ lập trình khác thì lại là chuyện khác)

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

ndu96081631 đã viết:
Vậy không dùng VBA nghĩa là dùng công thức à?
Vì dù dùng ADO thì cũng được viết bằng VBA thôi (trừ phi dùng ADO nhưng được viết trên nền của VB6 hay các ngôn ngữ lập trình khác thì lại là chuyện khác)

Hihi, không biết diễn giải thế nào cho nó rõ ràng nhỉ, thôi thì dựa vào tiêu đề đề tài chắc là rõ rồi.

viehoai · 29/5/13

Hai Lúa Miền Tây đã viết:
Buồn đọc lại #58 xin các bạn hãy cho biết dùng cách nào ngoại trừ VBA để lấy tên sheet đầu tiên của 1 file nào đó.

1. Nói về bài #58 này thì em tìm hiểu thì có trang này nhưng cũng không hiểu lắm, nhưng sao thầy Tuấn đã làm rồi mà vẫn còn thắc mắc nhỉ?
2. Em không biết và DAO nhưng tiểm hiểu trên mạng và làm được code sau không biết có chuẩn không:
[GPECODE=vb]Sub FsheetName()
Dim xlWB As DAO.Database
Dim strFirstSheetName As String
Set xlWB = OpenDatabase("E:\VBA\LuongTH.xls", False, True, "Excel 8.0;")
strFirstSheetName = xlWB.TableDefs(0).Name
MsgBox strFirstSheetName
xlWB.Close
End Sub

[/GPECODE]

dhn46 · 29/5/13

viehoai đã viết:
1. Nói về bài #58 này thì em tìm hiểu thì có trang này nhưng cũng không hiểu lắm, nhưng sao thầy Tuấn đã làm rồi mà vẫn còn thắc mắc nhỉ?

Theo em thì điều thầy Tuấn thắc mắc là tên sheet đầu tiên ứng với Sheet1 chứ không phải sheet hiện đầu tiên trong sheet tab như code của anh VieHoai. Cái này em tìm Google mãi chưa ra.

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

viehoai đã viết:
1. Nói về bài #58 này thì em tìm hiểu thì có trang này nhưng cũng không hiểu lắm, nhưng sao thầy Tuấn đã làm rồi mà vẫn còn thắc mắc nhỉ?
2. Em không biết và DAO nhưng tiểm hiểu trên mạng và làm được code sau không biết có chuẩn không:
[GPECODE=vb]Sub FsheetName()
Dim xlWB As DAO.Database
Dim strFirstSheetName As String
Set xlWB = OpenDatabase("E:\VBA\LuongTH.xls", False, True, "Excel 8.0;")
strFirstSheetName = xlWB.TableDefs(0).Name
MsgBox strFirstSheetName
xlWB.Close
End Sub

[/GPECODE]

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?

ndu96081631 · 29/5/13

dhn46 đã viết:
Theo em thì điều thầy Tuấn thắc mắc là tên sheet đầu tiên ứng với Sheet1 chứ không phải sheet hiện đầu tiên trong sheet tab như code của anh VieHoai. Cái này em tìm Google mãi chưa ra.

Ah, không!
Cái tôi cần là Sheet đầu tiên nhìn thấy trên sheet tab
Vấn đề là khi dùng ADO để lấy tên sheet đầu tiên mà sheet đầu có tên là "ZZZ", sheet thứ 2 có tên là "AAA" thì code sẽ xem sheet đầu tiên là sheet "AAA" (thay gì phải là "ZZZ") ---> Giống như nó đã ưu tiên sort tên sheet theo thứ tự ABC vậy

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

ndu96081631 đã viết:
Ah, không!
Cái tôi cần là Sheet đầu tiên nhìn thấy trên sheet tab
Vấn đề là khi dùng ADO để lấy tên sheet đầu tiên mà sheet đầu có tên là "ZZZ", sheet thứ 2 có tên là "AAA" thì code sẽ xem sheet đầu tiên là sheet "AAA" (thay gì phải là "ZZZ") ---> Giống như nó đã ưu tiên sort tên sheet theo thứ tự ABC vậy

DAO sẽ lấy chính xác vì nó không tự động sắp xếp như bên ADO Thầy à.

ndu96081631 · 29/5/13

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?

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!

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

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!

Em dùng Office 2013 test nó cho kết quả bình thường.

ndu96081631 · 29/5/13

Hai Lúa Miền Tây đã viết:
Em dùng Office 2013 test nó cho kết quả bình thường.

Vậy Hai Lúa vui lòng "ráp" code vào file ở bài #57 giúp tôi với

dhn46 · 29/5/13

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

VetMini · 29/5/13

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.

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

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

Còn phải xử lý bỏ qua name, vùng lọc, vùng in, tiêu đề cần lặp lại...

ndu96081631 · 29/5/13

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

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

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.

ADO sẽ không lấy tên sheet như DAO, đó là vấn đề mình muốn nói ở đây.

dhn46 · 29/5/13

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...

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

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

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

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

ndu96081631 · 29/5/13

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

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 · 29/5/13

ndu96081631 đã viết:
Bài 57 có xử lý rồi đấy

Vậy Thầy nên trả lời bài #58 ở đề tài bên đó và đưa file đính kèm cho mọi người học hỏi thêm.

VetMini · 29/5/13

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.

Đồ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.

sealand · 29/5/13

Mã:
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

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.

VetMini · 30/5/13

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.

Đề 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.

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

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)

dhn46 · 30/5/13

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)

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

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

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

Đúng là như vậy, không làm khó được bạn. Thật ra nó nằm ở #9

hungpecc1 · 30/5/13

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

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?<------------ +-+-+-+

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

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?<------------

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"

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

Xin hỏi các bạn là dùng cách nào để có thể xoá toàn bộ dữ liệu của tất cả các sheet ở 1 file nào đó mà không mở file đó không?

VetMini · 31/5/13

Hai Lúa Miền Tây đã viết:
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...

Khi trường cần select khác với trường dùng để sắp xếp thì người ta mới dùng từ chọn TOP

Mã:

"select top 1 caiGiDo From [Data$] order by stt desc"

Khi chỉ cần tìm giá trị lớn nhất thì dùng hàm MAX

Mã:

Select MAX(stt) From [Data$]

hungpecc1 · 31/5/13

Hai Lúa Miền Tây đã viết:
Xin hỏi các bạn là dùng cách nào để có thể xoá toàn bộ dữ liệu của tất cả các sheet ở 1 file nào đó mà không mở file đó không?

Vọc mãi mà không hiểu sao câu lệnh .DELTE toàn bị lỗi ISAM --> nản , thôi đành dùng cách "chuối củ " này vậy,!<---- +-+-+-+

[GPECODE=vb]
Sub DELETE()
'On Error Resume Next
Dim cnn As Object, lsSQL As String, lrs As Object, Fld As Object
Dim i As Long
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;"

.Open

End With
With lrs
.Open "SELECT * FROM [DATA$]", cnn, 3, 3
.MoveFirst
Do While Not .EOF
For Each Fld In .Fields
Fld.Value = Null
Next Fld
.MoveNext
Loop
End With
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
lấy luôn dữ liệu file A.xls và file B.xls của câu hỏi bài trước để test!
ặc quên chưa HDR=No,IMEX = 1 ,! <----bác nào test thì nhớ bổ sung nhé!

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

hungpecc1 đã viết:
Vọc mãi mà không hiểu sao câu lệnh .DELTE toàn bị lỗi ISAM --> nản , thôi đành dùng cách "chuối củ " này vậy,!<----
[GPECODE=vb]
Sub DELETE()
'On Error Resume Next
Dim cnn As Object, lsSQL As String, lrs As Object, Fld As Object
Dim i As Long
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;"

.Open

End With
With lrs
.Open "SELECT * FROM [DATA$]", cnn, 3, 3
.MoveFirst
Do While Not .EOF
For Each Fld In .Fields
Fld.Value = Null
Next Fld
.MoveNext
Loop
End With
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
lấy luôn dữ liệu file A.xls và file B.xls của câu hỏi bài trước để test!
ặc quên chưa HDR=No,IMEX = 1 ,! <----bác nào test thì nhớ bổ sung nhé!

Lại dùng chiêu "ngoại đạo", câu hỏi là xoá toàn bộ dữ liệu của tất cả các sheet ở 1 file nào đó, nếu như dữ liệu nhiều thì duyệt qua từng dòng thì sẽ rất chậm, ít ra cũng phải dùng giống như bài #118 , nhưng sẽ còn cách nữa phải không các bạn?

viehoai · 31/5/13

Hai Lúa Miền Tây đã viết:
Xin hỏi các bạn là dùng cách nào để có thể xoá toàn bộ dữ liệu của tất cả các sheet ở 1 file nào đó mà không mở file đó không?

Để giải bài này em lại đi giải xóa toàn bộ dữ liệu của 1 Sheet nhưng vẫn chưa được. Vậy anh có thể giúp câu lệnh xóa toàn bộ dữ liệu sheet "tb1", file A.xls được không? Em đang nghỉ đến UPDATE toàn bộ thành Null nhưng vẫn chưa được
Xin cảm ơn anh

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

viehoai đã viết:
Để giải bài này em lại đi giải xóa toàn bộ dữ liệu của 1 Sheet nhưng vẫn chưa được. Vậy anh có thể giúp câu lệnh xóa toàn bộ dữ liệu sheet "tb1", file A.xls được không? Em đang nghỉ đến UPDATE toàn bộ thành Null nhưng vẫn chưa được
Xin cảm ơn anh

Nếu Update thì Anh nghiên cứu kỹ bài #118 là làm được.

hungpecc1 · 31/5/13

Hai Lúa Miền Tây đã viết:
Nếu Update thì Anh nghiên cứu kỹ bài #118 là làm được.

Hix em cũng vọc vụ Update này từ sáng mà chưa xong:
Nếu mà 1 File không biết vùng dữ liệu ở đâu, tên tuổi như thế nào ? thì có 2 vấn đề em đang vướng như sau :
*không thể dùng HDR = Yes đươc
* Không lẽ lại liệt kê từ f1,.....fn , ( cái này cũng không đúng, nhỡ dữ liệu nó chỉ năm từ fm đến fn ) thì sao ?
-->^^ anh thử gợi ý vụ update này thêm được không ?

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

hungpecc1 đã viết:
Hix em cũng vọc vụ Update này từ sáng mà chưa xong:
Nếu mà 1 File không biết vùng dữ liệu ở đâu, tên tuổi như thế nào ? thì có 2 vấn đề em đang vướng như sau :
*không thể dùng HDR = Yes đươc
* Không lẽ lại liệt kê từ f1,.....fn , ( cái này cũng không đúng, nhỡ dữ liệu nó chỉ năm từ fm đến fn ) thì sao ?
-->^^ anh thử gợi ý vụ update này thêm được không ?

Nếu không biết bao nhiêu cột thì mình lấy tên cột, đưa tên cột đó vào 1 chuổi truy vấn là được. Nhưng hướng này hơi khó và mất thời gian chạy hơn. Bạn thử nhé.

hungpecc1 · 31/5/13

Hai Lúa Miền Tây đã viết:
Nếu không biết bao nhiêu cột thì mình lấy tên cột, đưa tên cột đó vào 1 chuổi truy vấn là được. Nhưng hướng này hơi khó và mất thời gian chạy hơn. Bạn thử nhé.

Em thấy vừa khó, vừa khoai, vừa khó nhai hix --> anh test thử xem cách dùng UPDATE để xoá em viết đã ok chưa ?
[GPECODE=vb]
Sub DELETE()
'On Error Resume Next
Dim cnn As ADODB.Connection, rsSchema As ADODB.Recordset, rst As ADODB.Recordset
Dim source As String, TableName As String
Dim i As Long, szFld As String
Set cnn = New ADODB.Connection
source = Application.GetOpenFilename
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & source & _
";Extended Properties=""Excel 8.0;HDR=No;"";"

.Open
End With
Set rst = New ADODB.Recordset
Set rsSchema = cnn.OpenSchema(adSchemaTables)
While Not rsSchema.EOF
TableName = rsSchema.Fields("TABLE_NAME").Value
With rst
.Open "SELECT * FROM [" & TableName & "]", cnn, 3, 3
For i = 1 To .Fields.Count
szFld = szFld & " F" & i & "= Null"
Next
.Close
End With
rst.Open "UPDATE [" & TableName & "] SET " & Replace(Trim(szFld), " ", ", "), cnn, 3, 1
rsSchema.MoveNext
szFld = vbNullString
Wend
rsSchema.Close: Set rsSchema = Nothing: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
ps: còn cách thứ 2 không dùng update , ^^ anh phải gợi ý thêm 1 ít thì em mới có hướng để đi , không thì cũng bó tay -+*/

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

hungpecc1 đã viết:
Em thấy vừa khó, vừa khoai, vừa khó nhai hix --> anh test thử xem cách dùng UPDATE để xoá em viết đã ok chưa ?
[GPECODE=vb]
Sub DELETE()
'On Error Resume Next
Dim cnn As ADODB.Connection, rsSchema As ADODB.Recordset, rst As ADODB.Recordset
Dim source As String, TableName As String
Dim i As Long, szFld As String
Set cnn = New ADODB.Connection
source = Application.GetOpenFilename
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & source & _
";Extended Properties=""Excel 8.0;HDR=No;"";"

.Open
End With
Set rst = New ADODB.Recordset
Set rsSchema = cnn.OpenSchema(adSchemaTables)
While Not rsSchema.EOF
TableName = rsSchema.Fields("TABLE_NAME").Value
With rst
.Open "SELECT * FROM [" & TableName & "]", cnn, 3, 3
For i = 1 To .Fields.Count
szFld = szFld & " F" & i & "= Null"
Next
.Close
End With
rst.Open "UPDATE [" & TableName & "] SET " & Replace(Trim(szFld), " ", ", "), cnn, 3, 1
rsSchema.MoveNext
szFld = vbNullString
Wend
rsSchema.Close: Set rsSchema = Nothing: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]
ps: còn cách thứ 2 không dùng update , ^^ anh phải gợi ý thêm 1 ít thì em mới có hướng để đi , không thì cũng bó tay

Kết quả như mong đợi, còn cách không dùng update thì nếu gợi ý là ra liền, nên không thể gợi ý

, bạn chịu khó chút nữa là được.

P/s: Trình độ ADO của bạn càng lúc càng thâm hậu rồi đó.

VetMini · 31/5/13

Cái này có thấy qua lâu lắm rồi. Bây giờ lười tìm lại quá. Hình như là dùng lệnh DROP thì phải.

Nhớ mang máng có vụ cãi vả về lệnh này. Theo tiêu chuẩn thì lệnh bôi dữ liệu table của SQL là TRUNCATE, trong khi đó lệnh DROP TABLE sẽ xoá cả table luôn. Nhưng cụ Excel engine làm việc ngược ngạo, không cho dùng lệnh truncate, và lệnh drop của cụ chỉ bôi dữ liệu chứ không xoá sheet.

hungpecc1 · 31/5/13

Hai Lúa Miền Tây đã viết:
Kết quả như mong đợi, còn cách không dùng update thì nếu gợi ý là ra liền, nên không thể gợi ý , bạn chịu khó chút nữa là được.

P/s: Trình độ ADO của bạn càng lúc càng thâm hậu rồi đó.

ado ko được em định dùng DAO, query delete xem có được ko? Nhưng máy em 64bits >ko hiểu sao ko chạy được DAO 3.6,nên em chưa test được? Toàn báo lỗi run time. 46,nản đi ngủ luôn!

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

VetMini đã viết:
Cái này có thấy qua lâu lắm rồi. Bây giờ lười tìm lại quá. Hình như là dùng lệnh DROP thì phải.

Nhớ mang máng có vụ cãi vả về lệnh này. Theo tiêu chuẩn thì lệnh bôi dữ liệu table của SQL là TRUNCATE, trong khi đó lệnh DROP TABLE sẽ xoá cả table luôn. Nhưng cụ Excel engine làm việc ngược ngạo, không cho dùng lệnh truncate, và lệnh drop của cụ chỉ bôi dữ liệu chứ không xoá sheet.

Chính xác là phải dùng DROP TABLE trong trường hợp này.

hungpecc1 · 1/6/13

Hai Lúa Miền Tây đã viết:
Chính xác là phải dùng DROP TABLE trong trường hợp này.

Theo gợi ý của anh : em đưa ra lời giải như sau :
[GPECODE=vb]
Sub DELETE()
Dim rsSchema As ADODB.Recordset
Dim FileName As String, TableName As String
FileName = Application.GetOpenFilename(",*.xls")
With New ADODB.Connection
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & FileName & _
";Extended Properties=""Excel 12.0;HDR=No;"";"
.Open
Set rsSchema = .OpenSchema(adSchemaTables)
While Not rsSchema.EOF
TableName = rsSchema.Fields("TABLE_NAME").Value
.Execute " DROP TABLE [" & TableName & "]"
rsSchema.MoveNext
Wend
.Close
End With
End Sub
[/GPECODE]

viehoai · 3/6/13

Hai Lúa Miền Tây đã viết:
Chính xác là phải dùng DROP TABLE trong trường hợp này.

Nếu xác định tên Table để xóa dùng DAO thì với Excell 2003 là
[GPECODE=vb]Sub DELETE_DAO_2003()
Dim db As DAO.Database
Dim tdf As TableDef
Dim t As ADOX.Table
Dim FileName As String, TableName As String
FileName = Application.GetOpenFilename(",*.xls")
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & _
";Extended Properties=""Excel 8.0;HDR=no;"";"
.Open

Set db = OpenDatabase(FileName, False, True, "Excel 8.0;")
For Each tdf In db.TableDefs
.Execute " DROP TABLE [" & tdf.Name & "]"
Next
Set db = Nothing
.Close
End With
End Sub[/GPECODE]
Kết quả chạy tốt. Nhưng với Excel 2010 là
[GPECODE=vb]Sub DELETE_DAO_2010()
Dim db As DAO.Database
Dim tdf As TableDef
Dim t As ADOX.Table
Dim FileName As String, TableName As String
FileName = Application.GetOpenFilename(",*.xlsx")
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & FileName & _
";Extended Properties=""Excel 12.0;HDR=No;"";"
.Open

Set db = OpenDatabase(FileName, False, True, "Excel 12.0;")
For Each tdf In db.TableDefs
.Execute " DROP TABLE [" & tdf.Name & "]"
Next
Set db = Nothing
.Close
End With
End Sub[/GPECODE]
Bào lỗi lại dòng Set db = OpenDatabase(FileName, False, True, "Excel 12.0;")

Vậy xin hỏi các anh chi phải sửa câu lênh trên như thế nào. Em xin cảm ơn

ndu96081631 · 3/6/13

viehoai đã viết:
N
Bào lỗi lại dòng Set db = OpenDatabase(FileName, False, True, "Excel 12.0;")

Vậy xin hỏi các anh chi phải sửa câu lênh trên như thế nào. Em xin cảm ơn

Hôm trước dùng DAO trên Excel 2010, tôi thấy chẳng cần phải sửa lại dòng này, cứ để "Excel 8.0;" nó vẫn chạy
Bạn thử để nguyên vậy xem thế nào
Set db = OpenDatabase(FileName, False, True, "Excel 8.0;")

viehoai · 3/6/13

ndu96081631 đã viết:
Hôm trước dùng DAO trên Excel 2010, tôi thấy chẳng cần phải sửa lại dòng này, cứ để "Excel 8.0;" nó vẫn chạy
Bạn thử để nguyên vậy xem thế nào
Set db = OpenDatabase(FileName, False, True, "Excel 8.0;")

Nếu như vậy thì nó lỗi như sau Sư phụ ạ

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

Ví dụ tôi có 2 Workbook A.xls và B.xls, làm thế nào ở WB A.xls tôi có thể ghi dữ liệu vào WB B.xls như hình bên dưới.

WB A.xls:

WB B.xls

Xin cảm ơn.

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

viehoai đã viết:
Nhưng với Excel 2010 là
[GPECODE=vb]Sub DELETE_DAO_2010()
Dim db As DAO.Database
Dim tdf As TableDef
Dim t As ADOX.Table
Dim FileName As String, TableName As String
FileName = Application.GetOpenFilename(",*.xlsx")
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & FileName & _
";Extended Properties=""Excel 12.0;HDR=No;"";"
.Open

Set db = OpenDatabase(FileName, False, True, "Excel 12.0;")
For Each tdf In db.TableDefs
.Execute " DROP TABLE [" & tdf.Name & "]"
Next
Set db = Nothing.Close
End With
End Sub[/GPECODE]
Bào lỗi lại dòng Set db = OpenDatabase(FileName, False, True, "Excel 12.0;")
View attachment 103235
Vậy xin hỏi các anh chi phải sửa câu lênh trên như thế nào. Em xin cảm ơn

Anh chỉ cần chỉnh Excel 12.0 thành Excel 8.0 là được.

Mã:

Set db = OpenDatabase(FileName, False, True, "Excel 8.0;")

dhn46 · 17/6/13

Hai Lúa Miền Tây đã viết:
Ví dụ tôi có 2 Workbook A.xls và B.xls, làm thế nào ở WB A.xls tôi có thể ghi dữ liệu vào WB B.xls như hình bên dưới.
..............................
Xin cảm ơn.

Anh cho em hỏi: có được phép đặt sẵn f2, f3 tại B1, D1 SheetB Workbook B không anh?

hungpecc1 · 18/6/13

Hai Lúa Miền Tây đã viết:
Ví dụ tôi có 2 Workbook A.xls và B.xls, làm thế nào ở WB A.xls tôi có thể ghi dữ liệu vào WB B.xls như hình bên dưới.

WB A.xls:

View attachment 104076

WB B.xls

View attachment 104077

Xin cảm ơn.

Anh cho em hỏi, :
*câu hỏi của anh là :ghi sheetB có sẵn trong B.xls hay là tự tạo một sheet mới rồi ghi vào !

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

dhn46 đã viết:
Anh cho em hỏi: có được phép đặt sẵn f2, f3 tại B1, D1 SheetB Workbook B không anh?

Không được phép thêm vào anh à.

hungpecc1 đã viết:
Anh cho em hỏi, :
*câu hỏi của anh là :ghi sheetB có sẵn trong B.xls hay là tự tạo một sheet mới rồi ghi vào !

Ghi lên sheetB có sẵn trong B.xls bạn à.

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

Không biết các bạn Dhn46, Hungpecc1, VieHoai ... đã tìm ra câu trả lời cho bài này chưa?

hungpecc1 · 19/6/13

Hai Lúa Miền Tây đã viết:
Không biết các bạn Dhn46, Hungpecc1, VieHoai ... đã tìm ra câu trả lời cho bài này chưa?

chưa anh ah, em ngâm cứu mãi mà không ra, mấy hôm vừa rồi em thử vọc ALTER TABLE trong excel mà chẳng thấy "hiệu ứng " gì cả ! có lẽ hướng này đi vào ngõ cụt rồi !
Để em ngâm cứu đi theo hướng khác +-+-+-+

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

hungpecc1 đã viết:
chưa anh ah, em ngâm cứu mãi mà không ra, mấy hôm vừa rồi em thử vọc ALTER TABLE trong excel mà chẳng thấy "hiệu ứng " gì cả ! có lẽ hướng này đi vào ngõ cụt rồi !
Để em ngâm cứu đi theo hướng khác

Gợi ý cho bạn: Bạn hãy đi theo hướng chèn dữ liệu bình thường sẽ ra, đừng suy nghĩ phức tạp quá nhé.

dhn46 · 19/6/13

Hai Lúa Miền Tây đã viết:
Không biết các bạn Dhn46, Hungpecc1, VieHoai ... đã tìm ra câu trả lời cho bài này chưa?

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

Mã:

Option Explicit
Sub CopyDL()
    Dim i As Long, Target As String
    Dim cnn As Object, lsSQL As String, lrs As Object
    For i = 1 To 2
        Set cnn = CreateObject("ADODB.Connection")
        Set lrs = CreateObject("ADODB.Recordset")
        If i = 1 Then
            Target = ThisWorkbook.Path & "\B.XLS"
            lsSQL = "CREATE TABLE [sheetB$] (f1 char(100), f2 char(100), f4 char(100), f3 float)"
        Else
            Target = ThisWorkbook.FullName
            lsSQL = "INSERT INTO [SheetB$] IN '" & ThisWorkbook.Path & _
                    "\B.xls ' 'Excel 8.0;' SELECT f2, f3 FROM [DataA$A2:C16]"
        End If
        With cnn
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source= " & Target & _
                                ";Extended Properties=""Excel 8.0;HDR=No;"";"
            .Open
        End With
        lrs.Open lsSQL, cnn, 3, 1
        Set lrs = Nothing
    Next
End Sub

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

dhn46 đã viết:

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

Mã:

Option Explicit
Sub CopyDL()
    Dim i As Long, Target As String
    Dim cnn As Object, lsSQL As String, lrs As Object
    For i = 1 To 2
        Set cnn = CreateObject("ADODB.Connection")
        Set lrs = CreateObject("ADODB.Recordset")
        If i = 1 Then
            Target = ThisWorkbook.Path & "\B.XLS"
            lsSQL = "CREATE TABLE [sheetB$] (f1 char(100), f2 char(100), f4 char(100), f3 float)"
        Else
            Target = ThisWorkbook.FullName
            lsSQL = "INSERT INTO [SheetB$] IN '" & ThisWorkbook.Path & _
                    "\B.xls ' 'Excel 8.0;' SELECT f2, f3 FROM [DataA$A2:C16]"
        End If
        With cnn
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source= " & Target & _
                                ";Extended Properties=""Excel 8.0;HDR=No;"";"
            .Open
        End With
        lrs.Open lsSQL, cnn, 3, 1
        Set lrs = Nothing
    Next
End Sub

Có 2 điểm cần lưu ý như sau:

Khi chạy code nó mất hết dòng đầu tiên.
Không được tạo thêm dòng tên cột, xin bật mí chút xíu là hãy dùng cách khác đơn giản mà không cần tạo tiêu đề cột.

hungpecc1 · 21/6/13

Câu đố lần này khó quá ! anh phải gợi ý thêm tẹo nữa để định hướng đi vậy ! -+*/

dhn46 · 21/6/13

Em đông ý với bạn Hungpecc1. Đề lần này khó với việc không tạo tiêu đề cột. Anh gợi ý chit chút nữa, để tụi em tìm kiếm nghiền ngẫm, suy đoán cách làm
Hiểu và ứng dụng ADO sẽ là 1 lợi thế vô cùng lớn. Cảm ơn anh Hai lúa chia sẻ và hướng dẫn. Chúc anh sứ khỏe, thánh công!

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

hungpecc1 đã viết:
Câu đố lần này khó quá ! anh phải gợi ý thêm tẹo nữa để định hướng đi vậy !

dhn46 đã viết:
Em đông ý với bạn Hungpecc1. Đề lần này khó với việc không tạo tiêu đề cột. Anh gợi ý chit chút nữa, để tụi em tìm kiếm nghiền ngẫm, suy đoán cách làm
Hiểu và ứng dụng ADO sẽ là 1 lợi thế vô cùng lớn. Cảm ơn anh Hai lúa chia sẻ và hướng dẫn. Chúc anh sứ khỏe, thánh công!

Cảm ơn các bạn đã tham gia, xin gợi ý 90% đề bài như sau:

* Mở file B.xls lên, định dạng các cột tương ứng với kiểu dữ liệu cần đưa vào rồi lưu lại là được.

dhn46 · 24/6/13

Hai Lúa Miền Tây đã viết:
Cảm ơn các bạn đã tham gia, xin gợi ý 90% đề bài như sau:

* Mở file B.xls lên, định dạng các cột tương ứng với kiểu dữ liệu cần đưa vào rồi lưu lại là được.

Em mạnh dạn đưa câu trả lời lần 2 dựa trên gợi ý anh HLMT. Anh xem và góp ý nhé

Mã:

Sub ChuyenDL()
    On Error GoTo Handle
    Dim cnn As Object, lsSQL As String, lrs As Object
    Dim WbOpen As Workbook, Sh As Worksheet
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    Application.ScreenUpdating = False
    Set WbOpen = Application.Workbooks.Open(ThisWorkbook.Path & "\B.xls")
    With WbOpen
        For Each Sh In .Worksheets
            If Sh.Name = "SheetB" Then
                Sh.Range("A1:C1").NumberFormat = "@"
                Sh.Range("A1:D1").NumberFormat = "0"
                Exit For
            End If
        Next
        .Save
        .Close
    End With
    Application.ScreenUpdating = True
    With cnn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & ThisWorkbook.FullName & _
                ";Extended Properties=""Excel 8.0;HDR=No;Imex=2"";"
        .Open
    End With
    lsSQL = "INSERT INTO [SheetB$](f2,f4) IN '" & ThisWorkbook.Path & _
            "\B.xls ' 'Excel 8.0;' SELECT f2,f3 FROM [DataA$A1:D16]"
    lrs.Open lsSQL, cnn, 3, 1
    Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
    Exit Sub
Handle:
    MsgBox Err.Description
End Sub

hungpecc1 · 24/6/13

dhn46 đã viết:
Em mạnh dạn đưa câu trả lời lần 2 dựa trên gợi ý anh HLMT. Anh xem và góp ý nhé

khôgn biết Có cách nào mà dùng SQL để định dạng dữ liệu ở B.xls không nhỉ ?
vì theo mình nếu mà dùng workbooks.Open thì ta copy luôn dữ liệu vào luôn, cần gì INSERT nữa nhỉ ? --> không biết ý anh HLMT thế nào ?

dhn46 · 24/6/13

hungpecc1 đã viết:
khôgn biết Có cách nào mà dùng SQL để định dạng dữ liệu ở B.xls không nhỉ ?
vì theo mình nếu mà dùng workbooks.Open thì ta copy luôn dữ liệu vào luôn, cần gì INSERT nữa nhỉ ? --> không biết ý anh HLMT thế nào ?

Mình cũng đã nghĩ theo hướng này nhưng quả thực chưa tìm được cách, đành phải "liều" dùng VBA để làm. Không để topic này chìm quá lâu được.

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

Thật ra các bạn đừng suy nghĩ chi thêm sâu xa, mình đã nói là mở file B.xls ra và định dạng nó, lưu rồi đóng lại = cách thủ công mà không dùng code ấy. Đó là cái "mẹo" để các bạn tự nâng cao "tay nghề" thêm thôi.

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

Ví dụ dữ liệu của tôi có 100 dòng, bây giờ tôi muốn lấy 10 dòng đầu và 10 dòng cuối trong 1 câu lệnh thì phải làm như thế nào để ra được kết quả như hình.

VetMini · 4/7/13

Theo như dữ liệu trên, có cột ID thì chỉ việc select top 10 order theo ID, union với top 10 order theo ID ngược, xong order lại cho theo ID xuôi.

Không có cột ID thì rất phức tạp. Access không hổ trợ hàm đánh số dòng cho nên phải kèm theo thuật toán đánh số rất rắc rối.

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

VetMini đã viết:
Theo như dữ liệu trên, có cột ID thì chỉ việc select top 10 order theo ID, union với top 10 order theo ID ngược, xong order lại cho theo ID xuôi.

Vậy bạn thử viết cái Union theo hướng này thử nhé, đó là lý thuyết cái quan trọng là viết như thế nào.

dhn46 · 4/7/13

Em nộp bài

Mã:

Option Explicit
Sub Copy()
    On Error GoTo Handle
    Dim cnn As Object, lsSQL As String, lrs As Object, FileFullName As String
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    FileFullName = Application.ThisWorkbook.FullName
    With cnn
        If Val(Application.Version) < 12 Then
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=No"";"
        End If
        .Open
    End With
    lsSQL = "Select f1,f2,f3,f4,'1:10' as col from [Data$A2:D600] where f1<11 union all select f1,f2,f3,f4,'91:100' as col from [Data$A2:D600] where f1>90"
    
    lrs.Open lsSQL, cnn, 3, 1
    Sheet1.[H2:K6000].ClearContents
    Sheet1.Range("H2").CopyFromRecordset lrs
    Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
    Exit Sub
Handle:
    MsgBox Err.Description
End Sub

GPE quay quay thoát ra rồi vào mấy lần mới nộp được, híc

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

Vậy ta không dùng union thì câu lệnh khác sẽ như thế nào?

dhn46 · 4/7/13

Hai Lúa Miền Tây đã viết:
Vậy ta không dùng union thì câu lệnh khác sẽ như thế nào?

Nếu không Union thì như thế này có được không anh

Mã:

    lsSQL = "Select f1,f2,f3,f4,iif(f1<11,'1:10','91:100') as col from [Data$A2:D600] where f1<11 or f1>90"

dhn46 · 5/7/13

Em đưa ra 1 phương án nữa, cái này có được không anh HLMT?

Mã:

    lsSQL = "Select * From " _
            & "(Select top 10 f1,f2,f3,f4,'1:10' as Col from [Data$A2:D600] Order by f1 " _
            & "Union all " _
            & "Select top 10 a.f1,a.f2,a.f3,a.f4,'91:10' as Col " _
            & "from (Select f1,f2,f3,f4 From [Data$A2:D600] Order by f1 Desc)as a) as b " _
            & "Order by b.f1"

ADO ứng dụng viết Code rất hay, ngày trước khi chưa biết thì mày mò với Array, Dictionary. Giờ ứng dụng ADO thấy đơn giản hơn và cũng rất thú vị, không hiểu sao Topic này ít người tham gia thế?

Cảm ơn anh HLMT nhiều!

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

dhn46 đã viết:
Em đưa ra 1 phương án nữa, cái này có được không anh HLMT?

Mã:

lsSQL = "Select * From " _ & "(Select top 10 f1,f2,f3,f4,'1:10' as Col from [Data$A2:D600] Order by f1 " _ & "Union all " _ & "Select top 10 a.f1,a.f2,a.f3,a.f4,'91:10' as Col " _ & "from (Select f1,f2,f3,f4 From [Data$A2:D600] Order by f1 Desc)as a) as b " _ & "Order by b.f1"

ADO ứng dụng viết Code rất hay, ngày trước khi chưa biết thì mày mò với Array, Dictionary. Giờ ứng dụng ADO thấy đơn giản hơn và cũng rất thú vị, không hiểu sao Topic này ít người tham gia thế?

Cảm ơn anh HLMT nhiều!

Tất cả phương án bạn đưa ra đều đúng yêu cầu. Tuy nhiên phải gõ tay cột gom nhóm. Bạn thử không gõ tay cột này xem sao nhé.

VetMini · 5/7/13

dhn46 đã viết:
Em đưa ra 1 phương án nữa, cái này có được không anh HLMT?

Mã:

lsSQL = "Select * From " _ & "(Select top 10 f1,f2,f3,f4,'1:10' as Col from [Data$A2:D600] Order by f1 " _ & "Union all " _ & "Select top 10 a.f1,a.f2,a.f3,a.f4,'91:10' as Col " _ & "from (Select f1,f2,f3,f4 From [Data$A2:D600] Order by f1 Desc)as a) as b " _ & "Order by b.f1"

ADO ứng dụng viết Code rất hay, ngày trước khi chưa biết thì mày mò với Array, Dictionary. Giờ ứng dụng ADO thấy đơn giản hơn và cũng rất thú vị, không hiểu sao Topic này ít người tham gia thế?

Cảm ơn anh HLMT nhiều!

ADO có tất cả 5 Objects, trong Excel ta sử dụng 3.
Khi sử dụng ADO, Excel VBA dùng engine của Access. Trước đây engine này theo tiêu chuẩn JET (Access giấu mặt); về sau này MS xác định thẳng nó là Access. (*)
Sử dụng nó có 2 điểm bất lợi so với code trực tiếp (array mapping):
1. Đối tượng ADO bắt buộc phải xem file (xls*, txt, ...) là file phẳng (flat) và xuôi (sequential) cho nên nó phải tuân thủ theo các giới hạn về cột, dòng; và các giới hạn về thao tác (ví dụ cách chèn dòng, xoá dòng)
2. Phải học thêm ngôn ngữ SQL. SQL của Access lại không theo sát tiêu chuẩn cho nên lúc sử dụng phải học hết các ngoại lệ của nó.

Vì các bất lợi trên cho nên người ta chỉ dùng nó để import dữ liệu hàng loạt. Các thao tác khác chỉ biết chơi cho vui thôi chứ không nên xem như là một chiều hướng lập trình.

(*) Tôi chỉ nói trong phạm vi kết nối file xls,csv,txt mà chúng ta vẫn dùng ở đây. Đương nhiên là nếu bạn cho OLE DB nối với SQL Server, Orracle thì chúng dùng engine và tiêu chuẩn SQL khác.

VetMini · 5/7/13

Nếu id là số liên tục thì có thể không dùng Union. Nhưng lệnh này sẽ chạy chậm.

select * from Bảng where (id <= 10 or (select max(id) from Bang) - id <= 9)

hungpecc1 · 5/7/13

VetMini đã viết:
Nếu id là số liên tục thì có thể không dùng Union. Nhưng lệnh này sẽ chạy chậm.

select * from Bảng where (id <= 10 or (select max(id) from Bang) - id <= 9)

cho Em hỏi thêm anh là: nếu trong cột ID định dạng là text --> thì các mệnh đề WHERE ID <=10 có vẻ không ổn cho lắm , lúc này ta phải giải quyết như thế nào !

VetMini · 5/7/13

hungpecc1 đã viết:
cho Em hỏi thêm anh là: nếu trong cột ID định dạng là text --> thì các mệnh đề WHERE ID <=10 có vẻ không ổn cho lắm , lúc này ta phải giải quyết như thế nào !

Bạn có thấy chuỗi từ "Nếu id là số liên tục" không?
Nếu nó không phải là số liên tục thì phải dùng phương pháp rank. Tôi luời tra cứu cách sử dụng hàm rank trong Access quá (tôi chỉ chuyên dùng T-SQL và SQL Plus)

hungpecc1 · 5/7/13

VetMini đã viết:
Bạn có thấy chuỗi từ "Nếu id là số liên tục" không?
Nếu nó không phải là số liên tục thì phải dùng phương pháp rank. Tôi luời tra cứu cách sử dụng hàm rank trong Access quá (tôi chỉ chuyên dùng T-SQL và SQL Plus)

Không ý em không phải "ID có liên tục không ", mà bây giờ tại file excel , tất cả các giá trị cột ID là text (ví dụ như 1 --> "1") thì câu lệnh WHERE ID < = 10 sẽ gặp lỗi !

VetMini · 5/7/13

Nếu biết chắc nó là chuỗi số thì dùng hàm CInt hoặc CLng
select * from Bang where (CInt(id) <= 10 or (select CInt(max(id)) from Bang) - CInt(id) <= 9)

Đính chính: CInt là hàm của VBA, trong query dùng hàm VAL

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

VetMini đã viết:
Nếu biết chắc nó là chuỗi số thì dùng hàm CInt hoặc CLng
select * from Bang where (CInt(id) <= 10 or (select CInt(max(id)) from Bang) - CInt(id) <= 9)

Đính chính: CInt là hàm của VBA, trong query dùng hàm VAL

Ta dùng between cho nó gọn và nhẹ

VetMini · 6/7/13

Sau một đêm suy nghĩ, đã tìm ra cách viết không dùng UNION và cũng không phải dựa vào tính chất liên tục của ID.

Select * from Bang
Where ID IN (Select Top 10 ID From Bang Order By ID Asc)
OR ID IN (Select Top 10 ID From Bang Order By ID Desc)

Cách này chỉ là mẹo khai triển định nghĩa của UNION. Lưu ý là tính tố IN có thể chạy rất chậm đối với một số CSDL

@HLMT:
BETWEEN hàm tính tố AND. Ở đây tuy đề bài là 10 đầu vả 10 cuối nhưng nếu không dùng Union thì giải thuật là 10+10 ở đầu hoặc ở cuối

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

VetMini đã viết:
Sau một đêm suy nghĩ, đã tìm ra cách viết không dùng UNION và cũng không phải dựa vào tính chất liên tục của ID.

Select * from Bang
Where ID IN (Select Top 10 ID From Bang Order By ID Asc)
OR ID IN (Select Top 10 ID From Bang Order By ID Desc)

Cách này chỉ là mẹo khai triển định nghĩa của UNION. Lưu ý là tính tố IN có thể chạy rất chậm đối với một số CSDL

@HLMT:
BETWEEN hàm tính tố AND. Ở đây tuy đề bài là 10 đầu vả 10 cuối nhưng nếu không dùng Union thì giải thuật là 10+10 ở đầu hoặc ở cuối

Còn cột Group thì bạn sẽ xử lý như thế nào hả bạn?

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

Hai Lúa Miền Tây đã viết:
Tất cả phương án bạn đưa ra đều đúng yêu cầu. Tuy nhiên phải gõ tay cột gom nhóm. Bạn thử không gõ tay cột này xem sao nhé.

Hai Lúa Miền Tây đã viết:
Còn cột Group thì bạn sẽ xử lý như thế nào hả bạn?

Không biết các bạn giải quyết được yêu cầu trên chưa?

dhn46 · 10/7/13

Hai Lúa Miền Tây đã viết:
Không biết các bạn giải quyết được yêu cầu trên chưa?

Em xin đưa ra 2 câu truy vấn cho 2 phương án em giải lần trước
Gom nhóm không đánh tay các trường

1/ Dùng Union

Mã:

lsSQL = "Select a.*,'1:10' as f5 from [Data$A2:D600] a where f1<11 union all select b.*,'91:100' as f5 from [Data$A2:D600] b where f1>90"

2/ Không dùng Union

Mã:

     lsSQL = "Select a.*,iif(f1<11,'1:10','91:100') as col from [Data$A2:D600] a where f1<11 or f1>90"

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

dhn46 đã viết:
Em xin đưa ra 2 câu truy vấn cho 2 phương án em giải lần trước
Gom nhóm không đánh tay các trường

1/ Dùng Union

Mã:

lsSQL = "Select a.*,'1:10' as f5 from [Data$A2:D600] a where f1<11 union all select b.*,'91:100' as f5 from [Data$A2:D600] b where f1>90"

2/ Không dùng Union

Mã:

lsSQL = "Select a.*,iif(f1<11,'1:10','91:100') as col from [Data$A2:D600] a where f1<11 or f1>90"

Rất sáng tạo nhưng vẫn chưa theo yêu cầu. Thôi thì làm từng phần, là làm sao lấy ra kết quả cột Group (1:10,11:20,21:30...91:100), bỏ điều kiện đi nhé.

dhn46 · 10/7/13

Hai Lúa Miền Tây đã viết:
Rất sáng tạo nhưng vẫn chưa theo yêu cầu. Thôi thì làm từng phần, là làm sao lấy ra kết quả cột Group (1:10,11:20,21:30...91:100), bỏ điều kiện đi nhé.

Giờ em mới hiểu câu hỏi bỏ đánh trường bằng tay...hihi +-+-+-+

Em đưa ra phương án như thế này, anh góp ý nhé

Mã:

lsSQL = "Select a.*,int((f1-1)/10)*10+1&':'&int((f1-1)/10+1)*10 as col from [Data$A2:D600] a where f1<11 or f1>90"

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

dhn46 đã viết:
Giờ em mới hiểu câu hỏi bỏ đánh trường bằng tay...hihi
Em đưa ra phương án như thế này, anh góp ý nhé

Mã:

lsSQL = "Select a.*,int((f1-1)/10)*10+1&':'&int((f1-1)/10+1)*10 as col from [Data$A2:D600] a where f1<11 or f1>90"

Sẽ mất hết 1 dòng dữ liệu đầu tiên bạn à.

VetMini · 11/7/13

Cột group tôi cố tình không nói tới. Lý do là bị ngặt chỗ, nếu bảng có dưới 20 dòng thì sao? Trong câu truy vấn trước đây, tôi không có đề cập tới chuyện này, nhưng nó vẫn có thể giải quyết được dễ dàng bằng cách dùng lệnh lọc DISTINCT. Một khi phải đặt Group thì group ra sao? Những dòng trùng nhau thuộc về group nào?

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

VetMini đã viết:
Cột group tôi cố tình không nói tới. Lý do là bị ngặt chỗ, 1./ nếu bảng có dưới 20 dòng thì sao? Trong câu truy vấn trước đây, tôi không có đề cập tới chuyện này, nhưng nó vẫn có thể giải quyết được dễ dàng bằng cách dùng lệnh lọc DISTINCT. Một khi phải đặt Group thì group ra sao? 2./ Những dòng trùng nhau thuộc về group nào?

1./ Dưới 20 dòng thì cũng chạy bình thường
2./ Những dòng trùng nhau sẽ thuộc về group thỏa điều kiện với nó. Ví dụ có 15 dòng hoặc n dòng nhỏ hơn 10 thì toàn bộ dòng đó sẽ thuộc về Group 1:10

dhn46 · 11/7/13

Hai Lúa Miền Tây đã viết:
Sẽ mất hết 1 dòng dữ liệu đầu tiên bạn à.

Anh HLMT em kiểm tra sao không thấy mất nhỉ? Nhưng với câu trả lời này của anh thì chắc chắn còn phương án "tuyệt chiêu" nữa...

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

dhn46 đã viết:
Anh HLMT em kiểm tra sao không thấy mất nhỉ? Nhưng với câu trả lời này của anh thì chắc chắn còn phương án "tuyệt chiêu" nữa...

Thôi trả lời luôn, đó là mình dùng hàm Partition để chia dữ liệu:

[GPECODE=sql]Sub Loc_HLMT()
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 ID, I_DATE, [Q'TY], REMARKS, trim(Partition([id],1,100,10)) " & _
"FROM [Data$] where trim(Partition([id],1,100,10)) in ('1: 10','91:100')"
End With
With Sheet1
.[H2:L100].ClearContents
.[H2].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing

End Sub

[/GPECODE]

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

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

❆❆❆❆❆❆❆❆

File đính kèm

Thành viên gắn bó

❆❆❆❆❆❆❆❆

Thành viên gắn bó

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

Thành viên gắn bó

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

Thành viên gắn bó

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

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

Thành viên gắn bó

❆❆❆❆❆❆❆❆

Huyền thoại GPE

❆❆❆❆❆❆❆❆

Thành viên gắn bó

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

❆❆❆❆❆❆❆❆

Huyền thoại GPE

❆❆❆❆❆❆❆❆

Huyền thoại GPE

❆❆❆❆❆❆❆❆

Huyền thoại GPE

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

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

❆❆❆❆❆❆❆❆

Huyền thoại GPE

❆❆❆❆❆❆❆❆

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

❆❆❆❆❆❆❆❆

Huyền thoại GPE

❆❆❆❆❆❆❆❆

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

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

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

❆❆❆❆❆❆❆❆

File đính kèm

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

❆❆❆❆❆❆❆❆

Thành viên gắn bó

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

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

Thành viên gắn bó

❆❆❆❆❆❆❆❆

Thành viên gắn bó

❆❆❆❆❆❆❆❆

Thành viên gắn bó

❆❆❆❆❆❆❆❆

Thành viên gắn bó

❆❆❆❆❆❆❆❆

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

Thành viên gắn bó

❆❆❆❆❆❆❆❆

Thành viên gắn bó

Thành viên gắn bó

Huyền thoại GPE

Thành viên gắn bó

❆❆❆❆❆❆❆❆

File đính kèm

❆❆❆❆❆❆❆❆

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

Thành viên gắn bó

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

Thành viên gắn bó

❆❆❆❆❆❆❆❆

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

❆❆❆❆❆❆❆❆

Thành viên gắn bó

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

❆❆❆❆❆❆❆❆

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