Loop thru Folder

[Jenawade]

Does someone have a working Application.GetOpenFilename macro they can share with me that will allow the user to choose the folder and run through the workbooks in that folder, performing an action on each one? In my case I want to search for any of 4 numbers in each of the workbooks, and if found, perform 'replace' in the document.

 

[Jenawade]

I tried this one, but the "TEST" is placed on the worksheet/workbook the macro is stored in. How do I get the macro to instead run that range value code on each workbook it opens in the selected folder?

Sub Open_All_Files2()
    Dim aryOpenFiles As Variant, i As Integer
    aryOpenFiles = Application.GetOpenFilename(MultiSelect:=True)
    If IsArray(aryOpenFiles) Then
        For i = LBound(aryOpenFiles) To UBound(aryOpenFiles)
           Range("Z90").Value = "TEST"
            MsgBox aryOpenFiles(i)
        Next i
    End If
End Sub

 

[wigi]

Where in the code do you open the file aryOpenFiles(i) ?

 

[Jenawade]

I don't know. I'm up to attempt 26 to find something on the internet that will open all the workbooks in a folder, search for any of: 130620, 130618, 133362, 130604, and if found, then replace any instance of "BLUE" or "ORANGE" with "BLACK." I spent the entire work day yesterday and never got anywhere because I don't know enough about this stuff -- but because what I DO know is more than my coworkers, its left to me to figure it out. Supposedly there are 'thousands' of excel workbooks on a server that my coworkers in another state have to open and change "BLUE" or "ORANGE" with "BLACK" if it's a 130620, 130618, 133362, 130604 part.

 

[kpark91]

I'm not a big fan of GetOpenFilename as I've never used it! ahha Xd

I would go with FSO folder picker if you don't want the user to loop select every single workbook.

    ' Requires reference to Microsoft Office 11.0 Object Library.
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim strFolderPath As String
 
    ' Set up the File Dialog
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
 
    With fDialog
        '.InitialFileName = ThisWorkbook.Path
        ' Allow user to make multiple selections in dialog box
        .AllowMultiSelect = False
 
        ' Set the title of the dialog box.
        .Title = "Please select one folder to save data file in"
 
        ' Clear out the current filters, and add our own.
        .Filters.Clear
 
        ' Show the dialog box. If the .Show method returns True, the
        ' user picked at least one file. If the .Show method returns
        ' False, the user clicked Cancel.
        If .Show = True Then
            strFolderPath = .SelectedItems(1)
        End If
    End With
 
    ' Freeing Object Variables
    Set fDialog = Nothing

and then you could make use of this code from ozgrid

Sub RunCodeOnAllXLSFiles()Dim lCount As LongDim wbResults As WorkbookDim wbCodeBook As WorkbookApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseApplication.EnableEvents = FalseOn Error Resume Next    Set wbCodeBook = ThisWorkbook        With Application.FileSearch            .NewSearch            'Change path to suit            .LookIn = "C:\MyDocuments\TestResults"            .FileType = msoFileTypeExcelWorkbooks            'Optional filter with wildcard            '.Filename = "Book*.xls"                If .Execute > 0 Then 'Workbooks in folder                    For lCount = 1 To .FoundFiles.Count 'Loop through all                        'Open Workbook x and Set a Workbook variable to it                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)                                                'DO YOUR CODE HERE                        wbResults.Close SaveChanges:=False                    Next lCount                End If        End WithOn Error GoTo 0Application.ScreenUpdating = TrueApplication.DisplayAlerts = TrueApplication.EnableEvents = TrueEnd Sub

</PRE>http://www.ozgrid.com/VBA/loop-through.htm

 

[kpark91]

So in ozgrid's code you would change it to

.LookIn = strFolderPath
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.xls" 'or "*.xlsm" or "*.xlsx" or w/e
'inside for loop you could put your code.

 

[wigi]

In an earlier topics of yours, you were already given code to loop through files in a folder.

http://www.mrexcel.com/forum/showpost.php?p=2771254&postcount=2

Why not revise that code, and merge it with what you now have?

 

[wigi]

You should know that Application.FileSearch is not supported as from Excel 2007. If you're on Excel 2003 or earlier, it will work.

 

[kpark91]

You should know that Application.FileSearch is not supported as from Excel 2007. If you're on Excel 2003 or earlier, it will work.

Blast!
Thanks for the info.

You can use this in any version.

Sub CopySameSheetFrmWbs()Dim wbOpen As WorkbookDim wbNew As Workbook'Change PathConst strPath As String = "C:\Excel\"Dim strExtension As String'Comment out the 3 lines below to debugApplication.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualOn Error Resume Next    ChDir strPath'Change extensionstrExtension = Dir(strPath & "*.xls")        Set wbNew = Workbooks.Add'Change Path, Name and File FormatwbNew.SaveAs Filename:="C:\Excel\TemplateCollation", FileFormat:=xlWorkbookNormal            Do While strExtension <> ""            Set wbOpen = Workbooks.Open(strPath & strExtension)                     With wbOpen                .Sheets("Template").Copy After:=wbNew.Sheets(wbNew.Sheets.Count)                wbNew.Sheets(wbNew.Sheets.Count).Name = wbNew.Sheets(wbNew.Sheets.Count).Cells(1, 1)                .Close SaveChanges:=False            End With                        strExtension = Dir        Loop        Application.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticOn Error GoTo 0End Sub

</PRE>

http://www.ozgrid.com/VBA/2007-filesearch-alternative.htm
</PRE>


</PRE>

 

[Jenawade]

Thank you, thank you! It's going along now. Wigi, I guess I was too freaked out by the whole thing because I completely forgot about that post! And no wonder none of the stuff I tried worked, they were all filesearch macros!

Thanks everyone, now I'm getting somewhere!