Compare file names to folder contents

[marlea]

Hi-

In an Excel file I have a column of PDF file names; e.g., aaA.pdf
AbC.pdf; 123.pdf. The -actual- PDFs should be in a folder on m
desktop. I'd like a way to compare the list of file names to the folde
contents to verify that the PDFs are in fact in my desktop folder.

For each file name in the Excel doc, can a macro search for a matchin
PDF file in my desktop folder and place a "yes" or "no" in the adjacen
column?

Thank you

 

[Steve Yandl]

Here is one approach. Assuming that the folder on your desktop where the
files to check is located is named "Test", create the user defined function
below in a module. Then if your column of file names is in A, you just
enter
=FileInDeskFldr(A1)
and drag the function down to get results for the entire column. The
function returns True or False but you can nest it in an IF function and
have it deliver "yes" or "no" if you want.


Function FileInDeskFldr(fName As String) As Boolean

Const Desktop = &H10&

Dim strDsk As String
Dim strFldrPath As String

' Find path to the Folder on the Desktop
Set objShell = CreateObject("Shell.Application")
Set objFolderDsk = objShell.Namespace(Desktop)
strDsk = objFolderDsk.Self.Path
strFldrPath = strDsk & "\Test"

' Verify existence of named file in the Desktop Folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strFldrPath & "\" & fName) Then
FileInDeskFldr = True
Else
FileInDeskFldr = False
End If

End Function


Steve

 

[Steve Yandl]

I probably should have cleaned up the objects I created. The function above
will work fine but this is a bit better.

Function FileInDeskFldr(fName As String) As Boolean

Const Desktop = &H10&

Dim strDsk As String
Dim strFldrPath As String

' Find path to the Folder on the Desktop
Set objShell = CreateObject("Shell.Application")
Set objFolderDsk = objShell.Namespace(Desktop)
strDsk = objFolderDsk.Self.Path
strFldrPath = strDsk & "\Test"

' Verify existence of named file in the Desktop Folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strFldrPath & "\" & fName) Then
FileInDeskFldr = True
Else
FileInDeskFldr = False
End If

Set objFolderDsk = Nothing
Set objShell = Nothing
Set objFSO = Nothing

End Function

 

[marlea]

Hi Steve-

Thanks for the reply. I tried the function and when I dragged it ove
the cells, I got this: "#VALUE!".

I'm wondering if this has something to do with the fact that I'm on
Mac--perhaps the path to the desktop folder has to be define
differently? Unfortunately, I'm not sure how to resolve this.

I'd appreciate any help you could offer. Thanks