Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com
Learn how to create Excel dashboards.

Ozgrid Excel Newsletter. Excel Newsletter Archives  

VISIT OUR SPECIALS PAGE | ADVANCED SEARCH |FREE EXCEL & VBA LIFETIME SUPPORT | FREE DEMO DOWNLOADS

SUMMING BY DATE RANGE AND OTHER CRITERIA. DOWNLOAD DEMO

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.

EXCEL VBA: LOOP THROUGH FOLDER OF CHOICE. DOWNLOAD DEMO

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