OK, from Mynda's webinars, I know how to open a file on a local drive using the
Application.GetOpenFileName method.
And from Google searches, I see how to navigate to a SharePoint folder and select a file via this code.
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "https://"Specific SharePoint Path" & ""
.Show
End With
But the part I don't get, and what I'm trying to do is allow a user to open the file they select and then perform manipulations on it e.g. copy cells from that SharePoint file onto my local file where I'm running the macro from.
I was hoping that after executing the above code and selecting a file, that I could invoke the GetOpenFileName method and grab the file. But when I do just that, I end up selecting my desired Excel file (but then it doesn't do anything further with that file). Next, Excel executes the GetOpenFileName method but just opens up to a local folder on my hard drive and NOT the SharePoint folder. Does anyone know how to bring it all together to allow a user to open their file of interest in a SharePoint folder that is pre-specified for them?
Many thanks.
Hi, the only thing I know when adressing SharePoint links is that you may NOT use spaces but have to replace them with %20
GetOpenFilename doesn't open workbooks. It appears that all you need is to amend the original code to something like this:
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "https://"Specific SharePoint Path" & ""
If .Show = -1 then .Execute
End With
Hi Velouria,
That worked! How succinct and functional! As you can probably tell, I'm a novice Excel VBA programmer. So, I was (and still) unaware of the nuances of the various Excel objects, their properties, and methods.
Many thanks.
Scotty81
Glad we could help. 🙂
