I have written a macro that pulls a csv from a sharepoint folder and copies into a spreadsheet. The csv is deleted and recreated daily using power automate (which is working fine).The issue I am having is that after running the power automate and recreating the csv in sharepoint (which i can see in sharepoint has completed correctly), the macro pull is somehow reading the sharepoint path as the old version before the update (despite the fact it has been deleted and recreated in PA). I presume it is somehow cached somewhere in VBA but i don't know how to get around it. This is needed for a report so I need to csv to be the latest version.

I tried to google the issue but couldn't find anything that seemed to fix the issue.

Dim ws As Worksheet
Dim rng As Range
Dim result As Range
Dim spwb As Workbook
Dim SharePointPath As String
'Pulling from csv
Application.DisplayAlerts = False
Windows("CS Exec Dashboard - Automated.xlsm").Activate
Sheets("Input").Select
ActiveSheet.Cells.Clear
SharePointPath =     "/.../Board_Reporting_database_data.csv"
Set spwb = Application.Workbooks.Open(SharePointPath)` 

I have written a macro that pulls a csv from a sharepoint folder and copies into a spreadsheet. The csv is deleted and recreated daily using power automate (which is working fine).The issue I am having is that after running the power automate and recreating the csv in sharepoint (which i can see in sharepoint has completed correctly), the macro pull is somehow reading the sharepoint path as the old version before the update (despite the fact it has been deleted and recreated in PA). I presume it is somehow cached somewhere in VBA but i don't know how to get around it. This is needed for a report so I need to csv to be the latest version.

I tried to google the issue but couldn't find anything that seemed to fix the issue.

Dim ws As Worksheet
Dim rng As Range
Dim result As Range
Dim spwb As Workbook
Dim SharePointPath As String
'Pulling from csv
Application.DisplayAlerts = False
Windows("CS Exec Dashboard - Automated.xlsm").Activate
Sheets("Input").Select
ActiveSheet.Cells.Clear
SharePointPath =     "https://sharepoint/sites/.../Board_Reporting_database_data.csv"
Set spwb = Application.Workbooks.Open(SharePointPath)` 

• excel
• vba
• sharepoint
• power-automate

• Your computer may be caching the file locally if you've opened it recently. See mrexcel/board/threads/… for one possible solution – Tim Williams Commented Jan 17 at 18:54

1 Answer 1

Adapted from: https://www.mrexcel/board/threads/opening-file-via-url-ignoring-cached-copy.377563/

Private Declare PtrSafe Function DeleteUrlCacheEntry Lib "Wininet.dll" _
      Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long

Sub Tester()
    Dim wb As Workbook
    
    Set wb = GetFileFromUrl( _
       "https://contoso.sharepoint/sites/ABC/TestLibrary/barcodes.xlsx")
   
End Sub



Function GetFileFromUrl(sSourceUrl As String) As Workbook
    If DeleteUrlCacheEntry(sSourceUrl) = 1 Then
        Debug.Print "cached file found and deleted"
    Else
        Debug.Print "no cached file for " & sSourceUrl
    End If
    Set GetFileFromUrl = Workbooks.Open(sSourceUrl)
End Function