Loop through a SharePoint folder and open in Excel

jimmisavage

Board Regular
Joined
Jun 28, 2017
Messages
130
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:

VBA Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jimmisavage

Board Regular
Joined
Jun 28, 2017
Messages
130
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:

VBA 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:
Code:
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

Board Regular
Joined
Jun 28, 2017
Messages
130
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

Board Regular
Joined
Jun 28, 2017
Messages
130
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?


VBA Code:
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
VBA Code:
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

Board Regular
Joined
Jun 28, 2017
Messages
130

ADVERTISEMENT

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

bgolfb

New Member
Joined
Sep 30, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello, I have came across the same issue today, the VBA should open the excel files from SharePoint site and not from a folder.

VBA Code:
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

Well-known Member
Joined
Aug 21, 2021
Messages
557
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,452
Messages
5,741,206
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top