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]

For example:

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]

For example:

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]

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
	A	B	C	D
1	C:\Database\Scanned Forms\*.pdf		Files	Remove Filetype
2			074762.pdf	074762
3			074966.pdf	074966
4			074982.pdf	074982
5			074983.pdf	074983
6
7
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IFERROR(INDEX(ListofFiles,ROW(A1)),"")
D2:D7	D2	=SUBSTITUTE(C2,".pdf","")

 

[Ben171]

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
	A	B	C	D
1	C:\Database\Scanned Forms\*.pdf		Files	Remove Filetype
2			074762.pdf	074762
3			074966.pdf	074966
4			074982.pdf	074982
5			074983.pdf	074983
6
7
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IFERROR(INDEX(ListofFiles,ROW(A1)),"")
D2:D7	D2	=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]

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

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

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:

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]

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


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:

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

 

[Anthony47]

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]

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]

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"