Loop through a SharePoint folder and open in Excel

[jimmisavage]

Hi all,
I have a VBA that loops through a folder, opening all files and doing some work on them. It works very well sat on an internal shared drive.
We're switching to Office365 so I would like this VBA to loop through the files in a SharePoint and open them in the excel desktop app (I think this is important for my VBA to be able to make the necessary changes to the files?)

My current code looks like this:

Private Sub Workbook_Open()
    
    folderName = "C:\Users\Reports\Updates"
    If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
    fname = Dir(folderName & "*.xlsx")

    Do While Len(fname)

        With Workbooks.Open(folderName & fname)
        
            Select Case ActiveWorkbook.Worksheets("Status").Range("A1")
                Case "Open" ...

I have tried changing the line folderName = "C:\Users\Reports\Updates" with the "copy link" URL from SharePoint but this just gives me a Run-time error '52' Bad file name or number. Can anyone suggest what changes i'd need to make to this code for it to work?

Thanks in advance

 

[jimmisavage]

I've been playing around and still can't get this to work.
I've managed to test and open a single file with this code:

Private Sub NewTest()
Dim wbk As Workbook
Set wbk = Workbooks.Open(Filename:="https://sharepoint.com/filelocation/file.xlsx", UpdateLinks:=False)
End Sub

But no matter what I try i can't get it to work in my original loop to open all files in the folder.
I was hoping something like this would work but i still get the same error:

Private Sub Workbook_Open()
    
    folderName = "https://sharepoint.com/filelocation"
    If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
    fname = Dir(folderName & "*.xlsx", UpdateLinks:=False)

    Do While Len(fname)

        With Workbooks.Open(Filename:=folderName & fname)

Any help would be appreciated.

 

[jimmisavage]

Is anyone able to help with this? I have searched and tried various other suggestions but I cannot get it work. Is this even possible?

 

[jimmisavage]

Is it possible this isn't working because of a permissions issue? I'm logged in to Office 365 on the same PC so it would surly work? Right?

Private Sub Workbook_Open()
    
    folderName = "https://sharepoint.com/filelocation"
    If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
    fname = Dir(folderName & "*.xlsx", UpdateLinks:=False)

    Do While Len(fname)

        With Workbooks.Open(Filename:=folderName & fname)

I have also tried

Private Sub Workbook_Open()
    
    folderName = "\\sharepoint.com\filelocation"
    If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
    fname = Dir(folderName & "*.xlsx", UpdateLinks:=False)

    Do While Len(fname)

        With Workbooks.Open(Filename:=folderName & fname)

 

[jimmisavage]

Is anyone able to help with this? I'm all out of ideas

 

[bgolfb]

Hello, I have came across the same issue today, the VBA should open the excel files from SharePoint site and not from a folder.

Sub RollForward()

oldPlanPath = "\\yourCompany.sharepoint.com\sites\siteName\DepartmentDocuments\Database\LBE Q2 2021\" '--> you can insert spaces, no need for %20
oldFileName = Dir(oldPlanPath)

'// Loop through workbooks
    Do While Len(oldPlanPath) > 0
        counter = counter + 1
    '// Open the workbook
        Set wbToRoll = Workbooks.Open(Filename:=oldPlanPath & oldFileName)

    
    Loop
 
End Sub

from the original path I removed the https: and replaced '/' with '\', run the code and got an error "Bad File Name" at the Dir line.
The problem was that the code could not access SharePoint out of the same reason when you cannot access SharePoint through the Network Locations in "This PC".

So, after I opened the SharePoint site in windows explorer (it mus be IE and not Chrome or Edge), and run the code, it worked.

 

[JEC]

Dir is not able to access sharepoint without some workaround as described above.
Best way is syncing your sharepoint folders to your explorer. There is a sync button at the sharepoint page. Try to sync the highest parent folder.
Afterwards you can access every file like you used to do.