Importing a Directory Listing from CD to Excel

M

Mike

Good Morning all,

I hope you're all having a great day. I have been given
the task of creating a database of applications on CD
into Excel and I'm wondering if there is a VB function
that will output Root and sub folder names. I don't need
files contained in the folders, just folder names.
Preferably, I'd like to have 1 row for ever instance of a
folder:

Row1: Root1
Row2: Root1 Sub1
Row3: Root1 Sub1 Sub2
Row4: Root1 Sub1 Sub3 Sub3
Row5: etc...

Any ideas on how to go about doing this?

Thanks in advance and have a great one,

Mike
 
B

Bob Phillips

Mike,

This simple script should do it

Sub LoopFolders()
Dim i As Integer

Set objFSO = CreateObject("Scripting.FileSystemObject")

iRow = 0
iLevel = 0

selectFiles "c:\Windows"

Set objFSO = Nothing

End Sub

'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = objFSO.GetFolder(sPath)

iRow = iRow + 1
iLevel = iLevel + 1

Cells(iRow, iLevel) = Folder.Path

For Each fldr In Folder.subfolders
selectFiles fldr.Path
Next
iLevel = iLevel - 1

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Mike

Thanks Bob, but I'm getting a runtime error on the

Set Folder = objFSO.GetFolder(sPath)

call.
Runtime error '424'
Object required

Any thoughts? I can see the path being retained in the
(sPath) variable. I don't think I should need a plugin
for this one (Office XP).

Thanks in advance,

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top