Hi there,
I'm all Googled out and cannot find a solution to my problem. The scenario is that I have a cell on a sheet that my users can double click on and using the Application.GetOpenFilename dialog box they can choose their latest months source file before running their process. This has been working absolutely fine whilst we have been on FileShares but now we are moving over to SharePoint the Application.GetOpenFilename dialog box does not have an option to navigate to a SharePoint location to choose a file. I know in Excel if I go through the Menu option "File; Open" then I get an option to navigate to "Sites" but I cannot work out how to get to this feature using VBA ....... any prompts or help very much appreciated.
Thanks
Graham
Do you actually open the file they choose after they select it in the dialog, or do you just need the path? Automating the backstage view (File - Open) would actually open it.
Hi - I am just trying to get the path ......
You should be able to use something like this, specifying the relevant SP path in the InitialFileName property:
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "https://somesite.sharepoint.com/yourfolder/"
.AllowMultiSelect = False
.Show
For Each vrtSelectedItem In .SelectedItems
Debug.Print vrtSelectedItem ' adjust this to do whatever you need
Next
End With
Thanks for the reply Velouria ...... that works to a point but what I am really trying to work out is a way for the user to navigate to their SharePoint site without it being pre-defined in anyway in the code. The same as you can if you just go to File; Open in Excel and you have an option to navigate to SharePoint sites (it's about the 5th option down directly below the word "Open"). I'm beginning to think that this isn't supported in VBA ......
Unfortunately I don't think the exact thing you are looking for is possible with VBA. The only place the Sites appear is in Backstage view, not in any of the Open dialogs. I'll do a bit more looking but I wouldn't hold your breath... 😉
Hi Velouria,
I've managed to get a solution working using your code. As long as the main workbook is saved on the SharePoint site this will allow the user to navigate to their source file on the same SharePoint site:
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = ThisWorkbook.Path
.AllowMultiSelect = False
.Show
For Each vrtSelectedItem In .SelectedItems
Debug.Print vrtSelectedItem ' adjust this to do whatever you need
Next
End With
Thank you so much for your help
