Need help using VBA to open most recent excel in Sharepoint folder. Dir not working... Runtime 52

[logandiana]

I have a sharepoint folder that will have a new excel file loaded each day.
I want to use VBA to open the most recent excel file in the sharepoint folder.

So far, if I point to the individual file, then there's no problem, works just fine.

Directory = "https://companyx365-my.sharepoint.com/:x:/r/personal/personalusername_companyx_net/Documents/Documents/R%26A%20Report%20Templates/userfiles/SharepointDataFile.xlsx"

Workbooks.Open Directory

But if I try to use Dir then I get an error on the second line

Directory = "https://companyx365-my.sharepoint.com/:x:/r/personal/personalusername_companyx_net/Documents/Documents/R%26A%20Report%20Templates/userfiles/"
filename = Dir(Directory & "*.xlsx")  '<<<<<< CAN'T GET PAST THIS POINT

If filename <> "" Then
    MostRecentFile = filename
    MostRecentDate = FileDateTime(Directory & filename)
Do While filename <> ""
If FileDateTime(Directory & filename) > MostRecentDate Then
    MostRecentFile = filename
    MostRecentDate = FileDateTime(Directory & filename)
End If
filename = Dir
Loop
End If
Workbooks.Open Directory & MostRecentFile

I get a run time error 52, Bad file name or number.

I've done this many times for local folders, but first time trying to pull from Sharepoint.
I am stumped!

 

[logandiana]

Anybody...?

 

[logandiana]

So nobody then? That's a first, I've never stumped this forum before.

 

[rovert_enrobso]

Since the SP directory is in the cloud using the Dir() method won't work unless you mapped the SP directory to a local drive on your machine. I was running into this issue and mapping the SP directory to an available drive was not feasible.

The solution I discovered was to use PowerQuery to connect the base SharePoint site and then you will be able to load all the metadata for the files into your Excel workbook. All you need to do is record a macro and then use the Get Data module to import the SharePoint folder (Data tab on Ribbon -> Get Data -> From File -> From SharePoint Folder -> Insert url for the base SP site)

The columns included in the metadata are Name, Extension, Date accessed, Date modified, Date created, and Folder Path. You can either filter the folder path for the specific folder you are interested and then sort the Date created column in descending order through VBA or you can write the M PowerQuery to do the filtering on the server side and then load in the one single row containing the metadata for the most recent file.

Once you have the metadata loaded into your macro workbook, you can just concatenate the 'Folder path' column and the 'name' column to create the url (using this method I also had to append the string "?web=1" after the file name in order for the path to be correct) and pass it to the Workbooks.Open() method.