Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Cannot read file, permission denied.

Hi Experts,

We have a script that is downloading files from FTP server every 15 minutes.

I'm writing a function that will do the following.

Loop thru all files, process them, then delete them.

Part of the function is to see if file contains some data besides of just headers.
this is the code

Function CountOfRecords(sFolderPath As String, sFile As String) As Long
Dim fso
Dim folderPath
Dim file
Dim oFile
Dim fileContent
Dim arr
Dim cnt
Dim recCnt
Set fso = CreateObject("Scripting.FileSystemObject")
'folderPath = "C:\FTP\5-18\31\" 'Change the folder path as required and remember to add a back slash in the end
        Set oFile = fso.OpenTextFile(sFolderPath & sFile)
        fileContent = oFile.ReadAll
        arr = Split(fileContent, vbCrLf)
        If UBound(arr) > 0 Then
            cnt = cnt + 1
            recCnt = recCnt + UBound(arr)
        End If
        oFile.Close
        Set oFile = Nothing
CountOfRecords = recCnt

End Function

Open in new window

However some times I get an error, permission denied.

guess when its still in middle the download process...

what is the solution?

Thanks on advance.
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

I suppose step one would be to verify that the underlying issue really is that it is still in progress.
If you wait for a few seconds and retry, does the issue go away? If so you could build in a re-try functionality, e.g. wait X seconds try again, and if you've tried more than Y times, ask user if we wants to exit, so you don't get the user stuck in infinite wait mode.
Avatar of bfuchs

ASKER

Hi,
If you wait for a few seconds and retry, does the issue go away?
Yes this is the case.
ask user if we wants to exit, so you don't get the user stuck in infinite wait mode.
No user involved, I need this to run indefinitely.
How would you modify this code to accomodate this suggestion?
     sDir = "H:\FTP\test\"
    'StrFile = Dir(sDir & "*PatChanges*")
    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0
        If InStr(1, StrFile, "Full") = 0 Then
            If CountOfRecords(sDir, StrFile) > 1 Then
                'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                If InStr(1, StrFile, "PatChanges") > 0 Then
                    l = ProcessFile("Patients", sDir & StrFile)
                End If
                If l > 0 Then
                    s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & sTable & "','" & StrFile & "'," & l & ",'Success') "
                Else
                    s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & sTable & "','" & StrFile & "'," & l & ",'Failure') "
                End If
                db.Execute s
            End If
        End If
        On Error Resume Next
        Kill sDir & StrFile
        StrFile = Dir
        On Error GoTo 0
    Loop

Open in new window


Thanks,
Ben
Avatar of bfuchs

ASKER

Should I check for the following before continuing the loop?
https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open
Thanks,
Ben
Avatar of bfuchs

ASKER

I added the following
        Do While IsFileOpen(sDir & StrFile)
            ' do nothing
        Loop

Open in new window

Function IsFileOpen(filename As String)
    Dim filenum As Integer, 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.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Open in new window

Wondering if this is the right way to handle this?

Thanks,
Ben
No, because it will wait for the open file to close. It will be much more efficient to ignore it and proceed with other files ready to be processed.

In the next run, the locked file may be ready. If not, in the next run, etc.
Yes, that could be one way of doing it. There might be smarter ways, as Gustav mentions, that way should work well enough.
Actually it's worse than you think; during a download, a FTP process will not lock a file, so you can get all kinds of weird results.   Anything from "permission denied" to blank files, or partially filled ones.

Are you in control of the FTP process?   In other words are you pulling these files from someplace, or is someone pushing them to you?  Assuming it's the later.   If so, do you have control over that process?

Typically one uses a "flag" to indicate that the download/upload is complete.   A couple different ways that might happen:

1. File extension is set to something like   ".sending", and then renamed to a valid extension when the transfer is complete.

2. A "guard" file is used.  This is a variation of #1.   If the the file is being sent is abc.txt,   a guard file is first created called abc.locked.  The file is then transmitted and when complete, the .locked file is deleted.    As long as the guard file exists, you don't touch the file.

3. Different folders are used  - you send to something like \myFolder\inbox and then move the file to \myFolder\Unprocessed when the transfer is complete (you do a remote rename).   The receiver then only looks for files in the \unprocessed folder knowing that any file there is complete.

 In some way, you need a semaphore (a flag) to indicate when you can start working with the file.

Jim.
Avatar of bfuchs

ASKER

It will be much more efficient to ignore it and proceed with other files ready to be processed.
So I changed the loop for the following
    Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)

Open in new window

How does that sounds?

@Jim,
Actually the FTP process does name that file differently while its in process, therefore I suspect the logic that I have on script to copy from the download folder to the processing folder is the culprit.

Here is the script,
https://www.experts-exchange.com/questions/29112971/Script-causing-an-error-after-running-for-long-time.html?anchor=a42664409¬ificationFollowed=212064540&anchorAnswerId=42664409#a42664409
Wonder how easy would be to modify that, in order to fix this issue...

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Thanks to all experts.