• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67
  • Last Modified:

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.
0
johnnyjonathan
Asked:
johnnyjonathan
  • 7
  • 5
  • 2
1 Solution
 
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
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
Bill PrewCommented:
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 PrewCommented:
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
 
johnnyjonathanAuthor Commented:
Thank you! works amazingly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now