VBA help

[jtodd]

I have this code that creats a log of when a workbook is opened

Private Sub Workbook_Open()
    Open "\\Dtc06\newheart\Jim Todd\Testing_Recovered\RDC_Excel_testing\UserLogs\testlog.log" For Append As #1
    Print #1, Application.UserName, Now, ThisWorkbook.Name
    Close #1
End Sub

How could I change this to
1. write to an excel file rather than a notebook file.
2. to show open and close times .

Any help would be appreciated .

 

[Yongle]

Writing to a textfile is less intrusive
Here is amended code to do that

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    WriteToFile ("Close")
End Sub

Private Sub Workbook_Open()
    WriteToFile ("Open")
End Sub

Private Sub WriteToFile(Status As String)
Const c = ","
    Open "\\Dtc06\newheart\Jim Todd\Testing_Recovered\RDC_Excel_testing\UserLogs\testlog.log" For Append As #1
    Print #1, Application.UserName & c & Now & c & ThisWorkbook.Name & c & Status
    Close #1
End Sub

The data is now comma delimited
How this next bit works for you may be different - it depends on which version of Excel that you are using
But in Excel365,
Create a new workbook
On Data tab \ Get Data from Text or CSV \ Excel brings in data as a query in a table like this

Book1
	A	B	C	D
1	Column1	Column2	Column3	Column4
2	yongle	15/07/2020 12:18:35	Log to log.xlsm	Close
3	yongle	15/07/2020 12:19:33	Log to log.xlsm	Open
4	yongle	15/07/2020 12:19:36	Log to log.xlsm	Close
5	yongle	15/07/2020 12:22:51	Log to log.xlsm	Open
6	yongle	15/07/2020 12:25:45	Log to log.xlsm	Close
7	yongle	15/07/2020 12:25:52	Log to log.xlsm	Open
8	yongle	15/07/2020 12:25:55	Log to log.xlsm	Close
9	yongle	15/07/2020 12:29:26	Log to log.xlsm	Open
10	yongle	15/07/2020 12:29:30	Log to log.xlsm	Close
Sheet2

Save and close the new workbook
Open up the one being logged and close it again etc (simply to amend the log file)

Open new workbook
Click on Data tab \ Refresh All to refresh the values in the table

Book1
	A	B	C	D
1	Column1	Column2	Column3	Column4
2	yongle	15/07/2020 12:18:35	Log to log.xlsm	Close
3	yongle	15/07/2020 12:19:33	Log to log.xlsm	Open
4	yongle	15/07/2020 12:19:36	Log to log.xlsm	Close
5	yongle	15/07/2020 12:22:51	Log to log.xlsm	Open
6	yongle	15/07/2020 12:25:45	Log to log.xlsm	Close
7	yongle	15/07/2020 12:25:52	Log to log.xlsm	Open
8	yongle	15/07/2020 12:25:55	Log to log.xlsm	Close
9	yongle	15/07/2020 12:29:26	Log to log.xlsm	Open
10	yongle	15/07/2020 12:29:30	Log to log.xlsm	Close
11	yongle	15/07/2020 12:32:34	Log to log.xlsm	Open
12	yongle	15/07/2020 12:39:24	Log to log.xlsm	Close
Sheet2

 

[jtodd]

many thanks , very clear and comprehensive solution, i will try this ASAP and let you know how i get on.