Set default data format to "text" instead of "general"

Hello,
I am currently using VBA code to import multiple .txt and .tab files into excel which bypasses the Import Wizard. Even if I utilize the Import Wizard, I am restricted to one file at a time and have to change each data format field from "general" to "text" which can be very tedious to say the least. I am having another issue with this code importing each header row in the files while I only want one, but will address this in another thread.....As for what I am looking for here.....Simply put..... I would like the following code, or a similar code that does the same thing, to import all fields as "text" not "general".

Sub Import_Tab()

Dim myFolder, TabFile, fso As Object, fPath As String

  ' Turn off some Excel functions that are not needed
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
  ' Import files
    Set fso = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\" Else Exit Sub
    End With
    Set myFolder = fso.GetFolder(fPath).Files
      ' Open each file sequentially
        For Each TabFile In myFolder
            If LCase(TabFile) Like "*.tab" Then
              ' Import data from Text file
                With Workbooks.Open(Filename:=TabFile, Delimiter:=1)
                    With ThisWorkbook.ActiveSheet
                        ActiveSheet.UsedRange.Copy _
                        Destination:=.Cells(.Rows.Count, "A").End(xlUp)(2)
                    End With
                 .Close SaveChanges:=False
                End With
            End If
        Next TabFile
  ' clean up
    TabFile = vbNullString
  ' Turn Excel functions back on
    With Application
      .Calculation = xlCalculationAutomatic
      .DisplayStatusBar = True
      .EnableEvents = True
    End With
End Sub

Please find attached example of workbook and source file.... Any assistance in this matter will be greatly appreciated..... Regards.... Delta

hi Delta, change this code block:

' Import data from Text file
                With Workbooks.Open(Filename:=TabFile, Delimiter:=1)
                    With ThisWorkbook.ActiveSheet
                        ActiveSheet.UsedRange.Copy _
                        Destination:=.Cells(.Rows.Count, "A").End(xlUp)(2)
                    End With
                 .Close SaveChanges:=False
                End With

to this one:

' Import data from Text file
                Workbooks.OpenText Filename:=TabFile, DataType:=xlDelimited, Tab:=True, _
                fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), _
                Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2))
                
                ActiveSheet.UsedRange.Offset(1).Copy ThisWorkbook.ActiveSheet.Cells(Rows.Count, "A").End(xlUp)(2)
                
                ActiveWorkbook.Close 0

Hello again watersev... That’s what I wanted....Is it possible to keep one of the header rows from the imports like you were looking at in my other post? Or am I asking too much? If not I can work with this.....Thanks again for you knowledge, time, and effort….. Regards.....Delta

Sub Import_Tab()

Dim myFolder, TabFile, fso As Object, fPath As String, header As Integer

  ' Turn off some Excel functions that are not needed
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
  ' Import files
    Set fso = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\" Else Exit Sub
    End With
    Set myFolder = fso.GetFolder(fPath).Files
    header = 0
      ' Open each file sequentially
        For Each TabFile In myFolder
            If LCase(TabFile) Like "*.tab" Then
              ' Import data from Text file
                Workbooks.OpenText Filename:=TabFile, DataType:=xlDelimited, Tab:=True, _
                fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), _
                Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2))
                
                ActiveSheet.UsedRange.Offset(header).Copy ThisWorkbook.ActiveSheet.Cells(Rows.Count, "A").End(xlUp)(2)
                
                ActiveWorkbook.Close 0
            End If
            header = 1
        Next TabFile
  ' clean up
    TabFile = vbNullString
  ' Turn Excel functions back on
    With Application
      .Calculation = xlCalculationAutomatic
      .DisplayStatusBar = True
      .EnableEvents = True
    End With
End Sub

Perfect.....You are awesome watersev......Regards....Delta