Hi All,
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.
Code
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
Display More
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:
Code
'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
Display More
Any suggestion or assistance will be greatly appreciated. Please let me know if you need any further information.
Thank you in advance.
Kind Regards
CoenieH