Bài tập về ADO căn bản.

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

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

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

 

[ndu96081631]

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

[GPECODE=sql]Function GetData(FileName As String, SheetName As String)
If Val(Application.Version) < 12 Then
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"

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

 

[hungpecc1]

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

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

 

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

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

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

 

[hungpecc1]

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

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

 

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

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

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

 

[hungpecc1]

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

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

 

[nmhung49]

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

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

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

Thành

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

Khi đó sub Main

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

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

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

 

[ndu96081631]

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

Thành

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

Khi đó sub Main

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

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

 

[ndu96081631]

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

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

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

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

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

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

 

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

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

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

 

[ndu96081631]

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

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

 

[Hoàng Trọng Nghĩa]

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

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

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

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

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

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

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

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

 

[Hoàng Trọng Nghĩa]

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

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

 

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

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

Vì em thấy

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

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

 

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

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

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

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

 

[ndu96081631]

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

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

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

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

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

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

 

[Hoàng Trọng Nghĩa]

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

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

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

 

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

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

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

 

[ndu96081631]

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

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

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

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

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

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

Lại ghi thành:

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

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