Option Explicit
Public Sub compare_tuhocvba()
'//Khai bao va khoi tao FileSystemObject
Dim fso As New Scripting.FileSystemObject
Dim data As Variant, result As Variant
Dim book_open As Workbook, sheet_open As Worksheet
Dim strPath As String, strFileName As String, strCotF As String
Dim i As Long, r As Long
'// Ten 2 tap tin csv
Const strLayDuLieu As String = "LayDuLieu.csv"
Const strGhiDuLieu As String = "GhiDuLieu.csv"
'// Duong dan thu muc chua tap tin csv
strPath = ThisWorkbook.Path
strFileName = strPath & "\" & strLayDuLieu
'// Kiem tra xem cac tap tin strLayDuLieu co ton tai theo duong dan khong
If fso.FileExists(strFileName) Then
Set book_open = Workbooks.Open(strFileName)
'// CSV file chi co duy nhat 1 sheet
Set sheet_open = book_open.Worksheets(1)
r = sheet_open.Cells(sheet_open.Rows.Count, "M").End(xlUp).Row
'//Gan du lieu "LayDuLieu.csv" vao mang data tu cot F den cot M
data = sheet_open.Range("F1:M" & r).Value
book_open.Close False '// Dong tap tin va khong luu
Else '// Neu khong ton tai
MsgBox "Khong ton tai tap tin nguon.", vbCritical
Exit Sub
End If
strFileName = strPath & "\" & strGhiDuLieu
'// Kiem tra xem cac tap tin strGhiDuLieu co ton tai theo duong dan khong
If fso.FileExists(strFileName) Then
Set book_open = Workbooks.Open(strFileName)
'// CSV file chi co duy nhat 1 sheet
Set sheet_open = book_open.Worksheets(1)
r = sheet_open.Cells(sheet_open.Rows.Count, "F").End(xlUp).Row
'//Gan du lieu "GhiDuLieu.csv" vao mang result tu cot F den cot M
result = sheet_open.Range("F1:M" & r).Value
Else '// Neu khong ton tai
MsgBox "Khong ton tai tap tin nguon.", vbCritical
Exit Sub
End If
'// Khai bao va khoi tao Dictionary
Dim dic As New Scripting.Dictionary
For i = LBound(data, 1) To UBound(data, 1) '// Vong lap 1 voi mang data
strCotF = data(i, 1) '// Duyet cac tu khoa trong cot F trong mang data
If Not dic.Exists(strCotF) Then dic.Add strCotF, i
Next i
For i = LBound(result, 1) To UBound(result, 1)
strCotF = result(i, 1)
If dic.Exists(strCotF) Then
r = dic.Item(strCotF) '// Xac dinh vi tri cua tu khoa trong vong lap 1
'// Gan ket qua vao cot M lan luot theo i voi gia tri tim thay vi tri r
result(i, UBound(result, 2)) = data(r, UBound(data, 2))
End If
Next i
'// Dua ket qua cua mang result xuong bang tinh
sheet_open.Range("F1").Resize(UBound(result, 1), UBound(result, 2)) = result
book_open.Close True '// Dong tap tin va luu lai
'// Ket thuc
MsgBox "Done!", vbInformation + vbOKOnly, "Tu hoc VBA"
End Sub