Checking File exists in a specific location

[Ben171]

Hi,

I have created a userform/database in excel.

Part of this database involves generating a form from the data entered, which is printed.

It is then signed and is scanned into a folder. All forms printed should be scanned, therefore I would like to be able to check whether a file exists in the scanned folder or whether it is missing.

In my database sheet called "Database" i have a column called works order number, this is important because the forms that need scanning will be called 'Works order number.pdf" e.g. in the below screenshot, the form for D2 would be 074982.pdf

Therefore technically, every works order number in column D should have a file named after it in the scanned documents folder located at lets say "C:\Database\Scanned Forms\"

I would like a button, which goes through this folder full of scans and check if a file exists for every works order number, and if not then display all the works order numbers that do not have a file named after it in this folder.

e.g. using the above screenshot, if there is no 074979.pdf in the location "C:\Database\Scanned Forms\" then it should be displayed.

Hope that is clear, sorry if not!

Thanks

 

[Anthony47]

In this case:

Sub ScanForm3()
Dim tTable As String, I As Long, mCnt As Long
Dim myPath As String, myF As String, myMsg As String
'
myPath = "C:\Database\Scanned Forms\"
myPath = "C:\Users\among\OneDrive\ARCHIVIO-BOLLETTE\SANDOM\"
'
Sheets("Database").Select
For I = 2 To Cells(Rows.Count, "D").End(xlUp).Row
    myF = Dir(myPath & Cells(I, "D").Value & ".pdf")
    If myF = "" Then
        myMsg = myMsg & Cells(I, "D") & vbCrLf
        mCnt = mCnt + 1
    End If
Next I
If mCnt > 0 Then
    myMsg = "The following files have not yet been scanned: " & vbCrLf & myMsg & vbCrLf _
    & "Please scan them into the correct folder and recheck"
Else
    myMsg = "Check completed, ok"
End If
MsgBox (myMsg)
End Sub

Bye

 

[Ben171]

In this case:

Sub ScanForm3()
Dim tTable As String, I As Long, mCnt As Long
Dim myPath As String, myF As String, myMsg As String
'
myPath = "C:\Database\Scanned Forms\"
myPath = "C:\Users\among\OneDrive\ARCHIVIO-BOLLETTE\SANDOM\"
'
Sheets("Database").Select
For I = 2 To Cells(Rows.Count, "D").End(xlUp).Row
    myF = Dir(myPath & Cells(I, "D").Value & ".pdf")
    If myF = "" Then
        myMsg = myMsg & Cells(I, "D") & vbCrLf
        mCnt = mCnt + 1
    End If
Next I
If mCnt > 0 Then
    myMsg = "The following files have not yet been scanned: " & vbCrLf & myMsg & vbCrLf _
    & "Please scan them into the correct folder and recheck"
Else
    myMsg = "Check completed, ok"
End If
MsgBox (myMsg)
End Sub

Bye

Perfect, this is exactly what i was after.

Thank you so much for your help today!

 

[Anthony47]

Thank you for the feedback

 

[Ben171]

Thank you for the feedback

Sorry to come back to you about this, left it for a few hours now come back to it and i've just realised something.

So i call this Sub when clicking the "Open Form" button as i want this to pop up when they go to open the form to enter another entry.

That works fine, however when this code runs, it takes me to the sheet called "Database" then opens the form. I guess this is because of the line

Sheets("Database").Select

Ideally it would remain on the home sheet called "Home" where the open form button is located instead of switching to the database sheet.

 

[Anthony47]

Here we are with the penultimate version:

Sub ScanForm4()
Dim tTable As String, I As Long, mCnt As Long
Dim myPath As String, myF As String, myMsg As String
'
myPath = "C:\Database\Scanned Forms\"
'
With Sheets("Database")
    For I = 2 To .Cells(Rows.Count, "D").End(xlUp).Row
        myF = Dir(myPath & .Cells(I, "D").Value & ".pdf")
        If myF = "" Then
            myMsg = myMsg & .Cells(I, "D") & vbCrLf
            mCnt = mCnt + 1
        End If
    Next I
End With
If mCnt > 0 Then
    myMsg = "The following files have not yet been scanned: " & vbCrLf & myMsg & vbCrLf _
    & "Please scan them into the correct folder and recheck"
Else
    myMsg = "Check completed, ok"
End If
MsgBox (myMsg)
End Sub

Bye

 

[sofas]

It's nice