Hello

I want to develop a spreadsheet (Excel) which will have all the files in one
of my folders as on of its columns!

I have tried opening explorer , using copy & paste, but it doesn't seem to
work

Can anybody help please ?

Thanks

KK

Try this. Allows you to select file extension or serach on all files and then
select search folder. Puts the results in a new worksheet but that would be
easily changed:-

Option Explicit

Sub SrchForFiles()
Dim i As Long, z As Long, Rw As Long
Dim ws As Worksheet
Dim y As Variant
Dim fLdr As String, Fil As String, FPath As String

y = Application.InputBox("Please Enter File Extension - leave blank for
all files", "Info Request")
If y = False And Not TypeName(y) = "String" Then Exit Sub
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
fLdr = .SelectedItems(1)
End With
With Application.FileSearch
.NewSearch
.LookIn = fLdr
.SearchSubFolders = True
.Filename = y
Set ws = ThisWorkbook.Worksheets.Add(Sheets(1))
On Error GoTo 1
2: ws.Name = "FileSearch Results"
On Error GoTo 0
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Fil = .FoundFiles(i)
FPath = Left(Fil, Len(Fil) - Len(Split(Fil,
"\")(UBound(Split(Fil, "\")))) - 1)
If Left$(Fil, 1) = Left$(fLdr, 1) Then
If CBool(Len(Dir(Fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 4) = _
Array(Dir(Fil), _
FileLen(Fil) / 1000, _
FileDateTime(Fil), _
FPath)
ws.Hyperlinks.Add Anchor:=Cells(z + 1, 1), _
Address:=.FoundFiles(i)
End If
End If
Next i
End If
End With

ActiveWindow.DisplayHeadings = False

With ws
Rw = .Cells.Rows.Count
With .[A1:D1]
.Value = [{"Full Name","Kilobytes","Last Modified", "Path"}]
.Font.Underline = xlUnderlineStyleSingle
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With
.[E1:IV1 ].EntireColumn.Hidden = True
On Error Resume Next
Range(Cells(Rw, "A").End(3)(2), Cells(Rw, "A")).EntireRow.Hidden =
True
Range(.[A2 ], Cells(Rw, "C")).Sort [A2 ], xlAscending, Header:=xlNo
End With

Application.ScreenUpdating = True
Exit Sub
1: Application.DisplayAlerts = False
Worksheets("FileSearch Results").Delete
Application.DisplayAlerts = True
GoTo 2
End Sub


Mike


"KK" wrote:

Hello

I want to develop a spreadsheet (Excel) which will have all the files in one
of my folders as on of its columns!

I have tried opening explorer , using copy & paste, but it doesn't seem to
work

Can anybody help please ?

Thanks

KK

Hi Kk,

I want to develop a spreadsheet (Excel) which will have all the files in one
of my folders as on of its columns!

I have tried opening explorer , using copy & paste, but it doesn't seem to
work

Can anybody help please ?

In B1, enter your path and file wildcard, e.g.

c:\my documents\*.xls

Then select Insert, name, define.

Type FileList in the name box and

=FILES($B$1)

in the RefersTo box and hit enter.

Now in cell A1 type :

=INDEX(FILES,ROW())

copy down.

Note: Excel 2000 crashes if you copy a cell with this formula to another
worksheet.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

You might also want to take a look at the "List Files" program from

http://www.realezsites.com/bus/primi...e/products.php

Its pretty easy to use, has a nice ReadMe file, and is adaptable to many uses.

--
Best Regards,

Luke Moraga


"KK" wrote:

Hello

I want to develop a spreadsheet (Excel) which will have all the files in one
of my folders as on of its columns!

I have tried opening explorer , using copy & paste, but it doesn't seem to
work

Can anybody help please ?

Thanks

KK

Jan

Thanks for the help, but I'm struggling to understand.

I cannot find the menu sequence Insert/name/define ! I suspect the problem
is that I have excel 2007 (sorry - I should have said so in my original
email)

Thanks again

KK

"Jan Karel Pieterse" wrote in message
...
Hi Kk,

I want to develop a spreadsheet (Excel) which will have all the files in
one
of my folders as on of its columns!

I have tried opening explorer , using copy & paste, but it doesn't seem
to
work

Can anybody help please ?

In B1, enter your path and file wildcard, e.g.

c:\my documents\*.xls

Then select Insert, name, define.

Type FileList in the name box and

=FILES($B$1)

in the RefersTo box and hit enter.

Now in cell A1 type :

=INDEX(FILES,ROW())

copy down.

Note: Excel 2000 crashes if you copy a cell with this formula to another
worksheet.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi

There was a small typo in Jan Karel's formula to be entered in A1
=INDEX(FILES,ROW())
should have read
=INDEX(FILELIST,ROW())

In XL2007, to Insert the name Filelist, Formulas tabDefined Names
sectionDefine Name
--
Regards

Roger Govier


"KK" wrote in message
...
Jan

Thanks for the help, but I'm struggling to understand.

I cannot find the menu sequence Insert/name/define ! I suspect the
problem is that I have excel 2007 (sorry - I should have said so in my
original email)

Thanks again

KK

"Jan Karel Pieterse" wrote in message
...
Hi Kk,

I want to develop a spreadsheet (Excel) which will have all the
files in one
of my folders as on of its columns!

I have tried opening explorer , using copy & paste, but it doesn't
seem to
work

Can anybody help please ?

In B1, enter your path and file wildcard, e.g.

c:\my documents\*.xls

Then select Insert, name, define.

Type FileList in the name box and

=FILES($B$1)

in the RefersTo box and hit enter.

Now in cell A1 type :

=INDEX(FILES,ROW())

copy down.

Note: Excel 2000 crashes if you copy a cell with this formula to
another
worksheet.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi Roger,

There was a small typo in Jan Karel's formula to be entered in A1
=INDEX(FILES,ROW())
should have read
=INDEX(FILELIST,ROW())

In XL2007, to Insert the name Filelist, Formulas tabDefined Names
sectionDefine Name

Thanks for jumping in and correcting my mistake.
Ron De bruin has a nice page which shows you what commands went where
in XL2007:
http://www.rondebruin.nl/0307commands.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi Jan Karel
Thanks for jumping in and correcting my mistake.
As the OP was from some time ago, I didn't know whether you were
"around" to pick it up!!!

In addition to the help that Ron has put on his site, I came across this
add-in from MS recently, which inserts a Get Started tab on the ribbon
with lots of useful help for new users when trying to get to grips with
the new Ribbon layout
http://snipurl.com/1jr7e

This includes an interactive guide where you can see a 2003 layout,
hover over the command and it will show you where that is now located.
Click on the command and the screen changes to XL2007 with the area of
the Ribbon highlighted.
There are direct links from the new ribbon tab to MS sites with demo
videos etc.

--
Regards

Roger Govier


"Jan Karel Pieterse" wrote in message
...
Hi Roger,

There was a small typo in Jan Karel's formula to be entered in A1
=INDEX(FILES,ROW())
should have read
=INDEX(FILELIST,ROW())

In XL2007, to Insert the name Filelist, Formulas tabDefined Names
sectionDefine Name

Thanks for jumping in and correcting my mistake.
Ron De bruin has a nice page which shows you what commands went where
in XL2007:
http://www.rondebruin.nl/0307commands.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Jan (and Roger )

Thanks again, this works nicely.

I can't seem to find help pages on the =FILES function, so is it a VBA
command or something ?

Are there other similar commands for getting file info, specifically can I
also read the file size ?

Thanks again

K


"Jan Karel Pieterse" wrote in message
...
Hi Roger,

There was a small typo in Jan Karel's formula to be entered in A1
=INDEX(FILES,ROW())
should have read
=INDEX(FILELIST,ROW())

In XL2007, to Insert the name Filelist, Formulas tabDefined Names
sectionDefine Name

Thanks for jumping in and correcting my mistake.
Ron De bruin has a nice page which shows you what commands went where
in XL2007:
http://www.rondebruin.nl/0307commands.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Hi Kk,

n't seem to find help pages on the =FILES function, so is it a VBA
command or something ?

Are there other similar commands for getting file info, specifically can I
also read the file size ?

It is an ancient xl4 macro function. Check out this page for more info:

http://www.jkp-ads.com/Articles/ExcelNames08.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com