0

I'm trying to loop through all files in a Teams SharePoint folder, but I cannot get the FileSystemObject to recognize the address. First, I tried using the copy link for the folder in teams and then using this:

Dim fso As New FileSystemObject
Dim fldr As Folder: Set fldr = fso.GetFolder("address for sharepoint folder copied above")

But it always tells me it can't find it. I also tried something very similar for exact files in that folder Dim fl As File: Set fl = fso.GetFile("address") but still wouldn't work

Then I googled and found this link: Get the content of a sharepoint folder with Excel VBA but it still errors on oNetwork.MapNetworkDrive DriveLetter & ":", NetworkPath when trying to find that location.

I also tried replacing the / with \ and removing the https: but still no good. I've also tried getting the link from within Teams and within SharePoint. Still can't find it. If I put an Excel file in that folder and tell excel to open it with Application.Workbooks.Open("address") that will open an excel file. But I just can't get the Dir or FileSystemObject to recognize the address. I'm suspicious it might have something to do with not using the correct address but for the life of me I can't find another address link in SharePoint. I'd really appreciate any help someone can give me.

I'm specifically trying to do this in MS Access but also tested in Excel with the same results.

EDIT: I ended up punting on this one. What I ended up doing is have the user select the "Add shortcut to OneDrive" and throw an error message if they haven't and giving instructions on how to do so with the message. There wont' be a lot of users needing this specific feature, so it should be okay for my needs.

Jason Brady
  • 1,560
  • 1
  • 17
  • 40
  • You cannot use web link URLs for file system objects. Find the file system UNC network path that may begin with double forward slashes. A quick check is select an object (like the Excel workbook) in that folder using Explorer and holding shift right click *Copy as Path* and paste into text editor to get absolute path of file. – Parfait Jan 22 '22 at 17:57
  • Thank you, I'll see if I can find the UNC path for me folder and use that instead. – Jason Brady Jan 24 '22 at 15:20

1 Answers1

1

What you're trying to do would probably have worked in some situations for on premises SharePoint, as it would probably have used the very old WebDav protocol under the covers, but it's not possible/applicable for SharePoint Online (the cloud SharePoint that powers Teams) and you should user the modern Microsoft 365 approach to doing this. It's definitely more work, especially because there's increased security when you're in the cloud (which is a good thing), but it means there's a learning curve around this.

Essentially, the main API for anything Microsoft cloud is the Microsoft Graph. Specifically, for getting files you want to work with Drives and DriveItems (an abstraction around both SharePoint and OneDrive, depending on where the file(s) live). Here is a good starting point for you: https://learn.microsoft.com/en-us/graph/api/resources/onedrive?view=graph-rest-1.0

This also looks relevant: https://learn.microsoft.com/en-us/graph/api/channel-get-filesfolder?view=graph-rest-1.0&tabs=http

But of course you also need some background in using Graph, authenticating, etc., so see here: https://learn.microsoft.com/en-us/graph/overview?view=graph-rest-1.0. The security context you need will depend on whether you're running on behalf of a user (e.g. when the user is in a tab in Teams), which is called 'delegated' permissions, or whether you have a background service/process running (like a weekly process doing something), which would required 'application' permissions.

Hilton Giesenow
  • 9,809
  • 2
  • 10
  • 24