My work has changed over to sharepoint, and now I have to open files through sharepoint instead.

I am using Graham Mayor's code, but I cant figure out how to make it work when my excelfile is located on sharepoint. Any help would be apriciated

Sub Highlight_Words_From_Excel_NamedRange()
'Graham Mayor - Graham Mayor - Home Page - Last updated - 20 Mar 2020
Const strWorkbook As String = "C:\Path\Word List.xlsx" 'The workbook path
Const strRange As String = "WordList" 'The named Excel range
Dim arr() As Variant
Dim lngRows As Long
Dim oRng As Range
Dim strFind As String
arr = xlFillArray(strWorkbook, strRange)
For lngRows = 0 To UBound(arr, 2)
strFind = arr(0, lngRows)
Set oRng = ActiveDocument.Range
With oRng.Find
Do While .Execute(findText:=strFind)


oRng.HighlightColorIndex = wdYellow
oRng.Collapse 0
Loop
End With
Next lngRows
lbl_Exit:
Exit Sub
End Sub

Private Function xlFillArray(strWorkbook As String, _
strRange As String) As Variant
'Graham Mayor - Graham Mayor - Home Page - 24/09/2016
Dim RS As Object
Dim CN As Object
Dim iRows As Long

strRange = strRange & "]"
Set CN = CreateObject("ADODB.Connection")

CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12 .0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"""

Set RS = CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM [" & strRange, CN, 2, 1

With RS
.MoveLast
iRows = .RecordCount
.MoveFirst
End With
xlFillArray = RS.GetRows(iRows)
If RS.State = 1 Then RS.Close
Set RS = Nothing
If CN.State = 1 Then CN.Close
Set CN = Nothing
lbl_Exit:
Exit Function
End Function

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:
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

I have little experience with Sharepoint, but it uses internet style paths rather than Windows filing system paths. As long as the code can access the sharepoint location the appropriate path can be substituted in the code.
What is the result of Environ("HOMEPATH") ?
You cannot however use Environ in the Const strWorkbook. Define strWorkbook as a string variable and set the value of the string to the location of the workbook.

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

Thanks for your reply

The result is C:\Users\currentuserprofile\ , but this is only half of the adress. THe only constant in the adress is what comes after.(for example \files\test.xlsx. The first part has to depend on which user who is logged in.

Is there an way to combine the results of envirion and the string \files\test.xlsx into the second part of your code?

Change the macro as follows:

Note that Environ("HOMEPATH") here does not include C:\. That may be related to Sharepoint which I don't have access to.The alternative will include the C:\. I have added error trapping for the file.

__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

Thank you so very much, this was just what I needed.

To get the full path, i.e., C:\Users\username, use:
This function does not include a backslash at the end, so your code will need to include it: