Quote:
Originally Posted by Peterson
You may be able to run the macro if you first sync the SharePoint folder containing your files to File Explorer, then, in File Explorer, determine the file path and use that. When you sync, you get a local, standard file path, not a URL like you get with SharePoint.
For example, if you sync a SharePoint-based folder called Acme Project, then, in File Explorer, the path becomes something along the lines of:
Code:
C:\Users\YourUserName\YourOrganization'sSharePointName\Site Name - Documents\Acme Project\
Sync SharePoint files and folders
Note that ALL of the files you need to run the macro need to be in the sync'd folder
WARNING: If you sync a SharePoint folder to File Explorer and you no longer want the sync, in File Explorer, DO NOT DELETE THE FOLDER -- that deletes it on SharePoint, too. Unsync using the following instructions:
How to cancel or stop sync in OneDrive
Caveats: your org may have restrictions in place that prevent this, your version of SharePoint may work differently then my org's, etc.
If running the macro is a critical part of your workflow and you cannot work on the files locally, then tell your org and maybe they can convert the VBA code to work in another environment, e.g., Office Scripts or Power Automate. |
Thank you very much for your answer. This is the solution I am currently using, but since there are about 50 users, I have to manually update their macros to make this work. We are a school and do not have an it-department that can help us with this.
I've been searching and I think I have a theory of how to resolve it, but I have not yet figured out how to make the code work.
By getting all the user to sync the sharepoint folder, they will all have acess, but instead of making a unique template, is there a way to use EVIRON( HOMEPATH) in this code?
I have managed to get it right in the first part by using this: Dim Env As String
Env = Environ("HOMEPATH")
but in the second part : Private Function xlFillArray(strWorkbook As String, _
strRange As String) As Variant I am not sure what to do