 |
|
|
|
|
|
This month I will show you how to sum by a date range and other criteria. The method I use is the SUMPRODUCT Function, Data Validation and Conditional Formatting. If your table of data is large and/or you will be using multiple SUMPRODUCT Functions, you are better of with a PivotTable or Database Functions.
This month I want to show a simple method to loop through a Folder of a users choosing. For those who have upgraded to Excel 2007 you may, or may not, know the Application.FileSearch is not supported in 2007. This method WILL work for 97-2007 and beyond :)
Sub OutputFilesFolders()
Dim fdFolder As FileDialog
Dim strFolder As String
Dim strExtension As String
Dim lRow As Long
Set fdFolder = Application.FileDialog(msoFileDialogFolderPicker)
With fdFolder
.Title = "Select a Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
'It's here that we pass the folder path to our Variable
strFolder = .SelectedItems(1)
End With
ChDir strFolder
'Change extension or use: Dir("*") for all file types
strExtension = Dir("*.xls")
'Put in our headings
With Range("A1:B1")
.Value = Array("FILE NAME", "FOLDER")
.Font.Bold = True
End With
'Loop through Folder and out file names and folder
Do While strExtension <> ""
With Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0) = strExtension
.Offset(1, 1) = strFolder
End With
strExtension = Dir
Loop
End Sub
See ya next month :)
Excel Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft