Solved

Create list in excel of all folders within a directory

Posted on 2011-02-17
5
422 Views
Last Modified: 2012-05-11
I am a bit confused on how to come up with a macro that lists the folders in a directory

Directory: Q:\Deburr issue\WT Review\Ship 0008

There are also some .zip files in this directory, but i just want to list the folders.

Thanks,

Brandon
0
Comment
Question by:KnutsonBM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34915445
Try this:
Sub PlainFolderListing()
   Dim fso As FileSystemObject, fdrSelected, fdrSub, filTemp
   Dim strFilePath As String, rngOutput As Range
   
   ' change folder as required
   strFilePath = "Q:\Deburr issue\WT Review\Ship 0008"
   
   ' change list destination as required
   Set rngOutput = ActiveSheet.Range("A1")
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set fdrSelected = fso.GetFolder(strFilePath)
   For Each fdrSub In fdrSelected.SubFolders
      rngOutput.value = fdrSub.Path
      Set rngOutput = rngOutput.Offset(1)
   Next fdrSub
   Set fso = Nothing

End Sub

Open in new window

0
 
LVL 6

Author Comment

by:KnutsonBM
ID: 34915462
at Dim fso As FileSystemObject i get 'Compile Error: User-defined type not defined'


any thoughts

Brandon
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34915465
Change that to:
Dim fso As Object

Open in new window

0
 
LVL 6

Author Comment

by:KnutsonBM
ID: 34915482
what do i need to do to remove the path and leave just the name of the folder on the output?
0
 
LVL 6

Author Comment

by:KnutsonBM
ID: 34915491
nevermind i got it, changed fdrSub.Path to fdrSub.Name
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question