Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Log
I have a spreadsheet that demonstrated excess inventory. I need to be able to
keep track of who is using this spreadsheet... so I came up with the idea of capturing some data (date, user, full path, how long they had it open) and writing it to another spreadsheet on a network drive that everyone has access to (P:\) The code (below) works perfectly in my testing... right up until I tested having 2 people open the file at the same time and close the workbook at the same time. When that happened... my ExcessLog.xls file returned an "unable to read file" error. I have not been able to open it since. Does anyone know what this happened? Have any suggestions for making this work? Have a better solution for keeping track of who opens/uses a spreadsheet? Here is the code, which is in the ThisWorkbook object: '** When workbook is opened triggers the start timer. When the workbook is closed it captures the user, path, stop time after validating that the user has access to the P:\ Dim TStart As Long 'Timer - Start Dim TStop As Long 'Timer - Stop Dim MyPath As String 'Full Path of Workbook Dim PW As String 'Sheet Protection Password Dim DV As String 'Dialog Value for MsgBox Dim x As Long Private Sub Workbook_BeforeClose(Cancel As Boolean) PW = "test" If Dir("P:\Permanent_Data\Patrick\Excess Inventory\") < "" Then Application.ScreenUpdating = False MyPath = Application.ActiveWorkbook.FullName ChDir "P:\Temporary_Data_60_Days\Patrick\Logs" Workbooks.Open Filename:="P:\Temporary_Data_60_Days\Patrick\Logs\ ExcessLog.xls" Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW Range("A2").Select x = 2 '** Get to empty row While Trim(ActiveCell.Offset(0, 0).Value) < "" ActiveCell.Offset(1, 0).Select x = x + 1 Wend TStop = Timer Range("A" & x).Value = Date Range("B" & x).Value = MyPath Range("C" & x).Value = Application.UserName Range("D" & x).Value = ((TStop - TStart) / 60) Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW Workbooks("ExcessLog.xls").Save Workbooks("ExcessLog.xls").Close Application.ScreenUpdating = True Else DV = MsgBox("Contact the HelpDesk and request access to the P:\", vbOKOnly, "Slight Problem...") End If End Sub Public Sub Workbook_Open() TStart = Timer End Sub Any assistance/feedback is greatly appreciated! patrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Log
Excel does not deal with concurency at all well. You will have a lot of
difficulty avoiding collisions. Do you have MS Access on your computer. If so Access is ideal for tracking this kind of info without any of the concurrency issues. What you want to do is really pretty easy to impliment with ADO recordsets. Let me know if you want to follow this path... "Patrick" wrote: I have a spreadsheet that demonstrated excess inventory. I need to be able to keep track of who is using this spreadsheet... so I came up with the idea of capturing some data (date, user, full path, how long they had it open) and writing it to another spreadsheet on a network drive that everyone has access to (P:\) The code (below) works perfectly in my testing... right up until I tested having 2 people open the file at the same time and close the workbook at the same time. When that happened... my ExcessLog.xls file returned an "unable to read file" error. I have not been able to open it since. Does anyone know what this happened? Have any suggestions for making this work? Have a better solution for keeping track of who opens/uses a spreadsheet? Here is the code, which is in the ThisWorkbook object: '** When workbook is opened triggers the start timer. When the workbook is closed it captures the user, path, stop time after validating that the user has access to the P:\ Dim TStart As Long 'Timer - Start Dim TStop As Long 'Timer - Stop Dim MyPath As String 'Full Path of Workbook Dim PW As String 'Sheet Protection Password Dim DV As String 'Dialog Value for MsgBox Dim x As Long Private Sub Workbook_BeforeClose(Cancel As Boolean) PW = "test" If Dir("P:\Permanent_Data\Patrick\Excess Inventory\") < "" Then Application.ScreenUpdating = False MyPath = Application.ActiveWorkbook.FullName ChDir "P:\Temporary_Data_60_Days\Patrick\Logs" Workbooks.Open Filename:="P:\Temporary_Data_60_Days\Patrick\Logs\ ExcessLog.xls" Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW Range("A2").Select x = 2 '** Get to empty row While Trim(ActiveCell.Offset(0, 0).Value) < "" ActiveCell.Offset(1, 0).Select x = x + 1 Wend TStop = Timer Range("A" & x).Value = Date Range("B" & x).Value = MyPath Range("C" & x).Value = Application.UserName Range("D" & x).Value = ((TStop - TStart) / 60) Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW Workbooks("ExcessLog.xls").Save Workbooks("ExcessLog.xls").Close Application.ScreenUpdating = True Else DV = MsgBox("Contact the HelpDesk and request access to the P:\", vbOKOnly, "Slight Problem...") End If End Sub Public Sub Workbook_Open() TStart = Timer End Sub Any assistance/feedback is greatly appreciated! patrick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Log
Hi Patrick,
Just check if the file if not open in read-only mode : ..... boFileOpen = False Do Set wbDest = Workbooks.Open("QuestionnaireDB.xls", , , , conPW_DB) If wbDest.ReadOnly = True Then wbDest.Close Application.Wait (Now + TimeValue("00:00:01")) Else: boFileOpen = True End If Loop Until boFileOpen = True ...... Regards, JY "Patrick" wrote in message ... I have a spreadsheet that demonstrated excess inventory. I need to be able to keep track of who is using this spreadsheet... so I came up with the idea of capturing some data (date, user, full path, how long they had it open) and writing it to another spreadsheet on a network drive that everyone has access to (P:\) The code (below) works perfectly in my testing... right up until I tested having 2 people open the file at the same time and close the workbook at the same time. When that happened... my ExcessLog.xls file returned an "unable to read file" error. I have not been able to open it since. Does anyone know what this happened? Have any suggestions for making this work? Have a better solution for keeping track of who opens/uses a spreadsheet? Here is the code, which is in the ThisWorkbook object: '** When workbook is opened triggers the start timer. When the workbook is closed it captures the user, path, stop time after validating that the user has access to the P:\ Dim TStart As Long 'Timer - Start Dim TStop As Long 'Timer - Stop Dim MyPath As String 'Full Path of Workbook Dim PW As String 'Sheet Protection Password Dim DV As String 'Dialog Value for MsgBox Dim x As Long Private Sub Workbook_BeforeClose(Cancel As Boolean) PW = "test" If Dir("P:\Permanent_Data\Patrick\Excess Inventory\") < "" Then Application.ScreenUpdating = False MyPath = Application.ActiveWorkbook.FullName ChDir "P:\Temporary_Data_60_Days\Patrick\Logs" Workbooks.Open Filename:="P:\Temporary_Data_60_Days\Patrick\Logs\ ExcessLog.xls" Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW Range("A2").Select x = 2 '** Get to empty row While Trim(ActiveCell.Offset(0, 0).Value) < "" ActiveCell.Offset(1, 0).Select x = x + 1 Wend TStop = Timer Range("A" & x).Value = Date Range("B" & x).Value = MyPath Range("C" & x).Value = Application.UserName Range("D" & x).Value = ((TStop - TStart) / 60) Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW Workbooks("ExcessLog.xls").Save Workbooks("ExcessLog.xls").Close Application.ScreenUpdating = True Else DV = MsgBox("Contact the HelpDesk and request access to the P:\", vbOKOnly, "Slight Problem...") End If End Sub Public Sub Workbook_Open() TStart = Timer End Sub Any assistance/feedback is greatly appreciated! patrick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Log
Perfect! Thank you JY!
"Jean-Yves" wrote: Hi Patrick, Just check if the file if not open in read-only mode : ...... boFileOpen = False Do Set wbDest = Workbooks.Open("QuestionnaireDB.xls", , , , conPW_DB) If wbDest.ReadOnly = True Then wbDest.Close Application.Wait (Now + TimeValue("00:00:01")) Else: boFileOpen = True End If Loop Until boFileOpen = True ....... Regards, JY |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Log
I do have Access and I am interested. Until this point my company has been
using a different relational database product... but it looks like we are heading toward Access. I should probably learn to use it... and this logging task will force me to get off my rump and finally do it (with some help :) "Jim Thomlinson" wrote: Excel does not deal with concurency at all well. You will have a lot of difficulty avoiding collisions. Do you have MS Access on your computer. If so Access is ideal for tracking this kind of info without any of the concurrency issues. What you want to do is really pretty easy to impliment with ADO recordsets. Let me know if you want to follow this path... "Patrick" wrote: I have a spreadsheet that demonstrated excess inventory. I need to be able to keep track of who is using this spreadsheet... so I came up with the idea of capturing some data (date, user, full path, how long they had it open) and writing it to another spreadsheet on a network drive that everyone has access to (P:\) The code (below) works perfectly in my testing... right up until I tested having 2 people open the file at the same time and close the workbook at the same time. When that happened... my ExcessLog.xls file returned an "unable to read file" error. I have not been able to open it since. Does anyone know what this happened? Have any suggestions for making this work? Have a better solution for keeping track of who opens/uses a spreadsheet? Here is the code, which is in the ThisWorkbook object: '** When workbook is opened triggers the start timer. When the workbook is closed it captures the user, path, stop time after validating that the user has access to the P:\ Dim TStart As Long 'Timer - Start Dim TStop As Long 'Timer - Stop Dim MyPath As String 'Full Path of Workbook Dim PW As String 'Sheet Protection Password Dim DV As String 'Dialog Value for MsgBox Dim x As Long Private Sub Workbook_BeforeClose(Cancel As Boolean) PW = "test" If Dir("P:\Permanent_Data\Patrick\Excess Inventory\") < "" Then Application.ScreenUpdating = False MyPath = Application.ActiveWorkbook.FullName ChDir "P:\Temporary_Data_60_Days\Patrick\Logs" Workbooks.Open Filename:="P:\Temporary_Data_60_Days\Patrick\Logs\ ExcessLog.xls" Workbooks("ExcessLog.xls").Sheets("UserLog").Unpro tect PW Range("A2").Select x = 2 '** Get to empty row While Trim(ActiveCell.Offset(0, 0).Value) < "" ActiveCell.Offset(1, 0).Select x = x + 1 Wend TStop = Timer Range("A" & x).Value = Date Range("B" & x).Value = MyPath Range("C" & x).Value = Application.UserName Range("D" & x).Value = ((TStop - TStart) / 60) Workbooks("ExcessLog.xls").Sheets("UserLog").Prote ct PW Workbooks("ExcessLog.xls").Save Workbooks("ExcessLog.xls").Close Application.ScreenUpdating = True Else DV = MsgBox("Contact the HelpDesk and request access to the P:\", vbOKOnly, "Slight Problem...") End If End Sub Public Sub Workbook_Open() TStart = Timer End Sub Any assistance/feedback is greatly appreciated! patrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a pdf | Excel Discussion (Misc queries) | |||
Really need help creating pop ups | Excel Discussion (Misc queries) | |||
Creating | Excel Discussion (Misc queries) | |||
Creating add-ins | Excel Programming | |||
Help with creating a VBA | Excel Programming |