Selecting files with file name in Worksheet

[Haroon]

Hi

I have list of filenames in worksheet, and i want to find out if there is a
way of selecting the files in a given folder, e.g. c:\test, to be selected
with the filenames in the worksheet?

e.g. file name in worksheet is test1
filename in folder c:\test1.xls
when i run the macro, it goes through the list of filenames in the rang
a1:a100, and hightlights files in that folder c:\test1.xls, test2.xls etc.

thanks in advance

 

[Joel]

What are you trying to do? Do you have an explorer open and want to show
only certina files? A file by itself can't get selected, it must be in some
sort of window.

 

[Haroon]

yeah i have window explorer/folder open too.

i want to run the macro to highlight the files in explorer according to file
names on the worksheet.

 

[Rick Rothstein]

I'm not sure if VB can do that to Windows Explorer or not, but my question
is "why do that"? I mean, you can't be doing it to simply show the names to
the user (they can already see them in the worksheet), so what is it you
want to do with those "highlighted" filenames? Perhaps VB can do that step
for you directly.

 

[Haroon]

i have lots of files in the folder, but the filenames in the worksheet are
limited and i want to highlight filenames which are in the worksheet and
delete them.

 

[Joel]

As I said, put the filename into a multiselect Listbox and have the user
select the fil;es to delete in the listbox. Then delete the select items in
the listbox. It is equivalent to what you are trying to do and easier to
program.

 

[Haroon]

yeah i want to delete all the files which appear in the worksheet, so user
dont need to select files from multiselect listbox to delete,

bascialy i have one colmn in worksheet like
test1.doc
test2.doc
test3.doc

i want a macro to select this range and delete these files from c:\test folder

cheers

 

[Rick Rothstein]

Give this macro a try (change the assignments in the Const statements to
those for your actual layout). Whatever filenames (no path, it is specified
in the Const statement) are listed in the worksheet specified in the Const
statement (for the column/row set in the Const statements) will be deleted.

Sub DeleteFilenames()
Dim X As Long
Dim LastRow As Long
Dim FN As String

Const StartRow As Long = 3
Const DataColumn As String = "B"
Const SheetName As String = "Sheet4"
Const DirPath As String = "c:\Test\" ' <=Note the backslash

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = StartRow To LastRow
FN = Dir(DirPath & .Cells(X, DataColumn))
If Len(FN) > 0 Then
Kill DirPath & FN
End If
Next
End With
End Sub

 

[Haroon]

Thanks Rick, your a star *

Works perfect :
-------------------------------------------------------------------------------------