Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder
Here’s a macro to list the names of all your Excel workbooks from a specified folder into column A of your worksheet.
In the first picture, you see several different types of files in the selected folder. The following macro will list only the Excel workbook names, as shown in the second picture.
To modify the macro to list, say, only the PowerPoint files, change ".xls" to ".ppt". If you want to list all files of all types, simply delete or comment out the lines
If InStr(objFile.Name, ".xls") > 0 Then
and the End If statement three lines below that.
Here’s the macro, which works in all version of Excel.
Sub ListExcelFiles()
'Declare and define variables.
Dim SourcePath As String, NextRow As Long
Dim objFSO As Object, objFolder As Object, objFile As Object
SourcePath = "C:\Your\File\Path\"
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set the folder object associated with your directory path.
'Halt the macro if the directory folder does not exist.
On Error Resume Next
Set objFolder = objFSO.GetFolder(SourcePath)
If Err.Number <> 0 Then
Err.Clear
MsgBox "This path does not exist:" & vbCrLf & _
SourcePath & vbCrLf & _
"Cannot continue.", 16, "No such animal."
Exit Sub
End If
'Define the NextRow variable as row 2.
'Row 1 will contain a header label.
NextRow = 2
'The workbook names will be listed in column A.
'Clear column A to start with a clean column.
Columns(1).Clear
'Place a header label in cell A1.
Range("A1").Value = "Excel files in the path " & SourcePath
'Loop through the Files in the source directory folder
'to list only the Excel file names.
For Each objFile In objFolder.Files
If InStr(objFile.Name, ".xls") > 0 Then
Cells(NextRow, 1).Value = objFile.Name
NextRow = NextRow + 1
End If
Next objFile
'Release the Objects variables from memory.
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
'For easier readability, autofit column A.
Columns(1).AutoFit
'Optional, sort the list.
Range("A1").CurrentRegion.Sort _
Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End Sub
Tom’s Tutorials For Excel: Commenting a Cell With Data Validation
Tom’s Tutorials For Excel: Commenting a Cell With Data Validation
As a practical alternative to using a comment for supplying information about a cell, consider using Data Validation instead. The advantage is, the user sees the popup message for directions or tips when they select the cell as they enter or edit data.
In the pictured sequence for example, select the range of cells that you want to supply a popup message for when a cell in that range is selected. Then, from your keyboard press
Alt+D+Lto show the Data Validation dialog box.Next:
• Click the Input Message tab.
• Check “Show input message when cell is selected”
• Enter a short title for the main idea of the cell entry.
• Enter a brief tip for the entry of data in that cell.
• Click OK.
You can see the result of the pictured settings below, when a cell in that range is selected.
Tags: Analytics, Big Data, Business Intelligence, Comments / Screen Tips, Data Science, Data validation, Excel Expert, Excel Guru, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis