Merge Workbooks into one - Help in Folder Picker

[anish.ms]

Hi,

I was trying to change the below pre defined path (Option-1) in the code to a folder pick (Option -2). But, it is not working and i'm unable to figure it out. Somebody please help me, what wrong I'm doing?

Option -1

xStrPath = "C:\Users\anish.ms\Desktop\Sample\"
xStrFName = Dir(xStrPath & "*.xlsx")

Option - 2

xStrPath = Application.FileDialog(msoFileDialogFolderPicker).Show
xStrFName = xStrPath & "*.xlsx"

'Merge Workbooks into one (each worksheet will be named with prefix of its original file name)


Sub MergeWorkbooks()


Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\anish.ms\Desktop\Sample\"
xStrFName = Dir(xStrPath & "*.xlsx")


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ActiveWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop


Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Thanks
Take care, stay safe