Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Windows File Structrure (2002)

    Does anyone know how to create a list of files on a computer in an Excel spreadsheet? I want to take an entire directory and input it into Excel and then compare it with an entire directory on a DVD. Does anyone have any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Windows File Structrure (2002)

    Try this code (copy it into a standard module):

    Sub ListFiles()
    Dim i As Long
    Dim strPath As String
    Dim strFile As String
    Dim strCol As String
    strPath = BrowseFolder
    If strPath = "" Then Exit Sub
    strCol = UCase(InputBox("Specify output column (A-Z)", , "A"))
    If Not Len(strCol) = 1 Then Exit Sub
    If Asc(strCol) < 65 Or Asc(strCol) > 90 Then Exit Sub

    If Not Right(strPath, 1) = "" Then
    strPath = strPath & ""
    End If

    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    i = i + 1
    Range(strCol & i) = strFile
    strFile = Dir
    Loop
    End Sub

    Public Function BrowseFolder( _
    Optional Title As String = "Select a Folder", _
    Optional RootFolder As Variant) As String
    On Error Resume Next
    BrowseFolder = CreateObject("Shell.Application"). _
    BrowseForFolder(0, Title, 0, RootFolder).Items.Item.Path
    End Function

    You can run ListFiles from the Visual Basic Editor by clicking somewhere in it and pressing F5, or from Excel by selecting Tools | Macro | Macros, selecting ListFiles and clicking Run.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Windows File Structrure (2002)

    The code below will put a list of files in the directory C:Work in column A of Sheet1:

    <pre>Dim strFName As String
    Dim strPath As String
    Dim I As Long
    I = 0
    strPath = "C:Work*.*"
    strFName = Dir(strPath)
    Do While strFName <> ""
    Worksheets("Sheet1").Range("A1").Offset(I, 0) = strFName
    I = I + 1
    strFName = Dir
    Loop
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •