VBA Open excel file from Sharepoint with changing name

Question

I am trying to generate some code that will open up an excel file on Sharepoint and copy data into another spreadsheet. I have been using the below code when the files were saved on my laptop and it worked great, however it does not appear to work when I use hppt for the sharepoint location. I suspect it has to do with the dir function. Is there an easy way I can alter the below code so it will work on a sharepoint address? The name of the excel file will change with a different month each time I run the macro.

Dim sourceworkbook As Workbook
Dim currentworkbook As Workbook
Dim FolderPath As String
Dim file As String
Set currentworkbook = ThisWorkbook

FolderPath = ("C:\Users\XXXXXX\Desktop\XX\")
file = Dir("C:\Users\XXXXXX\Desktop\XX\" & "WAS*.xls")
Do While file <> vbNullString
Set sourceworkbook = Workbooks.Open(FolderPath & file)
file = Dir()
Loop
    sourceworkbook.Sheets("ABC").Range("A1:A15").Copy
    currentworkbook.Sheets("END").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    sourceworkbook.Close SaveChanges:=False

Application.ScreenUpdating = True

Answer 1

I don't know if in your environment you have the necessary kind of authentication, but have you tried mapping the Sharepoint folder location to one of your network drives? Then you can you can access your file just as it was in any other folder.