CODEWITHSUNDEEP
vbams-word
Toast
Toast

Reputation: 530

Read file to string in VBA

I'm working on a sub that will use an HTTP post to upload a file to a server. I've already worked out all of the code to actually sent the post request, but I can't figure out how to get the file into the appropriate format. The server is expecting the file's bytes as a string, but no matter what approach I take to creating the string the values don't match up with what's expected.

Here is the code I currently have:

Sub Test()
    Dim FileNum As Integer
    Dim TestString As String
    Dim TestBytes() As Byte
    Dim TestByte As Variant

    FileNum = FreeFile
    Open ActiveDocument.Path & "\" & ActiveDocument.name For Binary Access Read As #FileNum    'Open document to write to string
    ReDim TestBytes(LOF(FileNum) - 1)
    Get FileNum, , TestBytes
    Close FileNum
    For Each TestByte In TestBytes
        TestString = TestString & Hex(TestByte)
    Next TestByte
    Debug.Print TestString
End Sub

The output looks something like this (truncated, as the full string is obviously quite long):

504B3414060800021076DD8E8C9D130825B436F6E74656E745F54797065735D2E786D6C20...

The problem is, the example output I have says it should look like this:

UEsDBBQABgAIAAAAIQCuTjGvewEAAAIGAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAACA...

I assumed the problem was that my test code is trying to encode the bytes as hex values, whereas the example is obviously not in hex, but when I just try to output the file's bytes directly as a string I get a lot of invalid characters. Here's the code:

Sub Test()
    Dim FileNum As Integer
    Dim TestString As String
    Dim TestBytes() As Byte
    Dim TestByte As Variant

    FileNum = FreeFile
    Open ActiveDocument.Path & "\" & ActiveDocument.name For Binary Access Read As #FileNum    'Open document to write to string
    TestString = String$(LOF(FileNum), Chr(32))    'Fill string with blank space to set string length
    Get FileNum, , TestString   'Write binary data from file to string
    Debug.Print TestString
End Sub

And here's the output:

PK     ! vÝŽŒ  p    ...

Is there something I'm missing as to how I can encode the bytes to get output that's encoded the same as the example output? When performing a similar operation in another language (e.g. Java using readFileToString), how is the string encoded?

Upvotes: 0

Views: 1715

Answers (1)

Toast
Toast

Reputation: 530

Using Alex K.'s advice in the comments above, and the Base64 encoding function found here, I came to this solution:

Sub Test()
    Dim FileNum As Integer
    Dim TestString As String
    Dim TestBytes() As Byte
    Dim TestByte As Variant

    FileNum = FreeFile
    Open ActiveDocument.Path & "\" & ActiveDocument.name For Binary Access Read As #FileNum    'Open document to write to string
    TestString = String$(LOF(FileNum), Chr(32))    'Fill string with blank space to set string length
    Get FileNum, , TestString   'Write binary data from file to string
    TestString = EncodeBase64(TestString)

    Debug.Print TestString
End Sub

Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As MSXML2.DOMDocument
  Dim objNode As MSXML2.IXMLDOMElement

  Set objXML = New MSXML2.DOMDocument
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text

  Set objNode = Nothing
  Set objXML = Nothing
End Function

Which outputs the correct string:

UEsDBBQABgAIAAAAIQCuTjGvewEAAAIGAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAACA...

Upvotes: 1

Related Questions