file mapping

[Acrowebat]

I'm in the process of mapping about 10 years worth of files. I want to build a simple flow chart to visually represent all the pathways (for example P Drive: Human Resources: Admin: Letters: Letter 1, Letter 2, Letter 3 etc).

Does anyone out there know if there's a way the file menu can be copied into Excel??

 

[dermie_72]

You could try this out.

Learn Excel Macro How to get list of All files in a Folder and Sub-folders

Is that sort of what you're after?

 

[Acrowebat]

that would have been perfect but IT wouldn't give me permission to download. Thanks for the suggestion though.

 

[dermie_72]

There was a script on the link, which captures the information you wanted.

Sub GetFilesInFolder(SourceFolderName As String)     
'--- For Example:Folder Name= "D:\Folder Name\"   </SPAN>
  
Dim FSO As Scripting.FileSystemObject   </SPAN>
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder   </SPAN>
Dim FileItem As Scripting.File   </SPAN>
  
    Set FSO = New Scripting.FileSystemObject   </SPAN>
    Set SourceFolder = FSO.GetFolder(SourceFolderName)   </SPAN>
  
    '--- This is for displaying, whereever you want can be configured   </SPAN>
  
    r = 14   
    For Each FileItem In SourceFolder.Files   </SPAN>
        Cells(r, 2).Formula = r - 13   
        Cells(r, 3).Formula = FileItem.Name   
        Cells(r, 4).Formula = FileItem.Path   
        Cells(r, 5).Formula = FileItem.Size   
        Cells(r, 6).Formula = FileItem.Type   
        Cells(r, 7).Formula = FileItem.DateLastModified   
        Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"   </SPAN>
  
        r = r + 1   ' next row number   </SPAN>
    Next FileItem   </SPAN>
  
    Set FileItem = Nothing  </SPAN>
    Set SourceFolder = Nothing  </SPAN>
    Set FSO = Nothing 
End Sub

There is one for subfolders too.