使用VBA和MS XMLHTTP将文件上传到Azure Blob存储

问题描述

我一直在尝试使用Microsoft Access中的VBA将文件上传到Azure存储，但到目前为止没有成功.

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

我在周围进行了很好的搜索，发现了一些看似有希望的代码，但我无法使其正常工作.似乎许多其他人一直在寻找类似的解决方案或在VBA中使用Azure的帮助.

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.

这是代码；

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

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

当我使用表格中的Azure存储URL尝试此代码时

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

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

和文件名(C:\ Temp \ Test.txt)，出现以下错误；

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.

我怀疑标题或帖子数据中存在问题，而不是VBA，这不是我的专长.

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.

推荐答案

我在寻找将图像上传到Azure Blob存储的相同答案时遇到了这篇文章.我花了我两天的时间才能得到答案.上面发布的代码确实帮助我部分解决了问题.

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.

在使用下面的代码之前，您需要从Azure门户(管理面板)获取共享访问签名(SAS).您应该可以在Google上找到答案.

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是一个看起来像URL(我在中国，所以主持人不同): 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

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

一个粗体字是您从Azure生成的SAS令牌.

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

这篇关于使用VBA和MS XMLHTTP将文件上传到Azure Blob存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！