How to get MOST RECENT Excel File in Sharepoint using command button VBA?

Question

Below is my code. I just manually edit the time to get the excel on that sharepoint but that file refreshes every 30minutes with new excel. So I want to get the most recent everytime it refreshes without editing the time

Private Sub CommandButton1_Click()
    Dim SharePoint As Workbook
    Dim Desktop As Workbook
  

 Set SharePoint = Workbooks.Open("https://work.sharepoint.com/sites/group/Excel Hourly Wip/Excel Wip as of Jan 27 05_12 PM.xlsm")

 Set Desktop = ThisWorkbook
 
 SharePoint.Sheets("ICM").Range("A2:A20000").Copy
 Desktop.Sheets("ICM").Range("A2:A20000").PasteSpecial
 
    SharePoint.Sheets("ICM").Range("D2:D20000").Copy
 Desktop.Sheets("ICM").Range("B2:B20000").PasteSpecial
    
    SharePoint.Sheets("ICM").Range("G2:G20000").Copy
 Desktop.Sheets("ICM").Range("C2:C20000").PasteSpecial
    
       SharePoint.Sheets("ICM").Range("H2:H20000").Copy
 Desktop.Sheets("ICM").Range("D2:D20000").PasteSpecial
    
           SharePoint.Sheets("ICM").Range("I2:I20000").Copy
 Desktop.Sheets("ICM").Range("E2:E20000").PasteSpecial
 
           SharePoint.Sheets("ICM").Range("W2:W20000").Copy
 Desktop.Sheets("ICM").Range("F2:F20000").PasteSpecial
 
           SharePoint.Sheets("ICM").Range("Z2:Z20000").Copy
 Desktop.Sheets("ICM").Range("G2:G20000").PasteSpecial
 
           SharePoint.Sheets("ICM").Range("AA2:AA20000").Copy
 Desktop.Sheets("ICM").Range("H2:H20000").PasteSpecial
 
           SharePoint.Sheets("ICM").Range("AB2:AB20000").Copy
 Desktop.Sheets("ICM").Range("I2:I20000").PasteSpecial
 
           SharePoint.Sheets("ICM").Range("AE2:AE20000").Copy
 Desktop.Sheets("ICM").Range("J2:J20000").PasteSpecial
 
 
 SharePoint.Close
 
 


End Sub

question from:https://stackoverflow.com/questions/65919591/how-to-get-most-recent-excel-file-in-sharepoint-using-command-button-vba

Answer 1

Waitting for answers