Macro to check if a list of files exist and mark then in a document?

[tonywatsonhelp]

Hi Everyone,

Hope this makes sense.

Every week I create lots of documents for the last week, things like Wages, Sales etc there is about 20 different docs in total.

On a Monday I run a very detailed macro that goes into lastweeks documents and and creates a head office dashboard for me using some data from all 20 docs.

the only problem I have with this is I sometimes forget to do one or more of the jobs in hand so effectively some of the files I need are missing.

The easiest way to deal with this in my current set up would be to check if each file exists in the file location I have for it. so this is what I want

So as shown below but for 20 rows, A is the File location, each one is different, B is the file name (if I have to set these two columns up differently that will be fine.
C tells me if the file was found or not.

if anyone could help me do this that would be great..

Tony

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

File Location

File Name

Does File Exist?

2

"S:\Docs\ New Documents Folder\New Wages London.xlsm"<strike></strike>

New Wages London<strike></strike>

Not Found!

3

"S:\Docs\ Old Documents Folder\New Sales1 London.xlsm"<strike></strike>

New Sales1 London<strike></strike>

Found!

4

"S:\New Docs\ Old Documents Folder\Last Years Sales1 London.xlsm"<strike></strike> <tbody> </tbody> <strike></strike>

Last Years Sales1 London<strike></strike>

Not Found!

5

6

7

8

9

<tbody>
</tbody>

 

[dmt32]

Give this a try & see if does what you want

In STANDARD module:

Public Function FileExists(ByVal FileName As String) As Boolean
    Dim Attr As Long
    On Error Resume Next
    Attr = GetAttr(FileName)
    FileExists = (Err.Number = 0) And ((Attr And vbDirectory) = 0)
    On Error GoTo 0
End Function

and then formula in Column C

=IF(FileExists(A4),"Found","Not Found")

drag down as required.

Hope Helpful

Dave

 

[tonywatsonhelp]

Thanks dave
This is great


Tony