Checking File exists in a specific location

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
58
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

1628671290924.png


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

Well-known Member
Joined
Mar 29, 2006
Messages
2,259
In this case:
VBA Code:
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
 
Solution

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
58
In this case:
VBA Code:
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!
 

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
58
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
VBA Code:
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,259
Here we are with the penultimate version:
VBA Code:
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
 

Forum statistics

Threads
1,143,637
Messages
5,719,961
Members
422,251
Latest member
bonebreaker100

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top