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