CODEWITHSUNDEEP
sharepointvbscript
Quant Analyst
Quant Analyst

Reputation: 21

Download file from Sharepoint via Visual Basic Script

I am trying to download a file from sharepoint via VBScript.

Unfortunately i get the following errormessage after downloading the Excel- File:

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

My code snipped is the following:

dim bStrm: Set bStrm = createobject("Adodb.Stream")
xHttp.Open "GET", "https://company.sharepoint.com/:x:/r/teams/xxxxx/_layouts/15/Doc.aspx?sourcedoc=%7B5AA396CB-4711-4E73-AEC5-3CB8B6E174D3%7D&file=xxxx%20Automation%xxxx.xlsx&wdOrigin=OFFICECOM-WEB.START.REC&action=default&mobileredirect=true", False
xHttp.Send

with bStrm
    .type = 1 '//binary
    .open
    .write xHttp.responseBody
    .savetofile "c:\temp\TestNow.xlsx", 2 '//overwrite
end with

Upvotes: 2

Views: 3427

Answers (1)

user692942
user692942

Reputation: 16672

The approach isn't wrong but when dealing with downloads from a URL you should always check the Response Status Code to see if it is valid before continuing.

After calling Send() always surround the response in a conditional statement like this;

If xHttp.Status = 200 Then 'Expecting a HTTP 200 OK response
    With bStrm
        .Type = 1 '//binary
        .Open
        .Write xHttp.responseBody
        .SaveToFile "c:\temp\TestNow.xlsx", 2 '//overwrite
    End With
Else
    'Check the response body for details of the error.
    MsgBox("Unexpected response: " & xHttp.Status & " - " & xHttp.StatusText, 48, "Error")
End If

Because you are trying to download from a SharePoint site the likelihood is the request is failing and returning an HTTP 403 Forbidden for not providing any authentication.

Upvotes: 1

Related Questions