Checking File exists in a specific location

Ben171

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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,268
For example:
VBA Code:
Sub ScanForm()
Dim tTable As String, I As Long
Dim myPath As String, myF As String
'
tTable = "Sheet2"                       '<<< The sheet used for listing the missing codes
'
myPath = "C:\Database\Scanned Forms\"
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
        Sheets(tTable).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = Cells(I, "D")
    End If
Next I
End Sub
Copy the code in a standard module of your vba project; the line marked <<< need to be compiled with the name of a workshet that will be used to list the missing forms

Bye
 

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
66
For example:
VBA Code:
Sub ScanForm()
Dim tTable As String, I As Long
Dim myPath As String, myF As String
'
tTable = "Sheet2"                       '<<< The sheet used for listing the missing codes
'
myPath = "C:\Database\Scanned Forms\"
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
        Sheets(tTable).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = Cells(I, "D")
    End If
Next I
End Sub
Copy the code in a standard module of your vba project; the line marked <<< need to be compiled with the name of a workshet that will be used to list the missing forms

Bye

oh wow that was easier than expected, thank you that works great!

Just a thought though, would i be able to display this information in a popup message box, instead of on a sheet?

Therefore i can make this pop up when opening the database, to make them aware if there is one that hasn't yet been scanned.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,200
Office Version
  1. 2016
Platform
  1. Windows
Hi Ben171,

Let me give you a different approach. This will list all pdf filenames in the specified directory so you can use column D to check if your file exists.

Ben171.xlsm
ABCD
1C:\Database\Scanned Forms\*.pdfFilesRemove Filetype
2074762.pdf074762
3074966.pdf074966
4074982.pdf074982
5074983.pdf074983
6  
7  
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=IFERROR(INDEX(ListofFiles,ROW(A1)),"")
D2:D7D2=SUBSTITUTE(C2,".pdf","")
 

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
66

ADVERTISEMENT

Hi Ben171,

Let me give you a different approach. This will list all pdf filenames in the specified directory so you can use column D to check if your file exists.

Ben171.xlsm
ABCD
1C:\Database\Scanned Forms\*.pdfFilesRemove Filetype
2074762.pdf074762
3074966.pdf074966
4074982.pdf074982
5074983.pdf074983
6  
7  
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=IFERROR(INDEX(ListofFiles,ROW(A1)),"")
D2:D7D2=SUBSTITUTE(C2,".pdf","")
Hi Toadstool,

Thanks for showing me a difference approach. this does look great. Thank you!
However what i forgot to mention in my post (which i mentioned in above comment) is that it's quite urgent that these forms are scanned in.

Therefore i think it needs an approach such as a pop up box when the database is opened, which users cannot avoid and it tells them which forms are yet to be scanned.
with an approach like the above, although it is great, users can just forget/avoid to use this
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,268
oh wow that was easier than expected, thank you that works great!
No no, it was very difficult but I managed in doing that :biggrin: :biggrin:

i think it needs an approach such as a pop up box when the database is opened
This version will scan the files, list the missing ones on the selected worksheet (as before) and at the end give a report to the user:
-check is ok; OR
-there are N missing files, scan them and retest, with the list shown

The new code:
VBA Code:
Sub ScanForm2()
Dim tTable As String, I As Long, mCnt As Long
Dim myPath As String, myF As String, myMsg As String
'
tTable = "Sheet2"                       '<<< The sheet used for listing the missing codes
'
myPath = "C:\Database\Scanned Forms\"
Sheets(tTable).Range("A:B").Clear
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
        Sheets(tTable).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = Cells(I, "D")
        mCnt = mCnt + 1
    End If
Next I
If mCnt > 0 Then
    myMsg = "There are " & mCnt & " MISSING FORMS" & vbCrLf _
    & "SCAN THEM and recheck"
    Sheets(tTable).Range("A1").Value = "MISSING FILES"
    Sheets(tTable).Select : Range("A1").Select
Else
    myMsg = "Check completed, ok"
End If
MsgBox (myMsg)
End Sub
Beware that the indicated worksheet will be cleared in column A:B when the macro starts

Bye
 

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
66

ADVERTISEMENT

No no, it was very difficult but I managed in doing that :biggrin: :biggrin:


This version will scan the files, list the missing ones on the selected worksheet (as before) and at the end give a report to the user:
-check is ok; OR
-there are N missing files, scan them and retest, with the list shown

The new code:
VBA Code:
Sub ScanForm2()
Dim tTable As String, I As Long, mCnt As Long
Dim myPath As String, myF As String, myMsg As String
'
tTable = "Sheet2"                       '<<< The sheet used for listing the missing codes
'
myPath = "C:\Database\Scanned Forms\"
Sheets(tTable).Range("A:B").Clear
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
        Sheets(tTable).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = Cells(I, "D")
        mCnt = mCnt + 1
    End If
Next I
If mCnt > 0 Then
    myMsg = "There are " & mCnt & " MISSING FORMS" & vbCrLf _
    & "SCAN THEM and recheck"
    Sheets(tTable).Range("A1").Value = "MISSING FILES"
    Sheets(tTable).Select : Range("A1").Select
Else
    myMsg = "Check completed, ok"
End If
MsgBox (myMsg)
End Sub
Beware that the indicated worksheet will be cleared in column A:B when the macro starts

Bye
ahh that's better! I can see this one looks more complicated lol

One more question though (sorry!)

Is it possible to just display the ones that need scanning inside the messagebox, instead of it taking you to a sheet and listing them there?

e.g. messagebox displays "The following forms need scanning..." then lists the missing files
 
Last edited:

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,268
And what if there is more than one?
If I had to do the job I would prefer to have a complete list of what I have to do, rather than starting with one; then recheck and discover there is a second one; then recheck and discover ... (and so on)
 

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
66
And what if there is more than one?
If I had to do the job I would prefer to have a complete list of what I have to do, rather than starting with one; then recheck and discover there is a second one; then recheck and discover ... (and so on)

Sorry i did not mean only display one,
I mean display all the missing files on the list, just on the msgbox instead of taking the user to a different sheet. - if its possible to do of course.
This is because i am implementing this to happen upon opening the form. Therefore when a user is going to put in a new entry the msgbox will popup telling them they should scan the missing files first.

If not then the current method will suffice as it is still a good method.
 

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
66
Sorry i did not mean only display one,
I mean display all the missing files on the list, just on the msgbox instead of taking the user to a different sheet. - if its possible to do of course.
This is because i am implementing this to happen upon opening the form. Therefore when a user is going to put in a new entry the msgbox will popup telling them they should scan the missing files first.

If not then the current method will suffice as it is still a good method.

e.g. msgbox would display:

"The following files have not yet been scanned...

075346
075162
075296

Please scan them into the correct folder"
 

Forum statistics

Threads
1,148,369
Messages
5,746,295
Members
424,006
Latest member
Metal_warrior

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