How to download the canonical revision?

Question

In my Excel workbook I use VBA code that calls URLDownloadToFile to download a different Excel file stored in SharePoint to the current folder of the workbook.

The downloaded workbook is not the latest / canonical revision. The link used as a parameter for URLDownloadToFile is the one that links directly to the file, NOT to a specific revision with "_vti_history".

I tried updating the file on SharePoint multiple times to test this but the same old revision was downloaded every time.

Edit: To clarify, the issue. I call the function as

URLDownloadToFile(0, "http://blahblah/file.ext", "C:\blah\file.ext", 0, 0)

The local copy saved is not the latest revision of "file.ext" but an older one.

Welcome to StackOverflow. Please note, that this is not a free code-writing service, but we are eager to help fellow programmers (and aspirants) writing their own code. Please read the help topics on [How To Ask a Good Question](http://stackoverflow.com/help/how-to-ask "How To"). You might also want to take the [Tour](http://stackoverflow.com/tour "Magical Mystery Tour") and earn a badge while doing so. Afterwards, please update your question with the VBA code you have written thus far in order to complete the task(s) you wish to achieve. — Skip Intro, Oct 14 '16 at 10:41
Look at this answer. http://stackoverflow.com/questions/26186279/urldownloadtofile-in-access-2010-sub-or-function-not-defined — Ryan Wildry, Oct 14 '16 at 12:57

score 0 · Accepted Answer · answered Oct 14 '16 at 13:00

If the URL used is the same to download the newest version, it may be that a cached version is being downloaded by URLDownloadToFile.

Try to clear the cache for the link before downloading by using DeleteUrlCacheEntry:

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
Sub downloadFile()
    Call DeleteUrlCacheEntry("http://blahblah/file.ext")
    Call URLDownloadToFile(0, "http://blahblah/file.ext", "C:\blah\file.ext", 0, 0)
End Sub

That was it, thank you very much! It being cached didn't occur to me. — Ludicus, Oct 17 '16 at 05:49

score 0 · Answer 2 · answered Dec 05 '19 at 23:04

I have been trying to figure this out. The function URLDownloadToFile was not working for Sharepoint Online as it couldn't navigate the authentication part. Finally, I found this link which saved my day. The solution that worked for me Version 1 as I didn't have access to the folder view of the file, I only had direct access to the file alone. I created the following function and added the reference to Windows Script Host Object Model library in VBA.

Public Sub VBA_FileCopy(ByVal sSourceFile As String, ByVal sDestinationFile As String)
Dim fs As FileSystemObject

Set fs = New FileSystemObject 'CreateObject("Scripting.FileSystemObject")

sSourceFile = Replace(sSourceFile, "/", "\")
sSourceFile = Replace(sSourceFile, "http:", "")
sSourceFile = Replace(sSourceFile, "https:", "")
sSourceFile = Replace(sSourceFile, Split(sSourceFile, "\")(2), Split(sSourceFile, "\")(2) & "@SSL\DavWWWRoot")
sSourceFile = Replace(sSourceFile, " ", "%20")

fs.CopyFile sSourceFile, sDestinationFile, True
End Sub

How to download the canonical revision?

2 Answers2