Copy content from all files stored on a SharePoint Folder

[filipipo]

Dear All,

Hope you're all well and safe.

I am building a dashboard that will pull data from stored files in a SharePoint Folder.

All files are identical in terms of columns and rows, only the data differs. All files extension is ."xlsx". There can be only one file but could have 10 files or even more with individual names at some stage.

The code should loop through all files within that folder, open one by one, copy data and paste it to the "Mater" file and close it.

The code I am using is the following:

Sub MasterList_Update()

Dim sFile As String           'file to process
Dim wsTarget As Worksheet
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim iRowReg As Long
Dim FOLDER_PATH As String

   'check the folder exists
If Not FileFolderExists(FOLDER_PATH) Then
    MsgBox "Specified folder does not exist, exiting!"
Exit Sub
End If

'reset application settings in event of error
'On Error GoTo errHandler
Application.ScreenUpdating = False

'set up the target worksheet
Set wsTarget = Sheets("MasterList_DB")

FOLDER_PATH = "https://sap.sharepoint.com/teams/CSS-DeliveryTransformationatIberia/Shared Documents/Customer Success Room/Heat Map Dashboard Test/MasterList" & "\"

FOLDER_PATH = Replace(FOLDER_PATH, "/", "\")
FOLDER_PATH = Replace(FOLDER_PATH, "https:", "")
FOLDER_PATH = Replace(FOLDER_PATH, " ", "%20")

'loop through the Excel files in the folder
sFile = Dir(FOLDER_PATH & "*.xlsx*")
Do Until sFile = ""

    'open the source file and set the source worksheet - ASSUMED WORKSHEET(1)
    Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
    Set wsSource = wbSource.Sheets(1) 'EDIT IF NECESSARY

    iRowReg = wsTarget.Range("A" & Rows.Count).End(xlUp).Row + 1

    With wsTarget
        .Cells(iRowReg, 1).Resize(198, 1).Value = wsSource.Range("A3:A200").Value 'BP Number
        .Cells(iRowReg, 2).Resize(198, 1).Value = wsSource.Range("B3:B200").Value 'BP Name
        .Cells(iRowReg, 3).Resize(198, 1).Value = wsSource.Range("C3:C200").Value 'Region
        .Cells(iRowReg, 4).Resize(198, 1).Value = wsSource.Range("D3:D200").Value 'Sub_Region
    End With
    
    'close the source workbook, increment the output row and get the next file
    wbSource.Close SaveChanges:=False
    sFile = Dir()
Loop

errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True
   
   'tidy up
   Set wsSource = Nothing
   Set wbSource = Nothing
   Set wsTarget = Nothing
    
End Sub

Private Function FileFolderExists(strPath As String) As Boolean
    If Not Dir(strPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function

The issue I am having with the above code is on "sFile = Dir(FOLDER_PATH & "*.xlsx*")". It gives me an "Bad file name or number" error.

Anyone with experience with Excel and SharedPoint?

Your assistance on this matter will be highly appreciated.

Thanks a million and be safe!

 

[GWteB]

I have no experience with SharePoint, but I do know that Windows cannot handle urls like this directly. One option might be to mount a drive to this network path. If you have admin rights on your computer you can do this in the cmd console as follows (it's worth the try...):
C:\> NET USE * "https://sap.sharepoint.com/teams/CSS-DeliveryTransformationatIberia/Shared Documents/Customer Success Room/Heat Map Dashboard Test/MasterList" YOURPASSWORD /USER:username
If you're lucky the last not used drive letter will be mounted (Z: ) and Windows will be able to perform disk i/o operations on that drive (Excel included).

 

[filipipo]

I have no experience with SharePoint, but I do know that Windows cannot handle urls like this directly. One option might be to mount a drive to this network path. If you have admin rights on your computer you can do this in the cmd console as follows (it's worth the try...):
C:\> NET USE * "https://sap.sharepoint.com/teams/CSS-DeliveryTransformationatIberia/Shared Documents/Customer Success Room/Heat Map Dashboard Test/MasterList" YOURPASSWORD /USER:username
If you're lucky the last not used drive letter will be mounted (Z: ) and Windows will be able to perform disk i/o operations on that drive (Excel included).

Thanks a million for your reply!
The issue here is that more than a user will be using this dashboard to pull data, and that includes management.
This code works well with folders stored on a pc/laptop but not SharePoint.
But thanks for your input, really appreciated.
Best regards,
Filipe

 

[GWteB]

Pitty it didn't work for you and your team. Thanks for letting me know.

 

[filipipo]

Pitty it didn't work for you and your team. Thanks for letting me know.

Found a workaround, even better than I expected.

Within SharePoint, there is an option to export the folder into Excel. The file is an Excel query that you can add the sheet into your own workbook and refresh as you want and the info is updated instantly.

Then you just work your way within that sheet as it has folder and files path for all formats.

This worked better than I expected.

Hope this will help future searches for assistance on this area as I see loads of them around with apparent solution.

Best regards,
Filipe Oliveira

 

[GWteB]

Thanks for letting me know. I will point another forum member to your findings.