Your workflow is unique 👨💻 - tell us how you use Dropbox here.
Forum Discussion
Solved
Upload file doesn't open
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
9 Replies
- Greg-DB
Dropbox Community Moderator
I don't see anything clearly wrong here, but can you clarify what you mean when you say that WinMerge indicates the files are equal but have different encodings? If the files have the same content, the upload must have worked correctly. You might want to try hashing the files to see if they are exactly the same. Comparing the file lengths and even just opening the files as text may be useful points of reference as well. Best answer I can give is this:
- Greg-DBI can't speak to how WinMerge does encoding detection, but it does seem the files are identical, so it seems the Dropbox upload/download worked correctly. Does the original file work correctly in Excel?
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.
Steve M.Dropbox Staff
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.)
- Steve M.
Oh, sorry, I see that you already included ReadBinary. :-) Sorry!
- Steve M.
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- Steve M.
I'm glad you got it sorted out! Thanks for being patient with my lack of VBA knowledge.
About Dropbox API Support and Feedback
Get help with the Dropbox API from fellow developers and experts.
