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!
-----Original Message-----
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
.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Mark" wrote in message
...
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:
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
.