Opening file via URL - ignoring cached copy

[IanOz]

[Hi all - my first post so please forgive any noobness]

I have a worksheet with an auto_open() sub which opens another workbook via a URL to manipulate some data.

Application.Workbooks.Open "http://myUrl/datafile.xls", Password:=myPwd


That works OK, but I've found that if the user's PC is not connected to the internet then it will use a cached copy of the "datafile.xls" workbook. I had expected it to give me an error code, but that only happens if the file hasn't been opened before..... (no cached copy).

Can anyone suggest a way to either ignore the cached copy, or to delete the cached copy of the data workbook from the user's PC before trying to open the URL?

Thanks

 

[IanOz]

Have found a way to do what I needed, so thought I'd post it here for anyone else.

But if anyone knows a simpler way to say "ignore the cache" when opening a workbook from a URL I'd appreciate it.



This will delete any cached copy of the file before opening it, to ensure that the user is getting the latest information instead of an old cached version.

courtesy of http://vbnet.mvps.org/index.html?code/internet/urldownloadtofilenocache.htm



---------------------------------------------------------------------

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


Sub OpenTheFile()

Dim sSourceUrl As String
sSourceUrl = "http://myUrl/myFile.xls"

If DeleteUrlCacheEntry(sSourceUrl) = 1 Then
Debug.Print "cached file found and deleted"
Else
Debug.Print "no cached file for " & sSourceUrl
End If

Application.Workbooks.Open sSourceUrl, Password:="myPassword"

'
' rest of my code
'

End Sub

------------------------------------------------------------------------