Results 1 to 8 of 8

Thread: read bynary data from non excel file and post the same in a excel sheet

  1. #1

    read bynary data from non excel file and post the same in a excel sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am Trying to read data as Bytes from a non Excel File by opening it as Binary and write the same data in Bytes in an excel file through the following VB Program.I am able to Read Successfully from the non Excel file but the data in read in Bytes is not getting posted in the Sheet4(Not the Active Sheet) of the Currently Active Excel workbook.I will be thankful if any body help me in this regard.please note that i am new to EXCEL VBA.
    Function RWfile (FilePath As String)
    Dim Filenumber As Integer
    Dim Byet(10) As Byte
    Dim I As Integer
    Set fso = CreateObject("Scripting.FilesystemObject")
    If fso.Fileexists(Filepath) Then
    Filenumber = FreeFile
    Open Filepath For Binary Access Read Write As #Filenumber
    For I = 1 To 10
    Get #Filenumber, , Byet(I)
    Sheets("Sheet4").Cells(I, 1).Value = Byet(I)
    Next M
    End If
    End Function

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,502
    Articles
    0
    Excel Version
    365
    Next M
    should probably be
    Next I

  3. #3

    Read binary data from non excel file and post the same in an excel sheet.

    I have realised this mistake immediately after posting this.However things did not improve even after correction.

    I have introduced a message box inside the loop and found out that while the loop is reading the binary data and assigning the values to the array (Byet) correctly,the code statement "Sheets("Sheet4").Cells(I,1)=Byet(I)" is not performing.

    I Have also introduced the statement "Sheets.("Sheet4").Activate" before the loop.

    I have also introduced the statement "Sheets("Sheet4").Range("A1")=Byet" after the loop by removing "Sheets("Sheet4").Cells(I,1)=Byet(I)".

    All these changes did not improve the performance.

    Can any body help me in overcoming this.

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    If you are new to VBA you'd better not use this code, without studying the fundamentals of VBA first.

  5. #5
    Thanks for the help guru

  6. #6
    DS
    With Binary files you must know precisely how the data is stored in the file to retrieve it . not just open a non-excel file but a specific binary file

    Code:
    Sub test()
        Range("A10").Resize(10, 1).Value = RWfile("YOUR FILE FULL PATH AND NAME")
    End Sub
    Function RWfile(FilePath As String) As Variant
        Dim Filenumber As Integer
        Dim Byet(1 To 10) As Byte
        Dim I      As Integer
        Set fso = CreateObject("Scripting.FilesystemObject")
        If fso.Fileexists(FilePath) Then
            Filenumber = FreeFile
            Open FilePath For Binary Access Read Write As #Filenumber
            For I = 1 To 10
                Get #Filenumber, , Byet(I)
            Next I
        End If
        Close #1
        RWfile = Application.Transpose(Byet)
    End Function

  7. #7

    Read binary data from non excel file and post the same in an excel sheet.

    Dear sir,

    Thank you for your reply.

    1.I know the structure of the binary file i am tryng to read.
    2.I have posted in Cell(A1) of sheet1 of my workbook the filepath along with name and posted in Cell(B1) of the same sheet the function call "=RWfile(A1)".However i got in Cell(B1) a resulting Value "73", which is ofcourse is the value of the first byte in the file.But as per the subtest() Value of all the 10 Bytes are to be posted from Cell(A10) to Cell(J10).Kindly let me know the mistake i am commiting.

  8. #8
    DS two ways

    Option One
    with full path and name in A1.
    In B1 =INDEX(RWfile(A$1),ROW(1:10)) and fill down to B10

    with UDF

    Code:
    Function RWfile(FilePath As String) As Variant
        Dim Filenumber As Integer
        Dim Byet(1 To 10) As Byte
        Dim I      As Integer
        Set fso = CreateObject("Scripting.FilesystemObject")
        If fso.Fileexists(FilePath) Then
            Filenumber = FreeFile
            Open FilePath For Binary Access Read Write As #Filenumber
            For I = 1 To 10
                Get #Filenumber, I, Byet(I)
            Next I
        End If
        Close #1
       RWfile = Application.Transpose(Byet)
    End Function

    Option Two
    with full path and name in A1.
    Run the sub test
    Code:
    Sub test()
         RWfile (Range("A1"))
    End Sub
    
    Function RWfile(FilePath As String) As Variant
        Dim Filenumber As Integer
        Dim Byet(1 To 10) As Byte
        Dim I      As Integer
        Set fso = CreateObject("Scripting.FilesystemObject")
        If fso.Fileexists(FilePath) Then
            Filenumber = FreeFile
            Open FilePath For Binary Access Read Write As #Filenumber
            For I = 1 To 10
                Get #Filenumber, I, Byet(I)
            Next I
        End If
        Close #1
        Range("B1").Resize(10, 1).Value = Application.Transpose(Byet)
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •