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

[nhanvvGPE]

Hi Anh Hungpecc1!

Mình có cách nào duyệt qua file csv và cop chỉ range B19:B26 không ạ?
Vì em chỉ cần dùng vùng dữ liệu này thôi, nếu cop cả sheet thì em cần phải lưu ở sheet 2 sau đó lọc lấy range B19:B26 đưa qua Sheet1. Như vậy tốc độ sẽ chậm hơn.

Và cho em hỏi thêm là có cách nào để duyệt từ hàng thứ 8 chẳng hạn trong file csv không tương tự như mình duyệt theo fields của Recordset ấy anh?

Mong anh giúp đỡ.
Bestregard!
Nhân.

 

[nhanvvGPE]

Hi Anh!

Nó báo lỗi Can't find project ỏ library của hàm TransArr().

Rgs!

 

[hungpecc1]

Hi Anh Hungpecc1!

Mình có cách nào duyệt qua file csv và cop chỉ range B19:B26 không ạ?
Vì em chỉ cần dùng vùng dữ liệu này thôi, nếu cop cả sheet thì em cần phải lưu ở sheet 2 sau đó lọc lấy range B19:B26 đưa qua Sheet1. Như vậy tốc độ sẽ chậm hơn.

Và cho em hỏi thêm là có cách nào để duyệt từ hàng thứ 8 chẳng hạn trong file csv không tương tự như mình duyệt theo fields của Recordset ấy anh?

Mong anh giúp đỡ.
Bestregard!
Nhân.

Tổng quát nhất bạn đưa toàn bộ recordset vào mảng rồi xử lý trong mảng,:
Thích copy từ dòng nào, duyệt từ dòng nào cũng được tuốt !

 

[nhanvvGPE]

Đã lấy được dữ liệu theo vùng mong muốn trong csv file!

Hi A Hungpecc1!

Em đã lấy được dữ liệu theo vùng mong muốn dùng Getrows như code bên dưới, nếu cần lấy nhiều dữ liệu em sẽ quét luôn theo field:

Sub GPE()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Dim arrayRows As Variant
Dim x As Integer
With cnn
.CursorLocation = 3
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
End With
rst.Open "SELECT * FROM 001.csv", cnn, 3, 3, adCmdText
arrayRows = rst.GetRows(26)
For x = 1 To 8
Cells(x, 10) = arrayRows(1, x + 16)
Next x
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Cảm ơn anh rất nhiều!
Giờ em phải nghiên cứu làm sao lưu range nào đó vào file xlsx đang đóng.

Thanks
Nhân

 

[hungpecc1]

Hi A Hungpecc1!

Em đã lấy được dữ liệu theo vùng mong muốn dùng Getrows như code bên dưới, nếu cần lấy nhiều dữ liệu em sẽ quét luôn theo field:

Cảm ơn anh rất nhiều!
Giờ em phải nghiên cứu làm sao lưu range nào đó vào file xlsx đang đóng.

Thanks
Nhân

Làm gì mà " phức tạp " dữ vậy :
thử thay bằng code sau xem :
[GPECODE=vb]
Sub GPE1()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset, lzSQL As String


Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
With cnn
.CursorLocation = 3
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & ";" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited"""
End With
lzSQL = "SELECT * FROM 001.csv WHERE Isnumeric(f1)"
rst.Open lzSQL, cnn, 3, 3, 1
Range("A1").CopyFromRecordset rst
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]

Muốn ghi vào phải đóng thì vọc thêm câu lệnh SQL INSERT hoặc UPDATE ( có thể kết hợp thêm các phép nối bảng )

 

[nhanvvGPE]

Làm gì mà " phức tạp " dữ vậy :
thử thay bằng code sau xem :
[GPECODE=vb]
Sub GPE1()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset, lzSQL As String


Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
With cnn
.CursorLocation = 3
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & ";" & _
"Extended Properties=""text;HDR=NO;FMT=Delimited"""
End With
lzSQL = "SELECT * FROM 001.csv WHERE Isnumeric(f1)"
rst.Open lzSQL, cnn, 3, 3, 1
Range("A1").CopyFromRecordset rst
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]

Muốn ghi vào phải đóng thì vọc thêm câu lệnh SQL INSERT hoặc UPDATE ( có thể kết hợp thêm các phép nối bảng )

Hi Anh,

Em thử với cách trên tuy nhiên câu lệnh nạy copy cả hàng khi cột A có giá trị số, nhưng em chỉ cần copy ở cột B thôi.
Anh có tài liệu giải thích các tham số trong các câu lệnh như select, update, insert thì cho em xin với. Vì em chưa hiểu cấu trúc các lệnh trên nên không biết thử sao cả.
Nếu anh có ví dụ về copy range (có thể là 1 cell hay một range bất kì) vào file xlsx thì cho em xin tham khảo với.

Thanks
Nhan

 

[nhanvvGPE]

Copy range B10:B20 từ workbk xlsm sang range B10:B20 của file xlsx đang đóng use ADO.

Hi Mọi Người!

Em sưu tầm module bên dưới đề copy dữ liệu từ 1 workbook đang đóng sang 1 workbook đang mở. Moi người giúp em chuyển đổi để em copy được 1 range từ file đang mở sang đang đóng nhé!

Public Sub GetDataADO(SourceFile As Variant, SourceSheet As String, SourceRange As String, desRange As Range)
Dim rsCon As Object: Dim rsData As Object: Dim szConnect As String: Dim szSQL As String
' Create the connection string
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
On Error GoTo SomethingWrong
Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
rsCon.Open szConnect: rsData.Open szSQL, rsCon, 0, 1, 1
' Check to make sure we received data and copy the data
If Not rsData.EOF Then
desRange.CopyFromRecordset rsData
Else: MsgBox "No records returned from : " & SourceFile, vbCritical
End If
' Clean up our Recordset object.
rsData.Close: Set rsData = Nothing
rsCon.Close: Set rsCon = Nothing
Exit Sub
SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, vbExclamation, "Error"
On Error GoTo 0
End Sub

Hi Anh Hungpecc1!

Khi sáng anh giúp em chỗ file csv có dạng 001.csv, tuy nhiên khi em chuyển file thành 001.dat.csv thì lại báo lỗi.
Có cách nào khắc phục khi vẫn để là 001.dat.csv không anh? Vì công ty em xuất dữ liệu từ hệ thống ra đã theo định dạng này!

Thanks
BestRegards

 

[hungpecc1]

Hi Mọi Người!

Em sưu tầm module bên dưới đề copy dữ liệu từ 1 workbook đang đóng sang 1 workbook đang mở. Moi người giúp em chuyển đổi để em copy được 1 range từ file đang mở sang đang đóng nhé!

Hi Anh Hungpecc1!

Khi sáng anh giúp em chỗ file csv có dạng 001.csv, tuy nhiên khi em chuyển file thành 001.dat.csv thì lại báo lỗi.
Có cách nào khắc phục khi vẫn để là 001.dat.csv không anh? Vì công ty em xuất dữ liệu từ hệ thống ra đã theo định dạng này!

Thanks
BestRegards

Thay vì bạn cóp nhặt các đoạn code để ghi dữ liệu vào file xls đóng, --> bạn đọc các bài tập từ page1 của topic này ,ngâm cứu 1 tẹo là có code ngay!
nếu file có định dạng .dat.csv --> thì dùng FilesystemOject để rename nó thành đuôi .csv thôi !
ví dụ :

set fso = CreateObject("Scripting.FileSystemObject") 
    set file = fso.GetFile(thisworkbook.path & "\001.dat.csv") 
    file.name = "001.csv" 
    set file = nothing 
    set fso = nothing

hoặc :

set fso = CreateObject("Scripting.FileSystemObject") 
    fso.MoveFile thisworkbook.path & "\001.dat.csv", "thisworkbook.path & "\001.csv"" 
    set fso = Nothing

Sub DOITEN()   
 Filename = ThisWorkbook.Path & "\001.dat.csv"   
 newfilename = ThisWorkbook.Path & "\001" & skill & ".csv"   
 Name Filename As newfilename
End Sub

 

[dhn46]

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

Nhờ các anh chị giúp đỡ dhn46 đã biết cách giải quyết vấn đề đặt ra, chỉ dùng 1 câu truy vấn duy nhất. dhn46 mượn Code anh HLMT để sửa theo hướng 1 truy vấn.

