Uploading a file to Azure Blob Storage using VBA and MS XMLHTTP

Question

I've been trying to upload file to Azure storage using VBA in Microsoft Access but so far without success.

I have had a good search around and have found some code which looks promising but I can't get it to work. Seems like many others have been looking for a similar solution or help with working with Azure from VBA.

This is the code;

Private Function pvPostFile(sUrl As String, sFileName As String, Optional ByVal bAsync As Boolean) As String
Const STR_BOUNDARY  As String = "3fbd04f5-b1ed-4060-99b9-fca7ff59c113"
Dim nFile           As Integer
Dim baBuffer()      As Byte
Dim sPostData       As String

'--- read file
nFile = FreeFile
Open sFileName For Binary Access Read As nFile
If LOF(nFile) > 0 Then
    ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
    Get nFile, , baBuffer
    sPostData = StrConv(baBuffer, vbUnicode)
End If
Close nFile
'--- prepare body
sPostData = "--" & STR_BOUNDARY & vbCrLf & _
    "Content-Disposition: form-data; name=""uploadfile""; filename=""" & Mid$(sFileName, InStrRev(sFileName, "\") + 1) & """" & vbCrLf & _
    "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & _
    sPostData & vbCrLf & _
    "--" & STR_BOUNDARY & "--"
'--- post
With CreateObject("Microsoft.XMLHTTP")
    .Open "POST", sUrl, bAsync
    .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & STR_BOUNDARY
    .Send pvToByteArray(sPostData)
    If Not bAsync Then
        pvPostFile = .ResponseText
    End If
End With
End Sub

Private Function pvToByteArray(sText As String) As Byte()
    pvToByteArray = StrConv(sText, vbFromUnicode)
End Function

(Thanks to - https://wqweto.wordpress.com/2011/07/12/vb6-using-wininet-to-post-binary-file/)

When I try this code using my azure storage URL in the form

https://XXXXX.blob.core.windows.net/

and a filename (C:\Temp\Test.txt) I get the following error;

<?xml version="1.0" encoding="utf-8"?><Error><Code>UnsupportedHttpVerb</Code><Message>The resource doesn't support specified Http Verb.

I suspect there's a problem in the header or post data rather than the VBA and this is not really my area.

Any help greatly appreciated.

I believe there are a few problems: 1) The URL should be https://XXXXX.blob.core.windows.net/container-name/file-name. 2) All the requests to Azure Storage should be authenticated. I don't see you setting an Authorization header anywhere in your code. — Gaurav Mantri
– Gaurav Mantri, Commented Oct 22, 2015 at 16:50

ThisaruG · Accepted Answer · 2017-07-29 07:01:01Z

3

I came across this post as I'm searching the same answer for uploading images to Azure Blob Storage. I took me 2 days to get the answer. And the code posted above did help me to partly solve the problem.

I would like to post my solution here in case anyone else is looking for the same answer.

Before you can use the code below, you need to get the Shared Access Signature (SAS) from your Azure portal (manage panel). You should be able to google the answers on this.

Public Sub UploadAfIle(sUrl As String, sFileName As String)
    Dim adoStream As Object
    Set adoStream = CreateObject("ADODB.Stream")
    adoStream.Mode = 3          ' read write
    adoStream.Type = 1          ' adTypeBinary
    adoStream.Open
    adoStream.LoadFromFile (sFileName)
    With CreateObject("Microsoft.XMLHTTP")
        adoStream.Position = 0
        .Open "PUT", sUrl, False
        .setRequestHeader "Content-Length", "0" 'this is not a must
        .setRequestHeader "x-ms-blob-type", "BlockBlob"
        .Send adoStream.Read(adoStream.Size)
    End With
    Set adoStream = Nothing
End Sub

sURL is a URL looks like (I'm in China so the Host is different): https://myaccount.blob.core.chinacloudapi.cn/products/newimagename.jpg?sv=2016-05-31&ss=bfpq&srt=dco&sp=rydlscup&se=2017-07-30T18:40:26Z&st=2017-07-28T10:40:26Z&spr=https&sig=mJgDyECayITp0ivVrD4Oug%2Bz%2chN7Wpo2nNtcn0pYRCU%4d

The one bolded is the SAS token you generated from Azure.

edited Jul 29, 2017 at 7:01

ThisaruG

3,4327 gold badges43 silver badges66 bronze badges

answered Jul 29, 2017 at 4:00

SiliconXu

1164 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jhTuppeny Over a year ago

Thanks for following up and posting this - I'll take a look.

jhTuppeny Over a year ago

I've managed to get this working - thanks again for posting it. Do you know the header type when working with an Azure file share instead of blob storage? The "x-ms-blob-type", "BlockBlob" generates an error when trying to upload to a fileshare

SiliconXu Over a year ago

Good to know that this is working for you. I don't know the header type for Azure File Share. But I think "x-ms-blob-type" should be "x-ms-blob-content-type".

Taher A. Ghaleb · Accepted Answer · 2018-12-06 00:53:42Z

3

Worth noting the format of the sUrl in SiliconXu's answer is made up of 3 parts, I didn't realise at first so got a sore head for a while!!

1) Azure blob container URL (from the properties in the Storage Explorer) 2) the filename (this is the part I omitted by mistake) 3) Shared Access Signature

So is built like this

sURL = destination_folder & "/" & local_file_name & "?" & conn_SAS

I don't have the reputation require to comment directly below that answer

Great code though, as soon as I worked out the format of the sURL it worked like a dream

edited Dec 6, 2018 at 0:53

Taher A. Ghaleb

5,2905 gold badges33 silver badges47 bronze badges

answered Dec 5, 2018 at 18:26

Stuart Kirkup

15512 bronze badges

Comments

markti · Accepted Answer · 2015-10-23 07:41:45Z

-1

Azure Storage Service uses private key authentication. Since VBA runs on the end user's machine you are exposing yourself to a whole slew of risks associated with that key getting into the wild. I would recommend rethinking the whole premise of going directly from VBA to Azure Storage and utilize your own WebAPI to handle storing data to Blob.

This would have the dual benefit of:

1) making it easier to integrate with from VBA and

2) protecting your Azure Storage private key behind a component of your solution that doesn't get deployed to the end user's machine.

answered Oct 23, 2015 at 7:41

markti

4,5342 gold badges38 silver badges54 bronze badges

5 Comments

jhTuppeny Over a year ago

Thanks for your answer. Security isn't really an issue with this application. What I want to avoid is having to install any additional components on the end user's machine. What would I use to create a WebAPI? (I'm a VBA programmer and my knowledge of other languages is limited)

markti Over a year ago

If you want to build one with VB.NET you could start with a simple ASP.NET Web Application configured. Select WebAPI 2.0. Then you'd simply implement a controller to upload the file to. Check out this basic tutorial link. As long as the files you are uploading aren't huge you should be okay.

markti Over a year ago

If somebody other than you is going to be using the application I'd still be concerned. If your Azure Storage key fell into the wrong hands and you weren't paying attention they could rack up a pretty huge bill for you.

jhTuppeny Over a year ago

Thanks for your follow up - I'll take a look. I take your point about the security but in this particular instance it really wouldn't be a problem.

jhTuppeny Over a year ago

After a lot of trying to get this working directly from VBA I have found a Microsoft tool that can be called from the command line that works for this application. The tool is called AzCopy and handles both uploads and downloads of files from Azure storage.

Collectives™ on Stack Overflow

Uploading a file to Azure Blob Storage using VBA and MS XMLHTTP

3 Answers 3

3 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related