Looking for a script to help find multiple file names in windows from an excel file

Hi,
I have an excel filled with over 5000 cells of names, i also have a folder with over 10000 names of PDF files, i need to know which one of the names in the cell exists in the folder.
i'm looking for a script to go one cell at a time (all in one column) and highlight\bold\any other modification to that cell so i'll know that it's located in the folder.

*Note, the cell only contains part of the PDF name, (e.g. cell name = XXX, pdf file name = a-xxx.pdf) a windows search finds it without problems

any scripts for that? i've tried to google it but i couldn't find a script to highlight the once found, only to move the files to another folder but that would require the exact name in the cell.
johnnyjonathanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can use the following code to do what you are looking for...

I have assumed your data is in A Column--and it will check for till the last row of A Column...

If the file is found it will bold the a column value and will give Y in B column otherwise N for that entry..

Sub checkfiles()

    Dim xpath As String
    Dim filename As String
    Dim rng As Range, cell As Range
    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = Range("A1:A" & lrow)


    xpath = "Your path here which you want to check with backslash at end"    '<--Like "C\abc\"

    For Each cell In rng

        filename = "a-" & cell.Value & ".pdf"

        If Len(Dir(xpath & filename)) > 0 Then
            cell.Font.Bold = True
            cell.Offset(0, 1).Value = "Y"
        Else
            cell.Offset(0, 1).Value = "N"
        End If

    Next cell

End Sub

Open in new window


Saurabh...
0
johnnyjonathanAuthor Commented:
Thanks, how should i run the script?
0
Saurabh Singh TeotiaCommented:
Copy the macro in your desired excel file.... by pressing ALT+F11 and then insert module...

and paste the macro their...do the necessary changes as highlighted..like check hows your file name in the folder..the folder path and the range it should check basis of actual data..

Post making those changes come back to excel worksheet..press alt+f8 and run the macro which you see in their which is checkfiles
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Bill PrewIT / Software Engineering ConsultantCommented:
Is it true that the PDF file name will always be a-xxx.pdf where xxx is the value in excel cell, or could the xxx be anywhere in the name, etc?

~bp
0
johnnyjonathanAuthor Commented:
XXX might be anywhere in the name
0
johnnyjonathanAuthor Commented:
Saurabh, your script works great  (Thanks!) if the file names are exactly a-xxx.pdf however they are not all exactly the same.
anyway the script can be modified to search for XXX in any part of the file name?
0
Bill PrewIT / Software Engineering ConsultantCommented:
No points for me, but try changing:

        filename = "a-" & cell.Value & ".pdf"

to:

        filename = "*" & cell.Value & "*.pdf"

~bp
0
johnnyjonathanAuthor Commented:
Hi Bil,
Thanks for the addition, please don't think that points won't be shared :)

i've tried your suggestion, and i got a weird result, it does look like it's finding some of the files based on part of the file name existing in the cell, however, it also marked some cells that i simply couldn't find in the windows search when i went and double checked it myself, what do you think could cause it?
0
Saurabh Singh TeotiaCommented:
I was looking for the code and if i understand you want to do search on like rather then the exact name..?

The only problem with like which i see that it can lead to wrong results by that i mean...

Lets say you have one file only with name of of let's say a-abcd.pdf

Now lets say if you are looking for search names of

abc
And abcd

It will give you match for both of them since abcd has abc as well, However this match of abcd only and for abc it should not show match...

However let me know what are your thoughts on this..I can write the code for you to do that but like i said it won't be full proof as given in the above example...
0
johnnyjonathanAuthor Commented:
That sounds perfect, a "like" search sounds like the way to go
0
Saurabh Singh TeotiaCommented:
Yeah but jonathan like i said it will have above bug where if the file name is

a-abcd.pdf

and in your ranges you have
abc in one cell
and abcd in one cell

It will set true for both of them in like where it should only set true for abcd but since you are using like and in like criteria both of them will match and give you an answer that match found..
0
johnnyjonathanAuthor Commented:
that's not a problem, it will get me a lot closer then i am right now..
0
Saurabh Singh TeotiaCommented:
You can use the following code to do what you are looking for...

Sub checkfiles()

    Dim xpath As String
    Dim filename As String
    Dim rng As Range, cell As Range
    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = Range("A1:A" & lrow)

    xpath = "Your file path here"    '<--Like "C\abc\"

    For Each cell In rng
        If checkfile(xpath, cell.Value) = True Then
            cell.Font.Bold = True
            cell.Offset(0, 1).Value = "Y"
        Else
            cell.Offset(0, 1).Value = "N"
        End If

    Next cell

End Sub

Function checkfile(str As String, sname As String) As Boolean

    Dim fname As String

    fname = Dir(str & "\" & "*.pdf")

    Do While fname <> ""
        If InStr(1, fname, sname, vbTextCompare) > 0 Then checkfile = True
        fname = Dir()
    Loop

End Function

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyjonathanAuthor Commented:
Thank you! works amazingly!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.