VBA uses dialog box to allow choice of folder then allows multi selection of all files in sub folder and open into one excel spreadsheet

[bgrove]

Hi,
I have researched the internet for three day and pieced together this code that gives me a dialog box and shows sub foder on sharepoint but when I select the sub folder to open it the dialogox shows zero files. There is message that appears There are no documents of the specified type in this document library.

When I manually navigate to the sharepoint folder there ARE 25 excel files. Very frustrated!

Here is my code. I would love some help..... Thank you in advance Bev

P.s. I have not gotten to the code that opens all selected files into one work sheet. I'm trying to solve one issue at a time.

Sub test3()


Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Dim vrtSelectedItem As Variant
With fd
.InitialFileName = "https://microsoft.sharepoint.com/teams/folder A/Folder B/ Folder C/"
.Title = "Select files"
.AllowMultiSelect = True
.ButtonName = "Confirm"

If .Show = -1 Then
'ok clicked'
For Each vrtSelectedItem In .SelectedItems
MsgBox vrtSelectedItem
Next vrtSelectedItem
Else
'Cancel Clicked'
End If
End With
''Set the object variable to nothing.
'Set fd = Nothing


End Sub

 

[Trevor G]

Bev try adding in a filter option if it is only the excel workbooks you want to see.

.Filters.Add "Excel", "*.xls;*.xlsm; *.xlsx", 1