Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-27-2021, 02:37 AM
gomezaka gomezaka is offline Open excel files from sharepoint by using word wba Windows 10 Open excel files from sharepoint by using word wba Office 2019
Novice
Open excel files from sharepoint by using word wba
 
Join Date: Oct 2021
Posts: 4
gomezaka is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 10-31-2021, 09:16 PM
Peterson Peterson is offline Open excel files from sharepoint by using word wba Windows 10 Open excel files from sharepoint by using word wba Office 2019
Competent Performer
 
Join Date: Jan 2017
Posts: 133
Peterson is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 11-03-2021, 05:08 AM
gomezaka gomezaka is offline Open excel files from sharepoint by using word wba Windows 10 Open excel files from sharepoint by using word wba Office 2019
Novice
Open excel files from sharepoint by using word wba
 
Join Date: Oct 2021
Posts: 4
gomezaka is on a distinguished road
Default

Quote:
Originally Posted by Peterson View Post
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
Reply With Quote
  #4  
Old 11-03-2021, 07:26 AM
gmayor's Avatar
gmayor gmayor is offline Open excel files from sharepoint by using word wba Windows 10 Open excel files from sharepoint by using word wba Office 2019
Expert
 
Join Date: Aug 2014
Posts: 3,829
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

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
Reply With Quote
  #5  
Old 11-03-2021, 07:44 AM
gomezaka gomezaka is offline Open excel files from sharepoint by using word wba Windows 10 Open excel files from sharepoint by using word wba Office 2019
Novice
Open excel files from sharepoint by using word wba
 
Join Date: Oct 2021
Posts: 4
gomezaka is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
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.
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?
Reply With Quote
  #6  
Old 11-03-2021, 09:37 PM
gmayor's Avatar
gmayor gmayor is offline Open excel files from sharepoint by using word wba Windows 10 Open excel files from sharepoint by using word wba Office 2019
Expert
 
Join Date: Aug 2014
Posts: 3,829
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

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
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
Reply With Quote
  #7  
Old 11-04-2021, 07:04 AM
gomezaka gomezaka is offline Open excel files from sharepoint by using word wba Windows 10 Open excel files from sharepoint by using word wba Office 2019
Novice
Open excel files from sharepoint by using word wba
 
Join Date: Oct 2021
Posts: 4
gomezaka is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
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
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.
Thank you so very much, this was just what I needed.
Reply With Quote
  #8  
Old 11-04-2021, 08:23 AM
Peterson Peterson is offline Open excel files from sharepoint by using word wba Windows 10 Open excel files from sharepoint by using word wba Office 2019
Competent Performer
 
Join Date: Jan 2017
Posts: 133
Peterson is on a distinguished road
Default

To get the full path, i.e., C:\Users\username, use:
Code:
Environ("USERPROFILE")
This function does not include a backslash at the end, so your code will need to include it:
Code:
strWorkbook = Environ("userprofile") & "\" & "YOUR PATH HERE\Word List.xlsx" ' The workbook path
Reply With Quote
Reply

Thread Tools
Display Modes


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
Open excel files from sharepoint by using word wba Excel Office 2010 Files will not open outside of excel stevetag Office 3 06-22-2014 07:23 PM
Open excel files from sharepoint by using word wba Excel 2011 can't open old Excel 98 or Excel X files FLJohnson Excel 8 05-09-2012 11:26 PM
Open excel files from sharepoint by using word wba Excel Files Will Not Open From Documents Normally tremaine Excel 1 06-17-2009 08:05 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:06 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft