Folder and filenames?

[Mark]

I want to list (from a known directory eg: E:\Customers)
the folder names (that branch off the Customers folder)
into sheet1 and filenames in each folder into sheet2. Can
this be done with VBA or do I need to resort to an API
call?
If anyone knows or has an idea, let me know here and at my
email:
(e-mail address removed)

Alternatively,
In vb6 there is a control that lists all the files in a
given directory, in a listbox. Is there a similar control
in excel?

Could I use this control in excel? I've got it but haven't
tried it yet.

regards
Mark

 

[Bob Phillips]

Mark,

A bit of code goes through all folders ands files from a given folder and
lists them on a worksheet as hyperlinks, you should be able to adapt to
what you want, both for the former and the latter requirement. There is no
control that I know of that does it.


Dim FSO As Object
Dim cnt As Long
Dim arfiles
Dim level As Long

Sub Folders()
Dim i As Long
Dim sFolder As String

Set FSO = CreateObject("Scripting.FileSystemObject")

arfiles = Array()
cnt = -1
level = 1

sFolder = "C:\myTest"
ReDim arfiles(1, 0)
If sFolder <> "" Then
SelectFiles sFolder
Worksheets.Add.Name = "Files"
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles, 2)
.Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(1, i)), _
Address:=arfiles(0, i), _
TextToDisplay:=arfiles(0, i)
Next
.Columns("A:Z").EntireColumn.AutoFit
End With
End If

End Sub

'-----------------------------------------------------------------------
Sub SelectFiles(Optional sPath As String)
'-----------------------------------------------------------------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

If sPath = "" Then
Set FSO = CreateObject("SCripting.FileSystemObject")
sPath = "c:\myTest"
End If

Set Folder = FSO.GetFolder(sPath)

Set Files = Folder.Files
For Each file In Files
cnt = cnt + 1
ReDim Preserve arfiles(1, cnt)
arfiles(0, cnt) = Folder.Path & "\" & file.Name
arfiles(1, cnt) = level
Next file

level = level + 1
For Each fldr In Folder.Subfolders
SelectFiles fldr.Path
Next

End Sub

 

[papou]

Hi Mark
Application.FileSearch will also do the job but partially:
With Application.FileSearch
..NewSearch
..LookIn = "E:\Customers"
..SearchSubFolders = True
..FileType = msoFileTypeAllFiles 'see choice in list
If .Execute() > 0 Then
Worksheets("Sheet1").Cells(1, 1).Value = "File Name"
For i = 2 To .FoundFiles.Count
Worksheets("Sheet1").Cells(i, 1).Value = .FoundFiles(i)
Next i
Else
MsgBox "No files where found in the specified folder", vbInformation,
"No Match"
End If
End With

HTH
Cordially
Pascal

 

[keepITcool]

in addition to Bob's code..


These will bring up standard dialogs for opening and browsing files.
see VBA help for details


office.FileDialog
excel.GetOpenfileName

Private Sub Test()
Dim xlapp As Object
Set xlapp = GetObject(, "excel.application")
xlapp.dialogs(475).Show
'475 = xlDialogFileFind
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Mark]

Hi Bob,
Thanks for the response. I will try your code.

I have to write a macro that picks up the full path and
address and xls file from the sheet, open it, copy a range
of data, then paste that data in my main book, close the
source book, goto next xls. and loop.

What I am doing at the moment, but I think your code will
work better, is using a vb6 userform with a directory list
box control, a file list control, and a drive list control.

THe main form has the drive list control. The user selects
a drive because i am using drive E he may well have to
access drive A to Z. A button exceutes to go. changes dir
and drive. Another form loads but does not open. It holds
the dir list, which loads info about current directory and
drive the dir list. a macro writes to a text file all
names of folders. another form loads, not show, with file
listbox contents of sub folder of folder in previous list,
writes to a text file all "*.xls" files.

When all this is complete, about 500 folders/sub folders
and 5,000 xls files, excel starts and automatically
imports the list, then copies an unknown range, probably
cell to last cell, to the main book. ....
I am hoping that your macro can do what the vb6 utility
(not quiter finished) is designed for.

Sorry for all the banter, I just wanted you to know what
it is all about and ps, I have not asked for a fee on this
ptroject(yet) from the "client". Let me see if I can do it
first!

 

[Mark]

Thanks for the reply, I might be able to use some of this
regards
mark

 

[Mark Philpot]

Hi,
Thanks for the reply, I will try it out but basically,
there can be no userintervention except to say "go". See
my reponse to Bob Phillips.

regards
mark