Dropbox API Support & Feedback
Find help with the Dropbox API from other developers.
I am trying to upload an .xlsx file using VBA. The upload seems to work and the file appears in dropbox, but it will not preview and once downloaded, will not open in excel. I compared the original and the uploaded files using winmerge. It says the files are equal, though I notice the encoding of the original is 1252 and the uploaded is UTF-8.
Below is my code. Please let me know what is woring.
Public Sub DB_PutFile(FileName As String)
Dim req As MSXML2.ServerXMLHTTP60
Dim strFile As String
Dim Pos1 As Integer
Dim Pos2 As Integer
Set req = New MSXML2.ServerXMLHTTP60
Dim arg As String
strFile= ReadBinary(FileName)
arg = "{""path"":""/" & FileName & """,""mode"":{"".tag"":""overwrite""},""autorename"":false,""mute"":true}"
req.Open "POST", "https://content.dropboxapi.com/2/files/upload", False
req.setRequestHeader "Authorization", "Bearer xxxxxxxxxxxxxxxx"
req.setRequestHeader "Content-Type", "application/octet-stream"
req.setRequestHeader "Content-length", Len(Result)
req.setRequestHeader "Dropbox-API-Arg", arg
req.setRequestHeader "User-Agent", "api-explorer-client"
req.send strFile
If req.Status = 200 Then
Debug.Print req.responseText
Else
'MsgBox req.Status & ": " & req.statusText
Debug.Print req.responseText
End If
End Sub
Public Function ReadBinary(FileName As String) As String
Dim f As Integer
f = FreeFile()
Open Uploadpath & FileName For Binary Access Read Lock Write As #f
ReadBinary = Space(FileLen(Uploadpath & FileName))
Get #f, , ReadBinary
Close #f
End Function
Try this instead? I have a suspicion that returning a String is the issue... the upload sees a string and tries to encode it as UTF-8, leading to the changes to the content making the file unusable. I don't think I've ever used VBA before, so this is code adapted from the web that I have not tested yet. Apologies if there are mistakes!
Private Function ReadBinary(FileName As String) As Byte()
Dim f As Integer
f = FreeFile
Open FileName For Input Access Read As #f
ReadBinary = InputB(LOF(f), f)
Close #f
End Function
Best answer I can give is this:
Original file opens in excel. Original file when uploaded to dropbox manually (not using API) will preview in dropbox and when downloaded, will open in excel.
API uploaded file does not preview. Manually downloading API uploaded file will not open in excel.
Is "ReadBinary" something you wrote yourself? I can't seem to find a definition for it. If you could either share the code or point to the documentation, that would help.
(This sort of error is typically caused by decoding or encoding the file wrong, so I wonder if ReadBinary is doing something incorrectly.)
Oh, sorry, I see that you already included ReadBinary. 🙂 Sorry!
Try this instead? I have a suspicion that returning a String is the issue... the upload sees a string and tries to encode it as UTF-8, leading to the changes to the content making the file unusable. I don't think I've ever used VBA before, so this is code adapted from the web that I have not tested yet. Apologies if there are mistakes!
Private Function ReadBinary(FileName As String) As Byte()
Dim f As Integer
f = FreeFile
Open FileName For Input Access Read As #f
ReadBinary = InputB(LOF(f), f)
Close #f
End Function
Your function didnt work, but your idea did.
I used this function and it worked.
Public Function GetFileBytes(ByVal path As String) As Byte() Dim lngFileNum As Long Dim bytRtnVal() As Byte lngFileNum = FreeFile If LenB(Dir(path)) Then ''// Does file exist? Open path For Binary Access Read As lngFileNum ReDim bytRtnVal(LOF(lngFileNum) - 1&) As Byte Get lngFileNum, , bytRtnVal Close lngFileNum Else Err.Raise 53 End If GetFileBytes = bytRtnVal Erase bytRtnVal
End Function
I'm glad you got it sorted out! Thanks for being patient with my lack of VBA knowledge.
Hi there!
If you need more help you can view your support options (expected response time for a ticket is 24 hours), or contact us on X or Facebook.
For more info on available support options for your Dropbox plan, see this article.
If you found the answer to your question in this Community thread, please 'like' the post to say thanks and to let us know it was useful!