Open excel files from sharepoint by using word wba

gmayor · #1 11-03-2021, 09:37 PM

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.

gomezaka · #2 11-04-2021, 07:04 AM

Quote:

Originally Posted by gmayor

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.

Peterson · #3 11-04-2021, 08:23 AM

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

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