#1
|
|||
|
|||
Open excel files from sharepoint by using word wba
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 |
#2
|
|||
|
|||
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\ 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. |
#3
|
|||
|
|||
Quote:
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 |
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
Quote:
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? |
#6
|
||||
|
||||
Change the macro as follows:
Code:
Sub Highlight_Words_From_Excel_NamedRange() 'Graham Mayor - https://www.gmayor.com - Last updated - 04 Nov 2021 Const strRange As String = "WordList" 'The named Excel range Dim strWorkbook As String Dim arr() As Variant Dim lngRows As Long Dim oRng As Range Dim strFind As String Dim FSO As Object strWorkbook = Environ("HOMEPATH") & "\files\test.xlsx" 'or 'strWorkbook = Environ("USERPROFILE") & "\files\test.xlsx" Set FSO = CreateObject("Scripting.FileSystemObject") If FSO.FileExists(strWorkbook) Then 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 Else MsgBox "The file '" & strWorkbook & "' does not exist!", vbCritical End If lbl_Exit: Set oRng = Nothing Set FSO = Nothing Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
To get the full path, i.e., C:\Users\username, use:
Code:
Environ("USERPROFILE") Code:
strWorkbook = Environ("userprofile") & "\" & "YOUR PATH HERE\Word List.xlsx" ' The workbook path |
|
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 |