Is it possible to read a file from share point directory in a macro?

[cubangt]

I found this post on this site: https://www.excelforum.com/excel-pro...nt-folder.html

But doesn't help, I'm working on a template that will eventually reside within Teams and seems all files are saves to a sharepoint folder.

Yesterday I setup and got working a Power Automation flow that monitors my inbox for a email from our requests server, when that email arrives, the flow gets triggered and the attachment in that email is saved to a specific folder within our teams (sharepoint path) this works great and was pretty quick and simple to setup.

Now comes my template, my current macro works if i run it against a local folder, it picks up the file, copies the data into the template and does its work..
I'm trying to merely change where that file is "read"/"opened" from.

Even if this template is not saved in TEAMS, the macro still needs to work off the file in teams as its source.

For testing purposes, I'm running the following macro:

Please Login or Register  to view this content.

And there is no file to upload as I'm not getting past getting to a file to process.. so the above macro is in a blank file. If you place a breakpoint on the While Filename <> "" line, the error will appear on the line above it.

[Andy Pope]

The DIR function will not work. You would need to map the sharepoint folder to a drive first.

As you know the name of the workbook you could write a function that users error trapping to detect the failure to open the workbook.
Simplest way to get the correct filepath is to manually open a file within the sharepoint folder and then use this code in the intermediate window

Please Login or Register  to view this content.

[cubangt]

Can a drive be mapped within the macro so that it can be used, then unmapped once the macro is completed?

[Andy Pope]

Yes. 1 result from google
https://www.access-programmers.co.uk...ac2007.268572/

But as I mentioned in your case you know the folder and file so only need to open it, and use error handling if not present.

[cubangt]

Thanks for the link i had already viewed and tested those examples, thats why i had made my post. All the functions work to get the mapped drives and paths but the map function never worked using either the
https://xxxxxxxx.sharepoint.com or the unc \\xxxxxxxx.sharepoint.com

Im testing it this morning, but are you saying i should be able to just call Workbooks.Open ("https://xxxxxxxx.sharepoint.com") and as long as i have the proper error handling that should work for what i need to do? No need to map a drive?

[Austris]

Yes, you can do workbook.open directly with a sharepoint link.
What you also can consider - is adding the SharePoint folder to your OneDrive - in that case you're 'sharepoint' files become local via your onedrive and that one you can operate with as usual network file.

[cubangt]

well if it was only me working within sharepoint i would, but im building a template for a whole department and each person has their own folder / file that they need to use to populate the template. So looking for as much automation as possible.

[cubangt]

I finally tested and it worked and was as simple as this:

Please Login or Register  to view this content.

It was able to open and return the value in A1 for my test, so now im working on incorporating it into my macro and adding the error handling
Thank you for the suggestions.