Text Import Problem

[AlexH]

I have a macro that imports a text file into excel. It works great, but I have one problem. When it imports, all the columns are in the General format. I need the 3rd column to be in Text format because that column has part numbers, and if the part# starts in 0 it removes the zero. Is there a way to make the code below format it to be a Text column when it imports? I've tried adding an array code, but that didn't work

Sub tgr()
    
    ImportDelimitedTextFiles ","
    ActiveSheet.Columns(15).Delete
    
End Sub

Sub ImportDelimitedTextFiles(ByVal sOtherChar As String, _
                                    Optional ByVal bConsecutiveDelimiter As Boolean = False, _
                                    Optional ByVal lTextQualifier As XlTextQualifier = xlTextQualifierDoubleQuote)
    
    Dim ws As Worksheet
    Dim FSO As Object
    Dim strText() As String
    Dim strName As String
    Dim i As Long, j As Long
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = "G:\Airport Laser\schedules"
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt"
        .AllowMultiSelect = False
        .Title = "Select Text Files to Import"
        If .Show = False Then Exit Sub  'Pressed cancel
        Set FSO = CreateObject("Scripting.FileSystemObject")
        For i = 1 To .SelectedItems.Count
            strText = Split(FSO.OpenTextFile(.SelectedItems(i)).ReadAll, vbNewLine)
            strName = Replace(Mid(.SelectedItems(i), InStrRev(.SelectedItems(i), Application.PathSeparator) + Len(Application.PathSeparator)), ".txt", vbNullString)
            For j = 1 To 7
                strName = Replace(strName, Mid(":\/?*[]", j, 1), " ")
            Next j
            strName = Trim(Left(WorksheetFunction.Trim(strName), 31))
            Select Case (Not Evaluate("IsRef('" & strName & "'!A1)"))
                Case True:  Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
                            ws.Name = strName
                Case Else:  Set ws = Sheets(strName)
                            ws.UsedRange.Clear
            End Select
            With ws.Range("A1").Resize(UBound(strText) - LBound(strText) + 1)
                .Value = Application.Transpose(strText)
                .TextToColumns .Cells, xlDelimited, lTextQualifier, bConsecutiveDelimiter, False, False, False, False, True, sOtherChar
            End With
            Erase strText
            Set ws = Nothing
        Next i
        Set FSO = Nothing
    End With
    
Cells.Replace What:="Tube17", Replacement:="Tubematic", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Tube18", Replacement:="Mazak", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[AlphaFrog]

Try something like this...

    .TextToColumns .Cells, xlDelimited, lTextQualifier, bConsecutiveDelimiter, _
    [COLOR=darkblue]False[/COLOR], [COLOR=darkblue]False[/COLOR], False, False, [COLOR=darkblue]True[/COLOR], sOtherChar, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 2))

The FieldInfo argument is the column data format (option buttons on Step 3 of TextToColumns)
Array(3, 2) is third column second option (text)

 

[AlexH]

Thank you very much. I tried that, but when I did it I put it in the wrong place and it didn't work. I'm not sure what went wrong but it kept giving me an expected statement error. I appreciate your help and it works perfectly.