HOW TO SEARCH FOR FILES FROM EXCEL?

[QUILO]

I HAVE ON EXCEL A LIST OF MP3 FILES WITH THE FILE NAME AND THE DESCRIPTION OF THE AUTHOR, THERE ARE ABOUT 500 OF THEM SPREAD IN ALL OF MY COMPUTER, I HAVE ERASE SOME BY MISTAKE BUT I DON’T KNOW WITCH ONES.
HOW CAN I SEARCH IN ALL THE COMPUTER FOR THE FILES I HAVE ON THE LIST?
1)IF THE FILE EXIST, I WANT TO KNOW WERE IS IT LOCATED.
2)IF THE FILE DOES NOT EXIST, I WANT TO CHANGE THE COLOUR OF THE FONT TO “RED” ON THE CELL WERE IS THE FILE NAME

CAN SOME ONE HELP ME ON THIS TASK?

THANKS
QUILO

 

[WantsToLearn]

Check out the dir command. It will look for a file name in a given folder and also subfolders as well.

Good Luck!

 

[QUILO]

Dear
WantsToLearn

That’s not what I need but thanks any way
What I need is
I have on Excel a list of files and the description of each one in two rows, on row “A” are the files names
And on row “B” are the description.
This list represents an inventory of all my Mp3 files, I have about 500 of files names and their description
But the files are all over my computer on different folders or different hard disk and by mistake I erased some files but I don’t know witch. I don’t want to look one by one, with a VBA or macro I want to do it automatically to find witch one I don’t have. I want that the list show on “red” the files that are not on the
Computer or on the folder.

Would any one help me on this?
Is there a way to do it?

Thanks
QUILO

 

[lambuhere1]

How about using Filesystemobject ?

 

[WantsToLearn]

Try something like the following:

Public Sub FindMyFiles()
Dim lngRow As Long
Dim lngCol As Long
Dim lngStartRow As Long
Dim lngLastRow As Long

lngStartRow = first row you want to check
lngLastRow = last row in your worksheet
lngCol = column in your worksheet (A = 1)

For lngRow = lngStartRow To lngLastRow
If Dir(Cells(lngRow, lngCol) = vbNullString Then
Cells(lngRow, lngCol).Font.Color = RGB(0, 255, 0)
End If
Next lngRow
End Sub

I couldn't test this but it should be close. Good Luck!

 

[QUILO]

Dear
WantsToLearn

The sub you send me has some syntax errors I don’t know
What is the problem or how can I fix it
Thanks
QUILO

 

[WantsToLearn]

Hello Quilo,

Please accept my apologies, I forgot a right paren )

Replace this line:

If Dir(Cells(lngRow, lngCol) = vbNullString Then

With this one:

If Dir(Cells(lngRow, lngCol)) = vbNullString Then

Good Luck!