I have multiple users that will access a workbook and run a macro to combine a number of sheet in a single worksheet. Therefore I need to allow each user to browse their own folder where to the relevant sheets are stored. I have the following piece of code that allows a user to browse their folder.
Sub browseFolderPath() On Error GoTo err Dim fileExplorer As FileDialog Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker) 'To allow or disable to multi select fileExplorer.AllowMultiSelect = False With fileExplorer If .Show = -1 Then 'Any folder is selected [folderPath] = .SelectedItems.Item(1) Else ' else dialog is cancelled MsgBox "You have cancelled the dialogue" [folderPath] = "" ' when cancelled set blank as file path. End If End With err: Exit Sub End Sub
The result is then stored in sheet "A" cell "B11" which I then want to use in the vba code (only portion of the full code) below as folder path. As mentioned before, this path will change for each user and cannot be static:
'Combine all workbooks into one and combine all worksheets into one Set table = Sheets("Combined").ListObjects("Table10") With table 'Delete 1st data row to last data row .DataBodyRange.Rows("1:" & .ListRows.Count).Delete 'Clear first 3 cells on 2nd data row ' .DataBodyRange(2, 1).Resize(1, 3).ClearContents End With On Error Resume Next xStrPath = "C:\Users\Joe.Bloggs\Desktop\Daily Tag Board Reports\New Tap Events Report\" xStrFName = Dir(xStrPath & "*.xlsx") Application.ScreenUpdating = False Application.DisplayAlerts = False Set xTWB = ThisWorkbook 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
Any suggestion or assistance will be greatly appreciated. Please let me know if you need any further information.
Thank you in advance.