VBA to get a folder path using msofiledialogfolderpicker

[Meghanakusuma]

hi, please help me to find the error.
when i run this code, it says the path not found

Public Sub process()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'DECLARE AND SET VARIABLES
reset
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim sheet_four As Worksheet
Dim sheet_five As Worksheet
Set sheet_four = ThisWorkbook.Worksheets("Sheet4 - Part List&Rel Data")
Set sheet_five = ThisWorkbook.Worksheets("Sheet5 - FMEA")


Application.FileDialog(msoFileDialogFolderPicker).Show
Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & ""




'Path = Application.FileDialog(msoFileDialogFolderPicker)
'Path
'Path = "C:\Users\yatisha.arehally\Desktop\ALL Documents"


Filename = Dir(Path & "*.xls*")


'--------------------------------------------
'OPEN EXCEL FILES
 Do While Filename <> ""  'IF NEXT FILE EXISTS THEN
    Set wbk = Workbooks.Open(Path & Filename)
    '
    ' CODE GOES HERE
    '
     ActiveWorkbook.Sheets(7).Select
     lr4 = sheet_four.Cells(Rows.Count, 1).End(xlUp).Row
     lr5 = sheet_five.Cells(Rows.Count, 1).End(xlUp).Row
     lra = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
     ActiveSheet.Range(Cells(7, 1), Cells(lra, 11)).Copy
     sheet_four.Cells(lr4 + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


     ActiveWorkbook.Sheets(8).Select
     lrb = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
     ActiveSheet.Range(Cells(8, 1), Cells(lrb, 16)).Copy
     sheet_five.Cells(lr5 + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     'MsgBox Filename & " has opened"
    wbk.Close True
    Filename = Dir
Loop




Sheets(1).Select
MsgBox "Its done!"
Application.ScreenUpdating = True
Application.DisplayAlerts = False
End Sub





Sub reset()
For i = 1 To ActiveWorkbook.Sheets.Count
lri = Sheets(i).Cells(Rows.Count, 2).End(xlUp).Row
lci = Sheets(i).Cells(1, Columns.Count).End(xlToLeft).Column
    If lri >= 2 Then
    Sheets(i).Select
    Range(Cells(2, 1), Cells(lri, lci)).Clear
    End If
Next i
Sheets(1).Select
End Sub

 

[Meghanakusuma]

when i run this code, it showing debug

 

[RoryA]

Welcome to the forum.

You need to add a path separator (\ on Windows) to the end of your path variable before using it with Dir and Workbooks.Open

 

[Meghanakusuma]

you can explain me clearly. I'm just started to learn VBA. Please

 

[Meghanakusuma]

You mean this like in the path




Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & ""

 

[RoryA]

Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"

 

[Meghanakusuma]

its showing this like

 

[Meghanakusuma]

its showing , can't execute code in break mode

 

[RoryA]

Stop the code and run it again.

 

[Meghanakusuma]

kk