Getting a folder path to tie to a variable in my script

[bobrandom123]

I have this script that will go through a folder in which the path is hardcoded into the macro and consolidate all of the .csv files into the .xls I am working with utilizing Excel 2007.

Sub Consolidate()


Dim fName As String, fPath As String, fPathDone As String, OldDir As String
Dim LR As Long, NR As Long
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet


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


    Set wbkNew = ThisWorkbook
    wbkNew.Activate
    
    'make sure the name of the first tab at the bottom of this xls file says Sheet1
    Sheets("Sheet1").Activate


    If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub


    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        Cells.Clear
        NR = 1
    Else
        NR = Range("A" & Rows.Count).End(xlUp).Row + 1
    End If


    OldDir = CurDir
    
    'select the directory path that has your csv files
    ' An example would be fPath = "C:\Documents and Settings\Administrator\My Documents\Scripts\test\"
    ' Make sure you put the backslash "\" at the end
    fPath = "\"
    
    'select the folder you want the csv files to be moved to once they are combined in the xls file
    ' An example would be fPathDone = "C:\Documents and Settings\Administrator\My Documents\Scripts\test\converted\"
    ' Make sure you put the backslash "\" at the end
    fPathDone = "\"
    
    ChDir fPath
    fName = Dir("*-*.csv")




    Do While Len(fName) > 0
            Set wbkOld = Workbooks.Open(fName)
            LR = Range("A" & Rows.Count).End(xlUp).Row
            Range("A2:A" & LR).EntireRow.Copy _
                wbkNew.Sheets("Sheet1").Range("A" & NR)
            wbkOld.Close True
            NR = Range("A" & Rows.Count).End(xlUp).Row + 1
            Name fPath & fName As fPathDone & fName
            fName = Dir
    Loop




    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True




    ChDir OldDir


End Sub

How can I use msoFileDialogFolderPicker to get the user to select the folder that their .csv files are located (fPath) and the folder they wish to have the files moved to once converted (fPathDone)? I think it would make things easier on the end user if they can select the folder instead of going in and manipulating the script.

    'select the directory path that has your csv files
    ' An example would be fPath = "C:\Documents and Settings\Administrator\My Documents\Scripts\test\"
    ' Make sure you put the backslash "\" at the end
    fPath = "\"
    
    'select the folder you want the csv files to be moved to once they are combined in the xls file
    ' An example would be fPathDone = "C:\Documents and Settings\Administrator\My Documents\Scripts\test\converted\"
    ' Make sure you put the backslash "\" at the end
    fPathDone = "\"

 

[AlphaFrog]

Try something like this...

    [color=green]' Prompt user to select the source folder[/color]
    [color=darkblue]With[/color] Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Temp\"               [color=green]' Default path[/color]
        .Title = "Please Select the Source Folder"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = [color=darkblue]False[/color]
        .Show
        [color=darkblue]If[/color] .SelectedItems.Count = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]   [color=green]' User clicked cancel[/color]
        fPath = .SelectedItems.Item(1) & "\"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]' Prompt user to select the destination folder[/color]
    [color=darkblue]With[/color] Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Temp\"               [color=green]' Default path[/color]
        .Title = "Please Select the Destination Folder"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = [color=darkblue]False[/color]
        .Show
        [color=darkblue]If[/color] .SelectedItems.Count = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]   [color=green]' User clicked cancel[/color]
        fPathDone = .SelectedItems.Item(1) & "\"
    [color=darkblue]End[/color] [color=darkblue]With[/color]

 

[bobrandom123]

Awesome! Thanks!!