Macros - Coding Macros - Windows Folder Listings

[theprofessionalnyc]

I apologize but I am not a VBA or Excel Guru.

I want to be able to use MS Excel 2007 Macro tool to create a list of yellow Folders on a certain hard drive (entire drive such as C:\ or E:\ or X:\ etc.) using the program below (see link), but I am stuck because of my cluelessness compared to you members who are Gods in my eyes for this stuff.

Here is the link below, but I am not sure where I "copy and paste" this code below to get Excel 2007 to work.

Can someone tell me where I can copy and paste this code in Excel and and also more importantly, besides this line of code here (which is obvious to
me which part of the code is where the developer is telling me that I need to customize the code to specify which directory to send the "output"

'Parent Directory - Change this to whichever directory you want to use
ListFoldersAndInfo "C:\Documents and Settings\Administrator\My Documents", startRange

Is there any other part of the program besides the above obvious, where I need to "adjust/customize" to specify my personal directory of the location of the folders or where I
want the output?


Here is the program and link where it came from:

http://www.excelforum.com/excel-prog...-in-excel.html

WHERE DO I CUT AND PASTE THIS CODE BELOW IN MS EXCEL 2007???

Sub ListThem()
   Dim startRange As Range
 
  Sheet1.Cells.Clear
  Set startRange = Sheet1.Range("A1")
  'Parent Directory - Change this to whichever directory you want to use
  ListFoldersAndInfo "C:\Users\MyName\Documents\Application Data", startRange, 0
End Sub
 
 
Sub ListFoldersAndInfo(foldername As String, Destination As Range, Level As Long)
  Dim FSO As Object
  Dim Folder As Object
  Dim R As Long
  Dim SubFolder As Object
  Dim Wks As Worksheet
 
    Set FSO = CreateObject("Scripting.FileSystemObject")
 
      Set Folder = FSO.GetFolder(foldername)
      Destination = Folder.Name
      Destination.IndentLevel = Level
 
      Destination.Offset(0, 1) = Folder.Path
      Destination.Offset(0, 2) = Folder.Size
      Set Destination = Destination.Offset(1, 0)
 
      For Each SubFolder In Folder.SubFolders
         ListFoldersAndInfo Folder.Path & "\" & SubFolder.Name, Destination, Level + 1
      Next SubFolder
 
    Set FSO = Nothing
 
End Sub

PS: I know that I must follow these instructions to enable Macro's function/tab in Excel in order to work with the Macro functions in Excel:

http://www.excelforum.com/excel-prog...00#post3190300

[AndyLitch]

Hi, try the following

1. Right click on a worksheet tab
2. Select view code
............View will now switch to the VB editor.
3. Find the VBA project which has the same name as you excel file in the project explorer to the left
4. Right click on it
5. Select "Insert Module"
6. Double click on the module
7. Paste your code into it.

[theprofessionalnyc]

Thanks AndyLitch - I've done that. See attached screen shot

Can you tell me what to do next?

[arlu1201]

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.