HOW TO TRACK EXCEL USAGE SHARED IN NETWORK DRIVE

Hello all,

I have an excel file that I shared in a network drive and I want to track the usage of that file like who accesses the file and when do they close it, like a log file. I did try to integrate a macro with the following code:

Option Explicit

Private strOpenClose As String

Private strDate As String

Private strTime As String

Private strUser As String

Private strValues As String

Private Const strlogfile As String = "UNC PATH FOR NETWORK DRIVE\log.xlsx"

Private Sub Workbook_Open()

'worksheets to show when macro is enabled

Sheets("HOME").Visible = True

Sheets("CNC,CNC RECURRING,RETURN,NRC").Visible = True

Sheets("DUPLICATE").Visible = True

Sheets("DSS").Visible = True

Sheets("DAMAGE").Visible = True

Sheets("PDD").Visible = True

Sheets("IRCB-NRCB").Visible = True

'worksheet that shows reminder to enable macro

Sheets("Reminder").Visible = xlVeryHidden

strOpenClose = "Opened" 

strDate = Format(Date, "mm.dd.yyyy") 'set format to taste 

strTime = Format(Time, "HH:MM:SS") 

strUser = Environ("username") 

strValues = strOpenClose & "', '" & strDate & "', '" & strTime & "', '" & strUser 

WriteToWorksheet strWorkbook:=strlogfile, strRange:="Sheet1", strValues:=strValues 

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'worksheet with reminder

Sheets("Reminder").Visible = True

'worksheets to show when macro is enabled

Sheets("HOME").Visible = xlVeryHidden

Sheets("CNC,CNC RECURRING,RETURN,NRC").Visible = xlVeryHidden

Sheets("DUPLICATE").Visible = xlVeryHidden

Sheets("DSS").Visible = xlVeryHidden

Sheets("DAMAGE").Visible = xlVeryHidden

Sheets("PDD").Visible = xlVeryHidden

Sheets("IRCB-NRCB").Visible = xlVeryHidden

ThisWorkbook.Save

strOpenClose = "Closed"

strDate = Format(Date, "mm.dd.yyyy") 

strTime = Format(Time, "HH:MM:SS") 

strUser = Environ("username") 

strValues = strOpenClose & "', '" & strDate & "', '" & strTime & "', '" & strUser 

WriteToWorksheet strWorkbook:=strlogfile, strRange:="Sheet1", strValues:=strValues 

End Sub

Private Function WriteToWorksheet(strWorkbook As String, _

                             strRange As String, \_ 

                             strValues As String) 

Dim ConnectionString As String

Dim strSQL As String

Dim CN As Object

strRange = strRange & "$]" 

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & \_ 

                   "Data Source=" & strWorkbook & ";" & \_ 

                   "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";" 

strSQL = "INSERT INTO [Sheet1$] VALUES('" & strValues & "')" 

Set CN = CreateObject("ADODB.Connection") 

Call CN.Open(ConnectionString) 

Call CN.Execute(strSQL, , 1 Or 128) 

CN.Close 

Set CN = Nothing 

End Function

The above code is to:

1. Force enable Macro which will prompt user to enable macro before they can see the data sheets.
2. Writes the open and closed time and user name of the person who accesses the file.

This code works absolutely fine if I use it, however every time any other user accesses the file from network drive, they get a runtime error "Invalid Path......". Both main excel file and log.xlsx are save in the same folder in network drive.

I need your help in fixing this, it doesn't matter if they save the excel file or not, the main purpose is to track the usage of the excel file.