Command button to open file explorer in Macro and store path, run the VBA

[kaarthikz]

Hi All,

Below is the code for Macro which opens all the excel files in the given folder and pastes the data in the Macro enabled sheet

sub openfile()

Dim spath as string

dim sfil as string

dim strname as string

dim owbk as workbook

dim twbk as workbook

dim ws as worksheet

set twbk = activeworkbook

spath = "h:\desktop
ewfolder

sfil = Dir(spath & "*.xlsx")

Do while sfil <> ""

strname = spath & sfil

set owbk = workbooks.open(strname)

set ws = owbk.sheets(1)

Range("A5").select

Range(selection, selection.end(xltoright)).select

Range(selection, selection.end(xldown)).copy

twbk.sheets(1).range("A1048576").end(xlup)(2).pastespecial xlpastevalues

owbk.close false

sfil = Dir

loop

twbk.save

end sub

Can you help me to create a Button where i can save the path instead of always opening the code to choose the path and a button to run the Macro.



Thank you

 

[Ajesh]

Replace below line in your code:

spath = "h:\desktop
ewfolder

with this:

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With FldrPicker
        .Title = "Select Folder......."
        .AllowMultiSelect = False
          If .Show <> -1 Then GoTo CleanExit ' In case user clicks on Cancel
          spath = .SelectedItems(1) & "\"
    End With
CleanExit:
    spath = spath
    If spath = "" Then
        MsgBox "No folder location selected. Processing cancelled!!!!"
        Exit Sub
    End If

also add below declaration in your code:

 Dim FldrPicker As FileDialog

thanks/Ajesh