I'm processing 200 text files in a folder. As I'm processing them I log progress into a log file. When I'm about to read it when it has processed and if any predefined event has happened.
So far I used Open and Close in a
WriteToLog sub. So far, no such problem (2 years).
A problem occurred recently: After an unpredictable number of individual writes I got:
70 - Permission denied error on :
Open sFileFullPath For Append As #filenumber.
As a fatal error, I terminate the app run. - I suspect :
a) request for new write happens before the previous Close is performed
b) antivirus analyzing log file with too many operations
c) bad cluster on a hard disk.
d) some file system error (as
chkdsk did discover some problems, but fixed it).
e) I suspect also
filenumber = FreeFile technique which I changed to recently having an impact on Close (not functioning properly). 2 years with #1, #2, #3, etc. with no such a problem.
It happens even if I check with: If
f_IsFileOpen(sFileFullPath) before trying it to Open for Append.
Any idea how to pinpoint the problem?
Option Explicit Public bDevMode As Boolean '-- Development mode - Y/N= where Log file is Opened/Closed Public sLogPath As String '-- store log path for easy change Public sLogFileName As String Sub ProcessFilesAndLog() Dim sLogFileName As String Dim sLogFileFullPath As String Dim sErrMsg As String Dim sInfo As String '-- ini variables bDevMode = False '-- i.e. in production mode sLogFileName = ActiveSheet.Cells(5, 3) '-- "working.log" sLogPath = ActiveSheet.Cells(6, 3) '-- "c:\temp\" '-- STEP INI0.3.5 - try to Write into the Log file in LogFolder given by Options. sLogFileFullPath = ActiveSheet.Cells(6, 3) '-- "c:\temp\" sInfo = vbCrLf & Now & " --- Processing started" p_WriteToLogINITEST sLogFileFullPath, sInfo, True, sErrMsg sInfo = "" 'Application.Wait (Now + TimeValue("0:00:02")) '--- what to test here ? '-- STEP INI0.4 - when in PRODUCTION mode, Open the log file, only once. - 2019-05-09 If Not bDevMode Then Open sLogFileFullPath For Append As #2 '-- HERE occassionally : 70 - Permission denied ! If sErrMsg <> "" Then Print #2, sErrMsg End If '--- of If not bDevMode sErrMsg = "" '-- analyze files code .... '-- close log file If Not bDevMode Then Close #2 End If MsgBox "Finished!" End Sub
and subs used are:
'-- check if a file is still open Function f_IsFileOpen(filename As String) As Boolean Dim filenum As Integer Dim errnum As Integer On Error Resume Next ' Turn error checking off. filenum = FreeFile() ' Get a free file number. '-- Attempt to open the file and lock it. Err.Clear Open filename For Input Lock Read As #filenum errnum = Err.Number ' Save the error number that occurred. Close filenum ' Close the file. On Error GoTo 0 ' Turn error checking back on. '-- Check to see which error occurred. Select Case errnum '-- 0 - No error occurred - File is NOT open by another user. Case 0 f_IsFileOpen = False '-- 70 - Error number for "Permission Denied." File is already opened by another user. Case 70 f_IsFileOpen = True '-- 53 - doesn't exist Case 53 f_IsFileOpen = False '-- Another error occurred. Case Else f_IsFileOpen = True 'Error errnum End Select End Function Sub p_WriteToLogINITEST(ByRef sLogFileFullPath As String, ByVal sMsg As String, ByVal bAppend As Boolean, ByRef sErrMsg As String) Dim nCycleCnt As Integer nCycleCnt = 1 On Error GoTo FILE_ERR '-- Write ini processing msg to log file If sMsg = "" Then sMsg = vbCrLf & Now & " --- Processing started" If bAppend = True Then Open sLogFileFullPath For Append As #2 Else Open sLogFileFullPath For Output As #2 End If Print #2, sMsg Close #2 '-- Wait, until it's sure that previous log file writing has finished, incl. Close. Wait max 10 seconds. Do While nCycleCnt < 11 '-- 10 trials If f_IsFileOpen(sLogFileFullPath) Then sErrMsg = sErrMsg & String(nCycleCnt, "#") & "," '-- for DEBUG ! only Application.Wait (Now + TimeValue("0:00:01")) Else '-- file IS NOT OPEN, i.e. I can LEAVE this sub ! Exit Do End If nCycleCnt = nCycleCnt + 1 Loop Exit Sub FILE_ERR: sErrMsg = "M1.57: Fatal error: Error writing into the log file: " & sLogFile '-- better error handling - 2019-05-08 On Error Resume Next Close '-- MS HELP: If you omit filenumberlist, all active files opened by the Open statement are closed. - 2019-05-08 On Error GoTo 0 '-- terminate any further error handling here End Sub
I could get error 70 most frequently, when run as the first thing after opening XLSM, with log file not existing yet and IDE closed.