Process gets stuck when checking network files

Question

I'm testing to see if a network file exists.

I find that with some files the VBA process stops and opens the debug window without any error, just highlighting a line (which I indicate below). If I press F5 to continue, the process finishes without any problem.

FilePath = "\\network\file.pdf"
If Not Dir(FilePath, vbDirectory) = vbNullString Then

    ' The process gets stuck on the next line
    ' I need to add a delay to prevent it to stop like: Application.Wait Now + #12:00:05 AM#

    Worksheets(valRef).OLEObjects.Add Filename:=FilePath, Link:=False, DisplayAsIcon:=False
Else
    ' Do something else
End If

It seems to happen when files are very big. If I add a wait, then the process is okay, but I'm delaying the whole process significantly when I have to process many files. Is there a way to prevent it from stopping?

Use FSO! Have a look here: https://stackoverflow.com/questions/3031497/how-to-verify-if-file-exist-with-vb-script — Sam, Sep 21 '18 at 15:28

score 1 · Accepted Answer · answered Sep 21 '18 at 19:52

You may find components that you like in the following:

'To check if a particular file exists
'excelFile = False, if it is not an Excel file that is being checked
Public Function isAnExistingFile(ByVal fileNameStr As Variant, Optional ByVal excelFile As Boolean = True) As Boolean
Dim wb As Workbook

isAnExistingFile = True
On Error Resume Next
If Not VarType(fileNameStr) = vbString Then
    isAnExistingFile = False
ElseIf Len(fileNameStr) = 0 Then
    isAnExistingFile = False
ElseIf Len(Dir(fileNameStr)) = 0 Then
    isAnExistingFile = False
ElseIf ((GetAttr(fileNameStr) And vbDirectory) <> vbDirectory) = False Then
    isAnExistingFile = False
Else
    If excelFile Then
        Set wb = Application.Workbooks.Open(Filename:=fileNameStr, UpdateLinks:=0, ReadOnly:=True)
        If wb Is Nothing Then isAnExistingFile = False
        If Not wb Is Nothing Then
            wb.Close False
            Set wb = Nothing
        End If
    End If
End If
Err.Clear: On Error GoTo 0

End Function

Process gets stuck when checking network files

1 Answers1