User input for folder location

**jmckee** · 09-02-2010, 01:50 PM

I need help adding this command to my script. Right now my script loops through a folder, opens each workbook, sorts it, saves it, then closes it.

I would like to add a user input for which folder to perform the loop on.

Any help would be appreciated.

Thanks

**blane245** · 09-02-2010, 03:19 PM

Welcome to the forum.

See the Application.FileDialog property and FileDialog Object in the VBA help for starters. If you are still having problems, check back here.

**JBeaucaire** · 09-02-2010, 03:21 PM

This is my standard "folder picker" method:

Code:

Dim fPath as String

'Folder selection
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
    End With

Then use the fPath in the rest of your code.

**jmckee** · 09-02-2010, 03:57 PM

Thanks for the advice. I am new to writing code. Here is my code so far. I think what you told me will work, I just cant get it to integrate with what I already have.

Any help would be appreciated.

Thanks again.

Code:

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change Path to Appropriate Location

.LookIn = ("H://Excel/Test")
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
.Filename = "*.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)
'Select Range, sort and save workbook
Range("A1:F6000").Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _
("B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
ActiveWorkbook.Save

wbResults.Close SaveChanges:=False
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

**jmckee** · 09-02-2010, 04:02 PM

Sorry, this might be easier to read.

**royUK** · 09-02-2010, 04:07 PM

We have code tags to make the code easier to read!

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here