Sub GopSheet()
    Dim cn As Object, rst As Object, cat As Object, tbl As Object, str$, str1 As String, i As Integer
    Set cn = CreateObject("ADODB.Connection")
    Set cat = CreateObject("ADOX.Catalog")
    Set tbl = CreateObject("ADOX.Table")
    Set rst = CreateObject("ADODB.Recordset")
    With cn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & ThisWorkbook.FullName & _
                ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
        .Open
    End With
    cat.ActiveConnection = cn
    For Each tbl In cat.Tables
        If Right(Replace(tbl.Name, "'", ""), 1) = "$" Then
            str = str & " union all SELECT * from [" & _
                    Replace(Replace(tbl.Name, "$", ""), "'", "") & "$A1:A1000] where ma is not null"
            str1 = Right(str, Len(str) - 10)
        End If
    Next
    
    str1 = "Select Tb2.Dem From [sheet1$A1:A1000] as Tb1 " & _
            "Inner Join " & _
            "(select T2.ma as Ma1, count(T2.ma) as Dem from (" & str1 & ") AS T2 group by T2.ma) AS TB2 " & _
            "On Tb1.ma=Tb2.ma1"
            
    With rst
        .ActiveConnection = cn
        .Open str1
    End With
    
    [D2:D65536].ClearContents
    Range("B2").CopyFromRecordset rst
    rst.Close: Set rst = Nothing
    cn.Close: Set cn = Nothing
    Set cat = Nothing: Set tbl = Nothing
End Sub

Qua ví dụ này dhn46 rút ra kinh nghiệm: khi viết sql thì có thể chia sql thành các truy vấn nhỏ rồi ghép nối. Việc định danh bảng, cột rất quan trọng trong quá trình viết câu lệnh.

 

[xì tin]

Nhờ các anh chị giúp đỡ dhn46 đã biết cách giải quyết vấn đề đặt ra, chỉ dùng 1 câu truy vấn duy nhất. dhn46 mượn Code anh HLMT để sửa theo hướng 1 truy vấn.

Sub GopSheet()
    Dim cn As Object, rst As Object, cat As Object, tbl As Object, str$, str1 As String, i As Integer
    Set cn = CreateObject("ADODB.Connection")
    Set cat = CreateObject("ADOX.Catalog")
    Set tbl = CreateObject("ADOX.Table")
    Set rst = CreateObject("ADODB.Recordset")
    With cn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & ThisWorkbook.FullName & _
                ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
        .Open
    End With
    cat.ActiveConnection = cn
    For Each tbl In cat.Tables
        If Right(Replace(tbl.Name, "'", ""), 1) = "$" Then
            str = str & " union all SELECT * from [" & _
                    Replace(Replace(tbl.Name, "$", ""), "'", "") & "$A1:A1000] where ma is not null"
            str1 = Right(str, Len(str) - 10)
        End If
    Next
    
    str1 = "Select Tb2.Dem From [sheet1$A1:A1000] as Tb1 " & _
            "Inner Join " & _
            "(select T2.ma as Ma1, count(T2.ma) as Dem from (" & str1 & ") AS T2 group by T2.ma) AS TB2 " & _
            "On Tb1.ma=Tb2.ma1"
            
    With rst
        .ActiveConnection = cn
        .Open str1
    End With
    
    [D2:D65536].ClearContents
    Range("B2").CopyFromRecordset rst
    rst.Close: Set rst = Nothing
    cn.Close: Set cn = Nothing
    Set cat = Nothing: Set tbl = Nothing
End Sub

Qua ví dụ này dhn46 rút ra kinh nghiệm: khi viết sql thì có thể chia sql thành các truy vấn nhỏ rồi ghép nối. Việc định danh bảng, cột rất quan trọng trong quá trình viết câu lệnh.

Không đúng yêu cầu ban đầu bạn đặt ra, đây chỉ select và đếm bình thường, hên là nó trùng vào mã trường hợp không khớp mã thì sao bạn? Ví dụ bạn xóa mã số, nó vẫn ra số lượng cột kế bên.

 

[dhn46]

Không đúng yêu cầu ban đầu bạn đặt ra, đây chỉ select và đếm bình thường, hên là nó trùng vào mã trường hợp không khớp mã thì sao bạn? Ví dụ bạn xóa mã số, nó vẫn ra số lượng cột kế bên.

Về cơ bản thì Code đúng, lúc test Code mình cho vào cột D nên bạn xóa cột C trước khi chạy Code sẽ không xảy ra hiện tượng bạn nêu. Hoặc bạn có thể sửa 1 chút trong Code (cái này đơn giản), bạn sửa đoạn

[D2:D65536].ClearContents

thành

[C2:C65536].ClearContents

 

[xì tin]

Về cơ bản thì Code đúng, lúc test Code mình cho vào cột D nên bạn xóa cột C trước khi chạy Code sẽ không xảy ra hiện tượng bạn nêu. Hoặc bạn có thể sửa 1 chút trong Code (cái này đơn giản), bạn sửa đoạn

[D2:D65536].ClearContents

thành

[C2:C65536].ClearContents

