Macro Requirement
The macro requirement is if some one opens specific excel file then we need to get the name of the user, Time of workbook open and Time of workbook closed in to another workbook.
Workbooks Names
1st workbook:- Checking_file.xlsm this is the main workbook if we open and close this workbook then it should be updated in then 2nd workbook. So macro code is written in 1st workbook. The code will run based on open and close events of the 1st workbook.
Click here to download the 1st workbook(Checking_file.xlsm)
2nd workbook:- Tracker File.xlsx in this excel file in which user details, Open time and Close time is updated.
Click here to download the 2nd workbook(Tracker File.xlsx)
VBA Codes
Below is the Excel VBA code to run on open of the 1st Workbook (Checking_file.xlsm)
Private Sub Workbook_Open()
st_tme = Format(Now(), "DD-MM-YYYY HH:MM:SS")
usrID = Application.UserName
trk_filenm = ThisWorkbook.Sheets("Sheet1").Range("B1")
trk_path = ThisWorkbook.Sheets("Sheet1").Range("B2")
trk_path_filenm = trk_path & "\" & trk_filenm
Dim trk_wbk As Workbook
Workbooks.Open (trk_path_filenm)
Workbooks(trk_filenm).Activate
Set trk_wbk = ActiveWorkbook
LR = trk_wbk.Sheets("Sheet1").Range("L1")
trk_wbk.Sheets("Sheet1").Range("A" & LR) = st_tme
trk_wbk.Sheets("Sheet1").Range("B" & LR) = usrID
trk_wbk.Save
trk_wbk.Close
End Sub
Below is the Excel VBA code to run on close of the 1st Workbook(Checking_file.xlsm)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
end_tme = Format(Now(), "DD-MM-YYYY HH:MM:SS")
trk_filenm = ThisWorkbook.Sheets("Sheet1").Range("B1")
trk_path = ThisWorkbook.Sheets("Sheet1").Range("B2")
trk_path_filenm = trk_path & "\" & trk_filenm
Dim trk_wbk As Workbook
Workbooks.Open (trk_path_filenm)
Workbooks(trk_filenm).Activate
Set trk_wbk = ActiveWorkbook
LR = trk_wbk.Sheets("Sheet1").Range("M1")
trk_wbk.Sheets("Sheet1").Range("C" & LR) = end_tme
trk_wbk.Save
trk_wbk.Close
End Sub