VBA Excel Importing Hex data from text

Question

I am trying to import text data for a logged sensor to excel. My issue is the hex values are in 8 bit groups: 01 or FF. When importing the "01" will be treaded as a number and I end up with "1". This makes further data processing more difficult, so I want to preserve the leading zeros on hex values "00" though "09".

The data is in hexadecimal format that is delimited by spaces, for example:

0    000001D0         6  3A  0D  00  00  0C  FE             787.636650 R
0    0000019F         6  0D  05  00  00  00  00             787.637580 R
0    000001D0         6  30  0D  00  00  07  FE             787.638610 R
0    000001D0         6  26  0D  00  00  0C  FE             787.640570 R
0    0000019F         6  0D  05  00  00  00  00             787.642450 R

Importing it into excel I am using this code that allows me to select the file

Sub ImportData()
'-Module for importing data to excel from text log files


    Dim fileToOpen As Variant   'Define the file we want to open, varriant type because it can be boolean or a string
    Dim fileFilterPattern As String 'Define varrable for logic to only open text
    Dim wsMaster As Worksheet   'Define the working worksheet where we are placing the text
    Dim wbTextImport As Workbook    'Define the working workbook we are importing into
    
    Application.ScreenUpdating = False 'speed up program by hiding profile
    ThisWorkbook.Worksheets("RawData").Range("A:A,C:H").Select    'Preemtively selecting the columns for Hex data import
    Selection.NumberFormat = "@"    'formatting selected columns to text to perserver hex data
    
    fileFilterPattern = "Text Files (*.txt;),*.txt"
    
    fileToOpen = Application.GetOpenFilename(fileFilterPattern)    'Assigning the file we are opening
    If fileToOpen = False Then
        'cancled input
        MsgBox "No file selected."
    Else
    
        Workbooks.OpenText _
            Filename:=fileToOpen, _
            StartRow:=1, _
            DataType:=xlFixedWidth, _
            Space:=True
            'Function to open a text file
        
        
        
        Set wbTextImport = ActiveWorkbook   'set a reference to the current text file
        Set wsMaster = ThisWorkbook.Worksheets("RawData") 'define the worksheeet "data" in excel

        
        wbTextImport.Worksheets(1).Columns("B:J").Copy  'this selects the text from the text import from columns B to J for the new worksheet in the defined ranges
        wsMaster.Range("A1").PasteSpecial   'Paste as text
        wbTextImport.Close False    'closes the text file without saving
        
        Application.ScreenUpdating = True
        
    End If

    
End Sub

Please let me know how I should be approaching this.

You need to define the data type (text) during the import process. I suggest using Datatype:=xlDelimited and then specifying FieldInfo for each of the columns. Or just use Power query and define the columns as text type at the time of import. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 26, 2022 at 10:55

Cameron Critchlow · Accepted Answer · 2022-07-26 00:27:39Z

This works:

Option Explicit
Sub ImportData()
'-Module for importing data to excel from text log files


    Dim fileToOpen As Variant   'Define the file we want to open, varriant type because it can be boolean or a string
    Dim fileFilterPattern As String 'Define varrable for logic to only open text
    Dim wsMaster As Worksheet   'Define the working worksheet where we are placing the text
    Dim wbTextImport As Workbook    'Define the working workbook we are importing into
    Dim TEXT As String
    Dim FileIndex As Integer
    Dim I As Integer
    Dim N As Integer
    Dim CNT As Integer
    Dim StringArray
    Dim TempArray
    Dim HexArray
    
    Application.ScreenUpdating = False 'speed up program by hiding profile
    ThisWorkbook.Worksheets("RawData").Range("A:I").Select    'Preemtively selecting the columns for Hex data import
    Selection.NumberFormat = "@"    'formatting selected columns to text to perserver hex data
    
    fileFilterPattern = "Text Files (*.txt;),*.txt"
    
    fileToOpen = Application.GetOpenFilename(fileFilterPattern)    'Assigning the file we are opening
    If fileToOpen = False Then
        'cancled input
        MsgBox "No file selected."
    Else
    
        FileIndex = FreeFile
        Open fileToOpen For Input As FileIndex
        
        
        TEXT = Application.Trim(Input(LOF(FileIndex), FileIndex))
        
        CNT = Len(TEXT) - Len(Replace(TEXT, Chr(10), ""))
        StringArray = Split(TEXT, Chr(10))
        
        ReDim HexArray(CNT, 9)
        ReDim TempArray(9)
        
        For I = 1 To CNT + 1
            TempArray = Split(StringArray(I - 1), " ")
            For N = 1 To 10
                HexArray(I - 1, N - 1) = TempArray(N - 1)
                Debug.Print I & "-" & N & "=" & HexArray(I - 1, N - 1)
            Next N
        Next I
        
        ThisWorkbook.Worksheets("RawData").Range("A1").Resize(UBound(HexArray, 1) + 1, UBound(HexArray, 2) + 1).Value = HexArray
        
        Application.ScreenUpdating = True
        
    End If
    
End Sub

If you still have issues, you can edit each item in array hexarray() to include an apostrophe

Collectives™ on Stack Overflow

VBA Excel Importing Hex data from text

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related