Open an Excel file from SharePoint site

Question

I'm trying to open an Excel file from SharePoint using VBA. Because the file I'm looking for might be different each time I run the macro, I want to be able to view the SharePoint folder and select the file I need.

The code below works fine when I want to look for a file on a network drive, however when I replace that with a SharePoint address I get "run-time error 76: Path not found".

Sub Update_monthly_summary()

Dim SummaryWB As Workbook
Dim SummaryFileName As Variant

ChDir  "http://sharepoint/my/file/path"
SummaryFileName = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select monthly summary file", , False)
If SummaryFileName = False Then Exit Sub

Set SummaryWB = Workbooks.Open(SummaryFileName)

End Sub

When I paste this address into Windows Explorer I have no problems accessing the SharePoint folder, so I know the path is correct.

Why doesn't VBA like it?

`ChDir()` and `GetOpenFilename` don't work over http, but you could try using the sharepoint "webdav" path instead of the http route. — Tim Williams, Oct 21 '13 at 23:49
You need to use a WebDAV address to link the file; Excel will treat it like a network location. See my answer below for a function that parses URLs into WebDAV addresses for you. — Shrout1, Jun 14 '14 at 15:30

ARich · Accepted Answer · 2015-09-10T19:31:33.433

13

Try this code to pick a file from a SharePoint site:

Dim SummaryWB As Workbook
Dim vrtSelectedItem As Variant

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "https://sharepoint.com/team/folder" & "\"
    .AllowMultiSelect = False
    .Show
    For Each vrtSelectedItem In .SelectedItems
        Set SummaryWB = Workbooks.Open(vrtSelectedItem)
    Next
End With

If SummaryWB Is Nothing then Exit Sub

If I remember correctly, the Microsoft Scripting Runtime reference must be enabled. Also, your site may use backslashes, mine uses forward slashes.

edited Sep 10 '15 at 19:31

answered Oct 22 '13 at 04:14

ARich

3,230
5
30
56

When I run this code, at line `.Show` it requires me to "Open file" (open window appears in order to select a file to be opened). Might the problem be in my link? – Flaw98 Jun 19 '19 at 08:16
@Flaw98 The point of this code is to open a file dialog (to manually pick a file). If you're trying to open a specific file without the use of a file dialog, you'll need to use a different approach. – ARich Jun 19 '19 at 18:35
Well, I cannot access the sharepoint location on my pc, I wanted to open the file directly from the sharepoint address, is that possible in a way? – Flaw98 Jun 20 '19 at 05:58
lesson learned: this code can pick a file, without a file dialog you need the `webdav` solution, see further. – Timo Oct 20 '21 at 14:48

Shrout1 · Answer 2 · 2018-03-22T20:51:40.567

I transform the URL into a WebDAV address using the following function I created. This function also returns regular system paths and UNC paths unscathed.

Call this function by adding it into a module in your VBA project and entering MyNewPathString = Parse_Resource(myFileDialogStringVariable) just after your file dialog command and before using the path selected by the file dialog. Then reference "MyNewPathString" when using the target file location.

 Public Function Parse_Resource(URL As String)
 'Uncomment the below line to test locally without calling the function & remove argument above
 'Dim URL As String
 Dim SplitURL() As String
 Dim i As Integer
 Dim WebDAVURI As String


 'Check for a double forward slash in the resource path. This will indicate a URL
 If Not InStr(1, URL, "//", vbBinaryCompare) = 0 Then

     'Split the URL into an array so it can be analyzed & reused
     SplitURL = Split(URL, "/", , vbBinaryCompare)

     'URL has been found so prep the WebDAVURI string
     WebDAVURI = "\\"

     'Check if the URL is secure
     If SplitURL(0) = "https:" Then
         'The code iterates through the array excluding unneeded components of the URL
         For i = 0 To UBound(SplitURL)
             If Not SplitURL(i) = "" Then
                 Select Case i
                     Case 0
                         'Do nothing because we do not need the HTTPS element
                     Case 1
                         'Do nothing because this array slot is empty
                     Case 2
                     'This should be the root URL of the site. Add @ssl to the WebDAVURI
                         WebDAVURI = WebDAVURI & SplitURL(i) & "@ssl"
                     Case Else
                         'Append URI components and build string
                         WebDAVURI = WebDAVURI & "\" & SplitURL(i)
                 End Select
             End If
         Next i

     Else
     'URL is not secure
         For i = 0 To UBound(SplitURL)

            'The code iterates through the array excluding unneeded components of the URL
             If Not SplitURL(i) = "" Then
                 Select Case i
                     Case 0
                         'Do nothing because we do not need the HTTPS element
                     Case 1
                         'Do nothing because this array slot is empty
                         Case 2
                     'This should be the root URL of the site. Does not require an additional slash
                         WebDAVURI = WebDAVURI & SplitURL(i)
                     Case Else
                         'Append URI components and build string
                         WebDAVURI = WebDAVURI & "\" & SplitURL(i)
                 End Select
             End If
         Next i
     End If
  'Set the Parse_Resource value to WebDAVURI
  Parse_Resource = WebDAVURI
 Else
 'There was no double forward slash so return system path as is
     Parse_Resource = URL
 End If


 End Function

