Open excel files from sharepoint by using word wba

gomezaka · #3 11-03-2021, 05:08 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
hyperlink to open pdf files in excel	sharathnarayanan	Excel	8	06-21-2021 03:50 AM
Cannot open some Excel and Word files.	melvin1942	Excel	1	11-17-2019 04:41 PM
Excel Office 2010 Files will not open outside of excel	stevetag	Office	3	06-22-2014 07:23 PM
Excel 2011 can't open old Excel 98 or Excel X files	FLJohnson	Excel	8	05-09-2012 11:26 PM
Excel Files Will Not Open From Documents Normally	tremaine	Excel	1	06-17-2009 08:05 AM