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
- 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
- xStrFName = Dir()
- 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.