URLDownloadToFile API not working RRS feed

  • Question

  • Hi Team,

    Im just a newbie to vba. I got a scenario to download some files from my company's sharepoint repo. I tried URLDownloadToFile API, it doesn't worked as expected.

    I got file downloaded successfull message but only 11kb of file saved out of 900kb. Please help me guys to fix this.

    Environment : Windows 10, Excel Office 365


    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
    Sub download_file()

    Dim downloadStatus As Long
    Dim url As String, destinationFile_local As String

    url = "sp url"
    destinationFile_local = "C:\Users\VK\Desktop\test.xlsx"
    downloadStatus = URLDownloadToFile(0, url, destinationFile_local, 0, 0)

    If downloadStatus = 0 Then
        MsgBox "Download successfully"
        MsgBox "Download failed"
    End If

    End Sub

    Error Message

    Excel cannot open the file 'test.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the extension matches the format of the file.

    Sunday, February 16, 2020 3:41 PM

All replies

  • Try investigating the downloaded file using some text editor, Notepad, for example. Maybe, instead of a binary Excel file, you will see in "test.xlsx" a text or HTML containing some error message, perhaps related to security stuffs. Maybe the file is not publicly available, and you cannot download it using your code because SharePoint expects a password.

    • Edited by Viorel_MVP Sunday, February 16, 2020 5:06 PM
    Sunday, February 16, 2020 5:06 PM
  • Try using BINDF_GETNEWESTVERSION as in VBnet™ Visual Basic Developers Resource Centre.

    Sam Hobbs

    Sunday, February 16, 2020 6:06 PM