Vẫn chưa ổn lắm bạn à, bạn thử xóa vài mã trong ở cột A rồi chạy code trên thử nhé.

 

[dhn46]

Vẫn chưa ổn lắm bạn à, bạn thử xóa vài mã trong ở cột A rồi chạy code trên thử nhé.

Cảm ơn bạn quan tâm, chắc vội nên mình không để ý, xóa dữ liệu cột B chứ không phải cột C. tức là:

[B2:B65536].ClearContents

Mình nghĩ nếu ai biết ít nhiều về Code cũng sẽ biết cách sửa vấn đề này. Mình Post lại File.

 

[xì tin]

Cảm ơn bạn quan tâm, chắc vội nên mình không để ý, xóa dữ liệu cột B chứ không phải cột C. tức là:

[B2:B65536].ClearContents

Mình nghĩ nếu ai biết ít nhiều về Code cũng sẽ biết cách sửa vấn đề này. Mình Post lại File.

Cái gì vậy bạn? ý mình là khi bạn xóa 1 vài mã ở cột A đi và chạy code nó vẫn ra kết quả kế bên, không thể gán ghép kiểu này được, rất nguy hiểm, bởi vì nó không có 1 mối quan hệ nào với cột A, có thể gán sai dữ liệu.

 

[xì tin]

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

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

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

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

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

phải viết là

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

Hãy làm thử ở excel để mọi người cùng học hỏi.

 

[VetMini]

Hãy làm thử ở excel để mọi người cùng học hỏi.

Trong mục này tôi chỉ thích đóng góp kinh nghiệm về SQL chứ không phải Excel. Nói cách khác, tôi chỉ đưa ra chiều hướng, còn code thực tiễn thì khi nào xét cần thiết tôi mới làm.
Tôi làm việc với Excel chủ yếu chuyên về thiết kế chứ không chuyên về các kỹ thuật code kiếc.

 

[xì tin]

Trong mục này tôi chỉ thích đóng góp kinh nghiệm về SQL chứ không phải Excel. Nói cách khác, tôi chỉ đưa ra chiều hướng, còn code thực tiễn thì khi nào xét cần thiết tôi mới làm.
Tôi làm việc với Excel chủ yếu chuyên về thiết kế chứ không chuyên về các kỹ thuật code kiếc.

Vậy ở đây người ta đang vướng về excel bạn nói bâng quơ ở đâu ai mà áp dụng cho được. Bạn hãy áp dụng update trong trường hợp này mà bạn nói ở Excel thử nhé.

 

[VetMini]

Cảm ơn bạn quan tâm, chắc vội nên mình không để ý, xóa dữ liệu cột B chứ không phải cột C. tức là:

[B2:B65536].ClearContents

Mình nghĩ nếu ai biết ít nhiều về Code cũng sẽ biết cách sửa vấn đề này. Mình Post lại File.

Bạn không cần phải xoá cột.
Lúc lập danh sách UNION aLL thì bạn đưa cả sheet1 vào.
Lúc tính số đếm (group by + count) thì bạn dùng count(*) - 1 để trừ số của sheet1 ra.

Như vậy tất cả các dòng ở sheet1 nhưng không có trong các sheet kia sẽ có trị dem=0

 

[xì tin]

Bạn không cần phải xoá cột.
Lúc lập danh sách UNION aLL thì bạn đưa cả sheet1 vào.
Lúc tính số đếm (group by + count) thì bạn dùng count(*) - 1 để trừ số của sheet1 ra.

Như vậy tất cả các dòng ở sheet1 nhưng không có trong các sheet kia sẽ có trị dem=0

Yêu cầu của người ta là đếm luôn shéet1 mà bạn.

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

 

[VetMini]

... Qua ví dụ này dhn46 rút ra kinh nghiệm: khi viết sql thì có thể chia sql thành các truy vấn nhỏ rồi ghép nối. Việc định danh bảng, cột rất quan trọng trong quá trình viết câu lệnh.

Căn bản của SQL và CSDL LH chỉ có thế thôi. Biết cách chia nhỏ yêu cầu ra thành từng truy vấn con rồi ráp lại là đã nắm vững được cơ cấu của SQL rồi.
Phần kế tiếp bạn cần học là làm thế nào để nhìn vào yêu cầu mà biết cách định dạng bảng. Trong lý thuyết CSDL LH có các dạng chuẩn, nếu có thì giờ thì cũng nên tìm hiểu.

Xin lỗi chủ thớt nói chuyện ngoài lề ADO một chút.