Tuesday, October 2, 2012

VBA List all files in directory - Excel


Sub List_All_Docs_in_Directory()
'______________________________________
'
'This VBA macro lists all files in a directory
'This quick macro works in Excel
'______________________________________
'

Dim The_Directory_Path, The_File_Name, The_Path_To_List, The_Sheet
Dim Row_Start As Long
Dim Column As Long
Dim The_Variable As String

The_Path_To_List = "C:\*.*"   'Choose the directory where the files are
                              'You can also choose which type of file you want,
                              'for example, if you want only Word documents,
                              'use this: pathstr = "C:\*.doc"

The_Sheet = "Sheet1"          'Name the Sheet where you want your results

If Len(The_Path_To_List) = 0 Then Exit Sub
The_Directory_Path = Dir(The_Path_To_List)
The_File_Name = The_Directory_Path

Row_Start = 1                 'Here you start listing the results on row 1
Column = 1                    'Here you start listing the results on column 1
                              'So the list of files will be in column A
                     
Do Until The_File_Name = ""
    If (The_File_Name <> "." Or The_File_Name <> "..") Then
     The_Variable = The_File_Name
      Worksheets("Sheet1").Cells(Row_Start, Column).Value = The_File_Name
      The_File_Name = Dir
      Row_Start = Row_Start + 1
    End If
Loop

End Sub

No comments:

Post a Comment

You may comment or show me other VBA tricks, but don't rest assured I'll always reply because I only have 24 hours in a day's hard work, and only a few minutes a week to update this blog... I'll try my best though...