Import, format, convert, print VBA module

Hi All,

I was wondering if its possible to build a module that would do the following: a) import a txt file to excel formatting it as text.
b) then in column D would remove the preceding space.
c) then convert data in D according to a separately kept Conversion table (or conversion table could be integrated into the code) and print conversion results into column J.
d) the last step is to print/copy columns A and J so that it looks like the final table in Sheet2.
Here are files attached.
sample data.txt
sample data.xls
conversion table.xls
To summarize I need to go from a txt file like the one attached and arrive at the table in Sheet2 of xls file attached.

Thank you!

This code might help.... You will have to change according to your needs

Private Sub Workbook_Open()
' Conversion Table
    WSS = 1
    MAS = 2
    W = 3
    S = 4
    MLS = 5

    Dim Filter As String, Title As String
    Dim FilterIndex As Integer
    Dim Filename As Variant
' File filters
    Filter = "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
' Default Filter to *.*
    FilterIndex = 3
' Set Dialog Caption
    Title = "Select a File to Open"
' Select Start Drive & Path
    ChDrive ("C")
    
    With Application
    ' Set File Name to selected File
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
    End With
' Exit on Cancel
    If Filename = False Then
        MsgBox "No file was selected."
        Exit Sub
    End If
    
    'Worksheets("Sheet1").Cells(21, 1) = Worksheets("Sheet1").Cells(1, 1)
    
    Open Filename For Input As #1
    ctr = 20
    Do
    Line Input #1, Data
    ctr = ctr + 1
    Worksheets("Sheet1").Cells(ctr, 1).Value = Trim(Split(Data, ",")(0))
    Worksheets("Sheet1").Cells(ctr, 2).Value = Trim(Split(Data, ",")(1))
    Worksheets("Sheet1").Cells(ctr, 3).Value = Trim(Split(Data, ",")(2))
    Worksheets("Sheet1").Cells(ctr, 4).Value = Trim(Split(Data, ",")(3))
    Worksheets("Sheet1").Cells(ctr, 5).Value = Trim(Split(Data, ",")(4))
    Worksheets("Sheet1").Cells(ctr, 6).Value = Trim(Split(Data, ",")(5))
    Worksheets("Sheet1").Cells(ctr, 7).Value = Trim(Split(Data, ",")(6))
    Worksheets("Sheet1").Cells(ctr, 8).Value = Trim(Split(Data, ",")(7))
    Worksheets("Sheet1").Cells(ctr, 9).Value = Trim(Split(Data, ",")(8))
    Loop While EOF(1) = False
    Close #1
    end_ctr = ctr
    
    Worksheets("Sheet2").Cells(21, 1).Value = "A"
    Worksheets("Sheet2").Cells(21, 2).Value = "J"
    x = 22
    Do
        If Worksheets("Sheet1").Cells(x, 4).Value = "WSS" Then
            Worksheets("Sheet1").Cells(x, 10).Value = WSS
        End If
        If Worksheets("Sheet1").Cells(x, 4).Value = "MAS" Then
            Worksheets("Sheet1").Cells(x, 10).Value = MAS
        End If
        If Worksheets("Sheet1").Cells(x, 4).Value = "W" Then
            Worksheets("Sheet1").Cells(x, 10).Value = W
        End If
        If Worksheets("Sheet1").Cells(x, 4).Value = "S" Then
            Worksheets("Sheet1").Cells(x, 10).Value = S
        End If
        If Worksheets("Sheet1").Cells(x, 4).Value = "MLS" Then
            Worksheets("Sheet1").Cells(x, 10).Value = MLS
        End If
        Worksheets("Sheet2").Cells(x, 1).Value = Worksheets("Sheet1").Cells(x, 1).Value
        Worksheets("Sheet2").Cells(x, 2).Value = Worksheets("Sheet1").Cells(x, 10).Value
        x = x + 1
    Loop While x < end_ctr + 1
    
End Sub

Please edit your post to add code tags.

Please edit your post to use code tags, not quote tags. The forum rules tell all.