Download file from API (Web Service) VBA code example

Download file from API (Web Service) VBA code example


Application Programming Interfaces refers to pre-compiled code or procedures which takes inputs and pass it to the server (HOST) for processing. Post processing the inputs, server returns response to the API which it passes back to the end user. In DOT NET It is hardly 4-5 lines code and job is done. But when it comes to lower technology like VBA. ah!!! developer gets real headache.

Code example

Public Sub DownloadFileAndSaveToDeskTop(fileName as string)

	Dim myURL As String
	myURL = ""

	Dim WinHttpReq As Object
	Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
	WinHttpReq.Open "GET", myURL, False, "username", "password"

	myURL = WinHttpReq.responseBody
	If WinHttpReq.Status = 200 Then
		Set oStream = CreateObject("ADODB.Stream")
		oStream.Type = 1
		oStream.Write WinHttpReq.responseBody
		' 1 = no overwrite 
		' 2 = overwrite
		oStream.SaveToFile fileName, 2
	End If
End Sub

Calling method

Public Sub DownloadFileMethod()
	DownloadFileAndSave "C:\myfile.docx"
End Sub

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published. Required fields are marked *