Compare file names to folder contents

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 my desktop. I'd like a way to compare the list of file names to the folder 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 matching PDF file in my desktop folder and place a "yes" or "no" in the adjacent column?

Thank you!

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


"marlea" <[email protected]> wrote in
message news:[email protected]...
>
> 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 my
> desktop. I'd like a way to compare the list of file names to the folder
> 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 matching
> PDF file in my desktop folder and place a "yes" or "no" in the adjacent
> column?
>
> Thank you!
>
>
> --
> marlea
> ------------------------------------------------------------------------
> marlea's Profile:
> http://www.excelforum.com/member.php...o&userid=26209
> View this thread: http://www.excelforum.com/showthread...hreadid=522891
>

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

Hi Steve-

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

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

I'd appreciate any help you could offer. Thanks.