none
Convert bytes to text in excel RRS feed

  • Question

  • I'm trying to convert a file to bytes and then show the bytes on a worksheet in a cell.  I have found code to convert a file into bytes, but I can't figure out how to show those bytes on the worksheet like:  73  58  61  0  0  0  2......  Is there some sort of method to "display" or "show" the actual byte numbers.  Also, I would prefer to show the bytes in one cell as opposed to individually in cells going down a column like:

    73

    58

    61

    etc.

    Although, either way would be appreciated.  Thanks.

    Public Sub Example()
        Dim bytFile() As Byte
        bytFile = GetFileBytes("c:\test\dirdump.doc")
        ''// Do something with bytFile here.
    End Sub
    
    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
    


    • Edited by Luna1200 Saturday, October 25, 2014 7:32 PM
    Saturday, October 25, 2014 7:28 PM

Answers

  • You could simply loop through the returned byte array and build a string adding some sort of padding between each element, or maybe something like this

    ReDim strArr(0 To UBound(bytFile)) As String
    For i = 0 To UBound(bytFile)
        strArr(i) = bytFile(i)
    Next
    Range("A1") = Join(strArr, ",")
    


    • Marked as answer by Luna1200 Sunday, October 26, 2014 5:03 PM
    Sunday, October 26, 2014 10:43 AM
    Moderator

All replies

  • You could simply loop through the returned byte array and build a string adding some sort of padding between each element, or maybe something like this

    ReDim strArr(0 To UBound(bytFile)) As String
    For i = 0 To UBound(bytFile)
        strArr(i) = bytFile(i)
    Next
    Range("A1") = Join(strArr, ",")
    


    • Marked as answer by Luna1200 Sunday, October 26, 2014 5:03 PM
    Sunday, October 26, 2014 10:43 AM
    Moderator
  • Thanks.  At first I was getting a "Run-time error '9': Subscript out of range"  But then I changed

    For i = 0 To UBound(bytFile)

    to

    For i =1 to Ubound(bytFile)

    Then it worked.  I also changed

    ReDim strArr(0 To UBound(bytFile)) As String

    to

    Redim strArr(1 To Ubound(bytFile)) As String

    This took away the comma before the first byte for some reason.  I also had do add "dim i as integer".  I should mention too, that I ended up using different code to convert the file to bytes.  Here's the final code that ended up working:

    Private Sub CommandButton5_Click()
    showbytes
    End Sub
    
    Sub showbytes()
    Dim bytes() As Byte
    Dim FileNum As Integer
    Dim Var() As Variant
    
    Const Gif_Path_Name As String = "C:\a.gif"
    FileNum = FreeFile
    ReDim bytes(1 To FileLen(Gif_Path_Name))
    Open Gif_Path_Name For Binary As #FileNum
    Get #FileNum, 1, bytes
    Close FileNum
        
    With Worksheets("Gif_Bytes")
    Dim i As Integer
    ReDim strArr(1 To UBound(bytes)) As String
    For i = 1 To UBound(bytes)
        strArr(i) = bytes(i)
    Next
    Range("E1") = Join(strArr, ",")
    End With
    End Sub

    Sunday, October 26, 2014 5:27 PM