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

logandiana

Board Regular
Joined
Feb 21, 2017
Messages
107
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.

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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top