Excel - copy explorer folder list to excel

Asked By KK

27-Apr-07 08:16 AM

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

27-Apr-07 08:42 AM

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,
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

27-Apr-07 09:45 AM

Hi Kk,


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

27-Apr-07 11:06 AM

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

http://www.realezsites.com/bus/primitivesoftware/products.php

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

--
Best Regards,

Luke Moraga

10-May-07 07:41 AM

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

10-May-07 08:12 AM

Hi

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

In XL2007, to Insert the name Filelist, Formulas tab>Defined Names
section>Define Name
--
Regards

Roger Govier

10-May-07 08:23 AM

Hi Roger,


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

10-May-07 08:39 AM

Hi Jan Karel
As the OP was from some time ago, I didn't know whether you were

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

13-May-07 07:42 AM

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

14-May-07 12:02 AM

Hi Kk,


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