Option Explicit
Dim dicOpenBook As New Dictionary
Public Function SheetExists(ByVal book As Workbook, ByVal sheetName As String) As Boolean
Dim sht As Worksheet
SheetExists = False
For Each sht In book.Worksheets
If sheetName = sht.Name Then
SheetExists = True
Exit Function
End If
Next
End Function
Public Function BookExists(ByVal bookName As String) As Boolean
Dim book As Workbook
BookExists = False
For Each book In Workbooks
If bookName = book.Name Then
BookExists = True
Exit Function
End If
Next
End Function
Public Function OpenSheet(ByVal book As Workbook, ByVal sheetName As String) As Worksheet
Set OpenSheet = Nothing
If SheetExists(book, sheetName) Then
Set OpenSheet = book.Worksheets(sheetName)
End If
End Function
Public Sub CloseBookIfOpenByMe(book As Workbook, Optional saveMe As Boolean = False)
If dicOpenBook.Exists(book.Name) Then
Exit Sub
End If
CloseBook book, saveMe
End Sub
Public Sub CloseBook(book As Workbook, Optional saveMe As Boolean = False)
Application.DisplayAlerts = False
If saveMe Then
book.Save
End If
book.Close
Set book = Nothing
Application.DisplayAlerts = True
End Sub
Public Function OpenBook(ByVal sPath As String, ByVal bookName As String) As Workbook
Set OpenBook = Nothing
If BookExists(bookName) Then
Set OpenBook = Workbooks(bookName)
If Not dicOpenBook.Exists(bookName) Then dicOpenBook.Add bookName, True
Exit Function
End If
If dicOpenBook.Exists(bookName) Then dicOpenBook.Remove bookName
On Error GoTo Err_
Set OpenBook = Workbooks.Open(sPath & "\" & bookName)
Err_:
End Function
Public Sub Run()
Dim opBook As Workbook, book As Workbook
Dim opSht As Worksheet, sheet As Worksheet
Dim data As Variant
Dim sPath As String, bookName As String
Dim r As Long, i As Long
Dim answer As Integer
Dim datExists As Boolean
Application.ScreenUpdating = False
Set book = ThisWorkbook
sPath = book.Path ' Thu muc duong dan file nguon
bookName = "Data.xlsx" ' Ten file nguon
Set opBook = OpenBook(sPath, bookName)
If opBook Is Nothing Then
MsgBox "Thong tin file nguon khong hop le", vbCritical + vbOKOnly
Exit Sub
End If
Set opSht = OpenSheet(opBook, "Data")
If opSht Is Nothing Then
MsgBox "Thong tin sheet nguon khong hop le", vbCritical + vbOKOnly
GoTo End_
End If
book.Activate
Set sheet = book.ActiveSheet
data = opSht.Range("A1").CurrentRegion.Value
r = UBound(data, 1) + 1
For i = LBound(data, 1) + 1 To UBound(data, 1)
If data(i, 4) = sheet.Range("C4") And _
data(i, 5) = sheet.Range("C5") Then
r = i: datExists = True
Exit For
End If
Next i
If datExists Then
answer = MsgBox("Du lieu da ton tai, ban co muon thay doi ?", vbYesNo + vbQuestion)
If answer <> vbYes Then GoTo End_
End If
opSht.Cells(r, 1) = sheet.Range("C1")
opSht.Cells(r, 2) = sheet.Range("C2")
opSht.Cells(r, 3) = sheet.Range("C3")
opSht.Cells(r, 4) = sheet.Range("C4")
opSht.Cells(r, 5) = sheet.Range("C5")
opSht.Cells(r, 6) = sheet.Range("C6")
opSht.Cells(r, 7) = sheet.Range("C7")
Application.ScreenUpdating = True
CloseBookIfOpenByMe opBook, True
MsgBox "Da xong!", vbInformation + vbOKOnly
Exit Sub
End_:
Application.ScreenUpdating = True
CloseBookIfOpenByMe opBook, True
End Sub