This function will check if your file path is a URL and if it is secure (HTTPS) or not secure (HTTP). If it is a URL then it will build the appropriate WebDAV string so that you can link directly to the target file in SharePoint.

The user will likely be prompted for credentials each time the file is opened especially if they are not sitting on the same domain as your SharePoint farm.

PLEASE NOTE: I have not tested this with an http site, however I am confident that it will work.

Also, this answer does not work within Windows XP because Windows XP does not support WebDAV. So Win 7 and newer only. — Shrout1, Oct 15 '14 at 14:38
I still get runtimeerror 52; filename or filenumber wrong with dir(sharepoint). My folder is `\\group.sharepoint.com@ssl\sites\ArbeitsVZ\Freigegebene Dokumente`. Maybe related to the space in Freigegebene Dokumente? — Timo, Oct 21 '21 at 08:16
@Timo That is likely. Also note that this answer is quite old now and it's hard to say what the default configurations for webdav are anymore. This *could* be the result of an authentication / access denied error if the service is disabled, but that is speculation on my behalf. Try quoting your path but really check to see if this service is available at all. The error message may not match the actual underlying issue... — Shrout1, Oct 25 '21 at 14:26

score 2 · Answer 3 · answered Mar 28 '14 at 16:26

2

From you script do not use http://sharepoint/my/file as path but rather \\sharepoint\my\file and then is should work. It works fo my programs done in C#.

answered Mar 28 '14 at 16:26

Jacek

21
1

score 1 · Answer 4 · edited May 23 '17 at 11:46

1

You can use my approach to map SharePoint folder as network drives. Then you can proceed as you did so far.

Excel VBA Up-/Download from multiple SharePoint folders

Then you can also browse through the files with Dir or File System Object

edited May 23 '17 at 11:46

Community

1
1

answered Jun 19 '15 at 20:35

psychicebola

939
1
8
16

You should paste your own response here as here are more visitors and delete the post. – Timo Oct 20 '21 at 15:13

score 1 · Answer 5 · edited Oct 25 '17 at 08:14

1

Please note there is a typo in your initial code

MyNewPathString = ParseResource(myFileDialogStringVariable)

should be replaced with

MyNewPathString = Parse_Resource(myFileDialogStringVariable)

The underscore was missing.

edited Oct 25 '17 at 08:14

Gilles Gouaillardet

8,193
11
24
30

answered Oct 25 '17 at 07:13

Ed van Harmelen

11
2

Thanks for mentioning this! Remember to leave comments as comments and not answers :) I've corrected it in my original response. – Shrout1 Mar 22 '18 at 21:09

score 1 · Answer 6 · answered Feb 05 '20 at 23:54

While this might not work exactly for OP's need to open file dialogue box, this is how I hard-coded opening a workbook stored via SharePoint/Teams which matches the title and possibly what many people end up here looking for.

Get the URL by hitting "Copy Link" and stripping the needed part after "ObjectURL" and before "baseURL".

Sub Test()
Dim URL As String
'Get URL By Coping Link and getting between "ObjectUrl" and "&baseUrl"
'Eg: objectUrl=https%3A%2F%2Fdomain.sharepoint.com%2Fsites%2FName_Teams%2FShared%20Documents%2FGeneral%2FDocuName.xlsx&baseUrl
URL = "https%3A%2F%2Fdomain.sharepoint.com%2Fsites%2FName_Teams%2FShared%20Documents%2FGeneral%2FDocuName.xlsx"
URLDecoded = URLDecode(URL)
'Debug.Print URLDecoded
Set WB = Workbooks.Open(URLDecoded)
End Sub

Public Function URLDecode(StringToDecode As String) As String

Dim TempAns As String
Dim CurChr As Integer

CurChr = 1

Do Until CurChr - 1 = Len(StringToDecode)
  Select Case Mid(StringToDecode, CurChr, 1)
    Case "+"
      TempAns = TempAns & " "
    Case "%"
      TempAns = TempAns & Chr(Val("&h" & _
         Mid(StringToDecode, CurChr + 1, 2)))
       CurChr = CurChr + 2
    Case Else
      TempAns = TempAns & Mid(StringToDecode, CurChr, 1)
  End Select

CurChr = CurChr + 1
Loop

URLDecode = TempAns
End Function

I still get `runtimeerror 52; filename or filenumber wrong` with `dir(sharepoint)`. — Timo, Oct 21 '21 at 08:12
My url is `https://group.sharepoint.com/sites/ArbeitsVZ/Freigegebene Dokumente` — Timo, Oct 21 '21 at 08:22
spaces need to always have %20 as a replacement @Timo so use: https://group.sharepoint.com/sites/ArbeitsVZ/Freigegebene%20Dokumente — DeerSpotter, May 08 '23 at 13:45

score -1 · Answer 7 · answered Oct 21 '13 at 22:24

-1

Try something like this:

Shell ("C:\Program Files\Internet Explorer\iexplore.exe http://sharepoint/my/file/path")

It worked for me.

answered Oct 21 '13 at 22:24

Steve

5,585
2
18
32

Welcome to SO. I never heard of a function `shell`. The explanation `it worked for me` does not help, I am not an animal who gets some food and is happy. I am a soul mate and need some info besides `it worked`. Sorry to be honest. – Timo Oct 21 '21 at 08:25

Open an Excel file from SharePoint site

7 Answers7

Linked