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.

0 Answers