Child Item

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

Find more posts tagged with

API

Abuse

Accepted answers

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!

<SPAN class="kwd">Private</SPAN> <SPAN class="kwd">Function</SPAN><SPAN class="pln"> ReadBinary</SPAN><SPAN class="pun">(FileName</SPAN> <SPAN class="kwd">As</SPAN> <SPAN class="kwd">String</SPAN><SPAN class="pun">)</SPAN> <SPAN class="kwd">As</SPAN> <SPAN class="kwd">Byte</SPAN><SPAN class="pun">()</SPAN>
    <SPAN class="kwd">Dim</SPAN><SPAN class="pln"> f </SPAN><SPAN class="kwd">As</SPAN> <SPAN class="kwd">Integer</SPAN><SPAN class="pln">

    f </SPAN><SPAN class="pun">=</SPAN><SPAN class="pln"> FreeFile
    Open FileName </SPAN><SPAN class="kwd">For</SPAN><SPAN class="pln"> Input Access Read </SPAN><SPAN class="kwd">As</SPAN> <SPAN class="pun">#f</SPAN>
<SPAN class="pln">    ReadBinary </SPAN><SPAN class="pun">=</SPAN><SPAN class="pln"> InputB</SPAN><SPAN class="pun">(</SPAN><SPAN class="pln">LOF</SPAN><SPAN class="pun">(</SPAN><SPAN class="pln">f</SPAN><SPAN class="pun">),</SPAN><SPAN class="pln"> f</SPAN><SPAN class="pun">)</SPAN>
<SPAN class="pln">    Close </SPAN><SPAN class="pun">#f</SPAN>
<SPAN class="kwd">End</SPAN> <SPAN class="kwd">Function</SPAN>

All comments

Greg-DB

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.

ERS R.

Best answer I can give is this:

Error

Greg-DB

I 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?

ERS R.

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.

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.

<SPAN class="kwd">Private</SPAN> <SPAN class="kwd">Function</SPAN><SPAN class="pln"> ReadBinary</SPAN><SPAN class="pun">(FileName</SPAN> <SPAN class="kwd">As</SPAN> <SPAN class="kwd">String</SPAN><SPAN class="pun">)</SPAN> <SPAN class="kwd">As</SPAN> <SPAN class="kwd">Byte</SPAN><SPAN class="pun">()</SPAN>
    <SPAN class="kwd">Dim</SPAN><SPAN class="pln"> f </SPAN><SPAN class="kwd">As</SPAN> <SPAN class="kwd">Integer</SPAN><SPAN class="pln">

    f </SPAN><SPAN class="pun">=</SPAN><SPAN class="pln"> FreeFile
    Open FileName </SPAN><SPAN class="kwd">For</SPAN><SPAN class="pln"> Input Access Read </SPAN><SPAN class="kwd">As</SPAN> <SPAN class="pun">#f</SPAN>
<SPAN class="pln">    ReadBinary </SPAN><SPAN class="pun">=</SPAN><SPAN class="pln"> InputB</SPAN><SPAN class="pun">(</SPAN><SPAN class="pln">LOF</SPAN><SPAN class="pun">(</SPAN><SPAN class="pln">f</SPAN><SPAN class="pun">),</SPAN><SPAN class="pln"> f</SPAN><SPAN class="pun">)</SPAN>
<SPAN class="pln">    Close </SPAN><SPAN class="pun">#f</SPAN>
<SPAN class="kwd">End</SPAN> <SPAN class="kwd">Function</SPAN>

ERS R.

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.