Text file import texttocolumns vs querytable.add

[Bacchus]

Hello,

I have a problem I have been stumbling over for some time. It is not such a huge issue that it needed solving, but now I've got the itch to finally figure it out and could use some help.

I import several text files using the following macro. This allows me to select a directory and all the text files I wish to import. The files are imported as fixed width and each file is put into its own sheet named after the text file. This works wonderfully except some of the files wont import as fixed width. The entire file contents are imported into column A instead of being broken across columns A-D.

Ignore the commented out line for now.

Sub ImportC() 

Application.ScreenUpdating = False

    Dim xFilesToOpen As Variant
    Dim I As Integer
    Dim xWb As Workbook
    Dim xTempWb As Workbook
    On Error GoTo ErrHandler
 

    xFilesToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "3D", , True)
    
    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files were selected", , "3D"
        GoTo ExitHandler
    End If

   
    I = 1
    Set xTempWb = Workbooks.Open(xFilesToOpen(I))
    xTempWb.Sheets(1).Copy
    Set xWb = Application.ActiveWorkbook
    xTempWb.Close False
    
    xWb.Worksheets(I).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), _
      DataType:=xlFixedWidth, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=False, _
      Other:=False
      'fieldinfo:=Array(Array(0, 1), Array(34, 1), Array(23, 1), Array(23, 1))            

    Do While I < UBound(xFilesToOpen)

        I = I + 1
        Set xTempWb = Workbooks.Open(xFilesToOpen(I))
        With xWb
            xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
            .Worksheets(I).Columns("A:A").TextToColumns _
                Destination:=Range("A1"), _
                DataType:=xlFixedWidth, _
                TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, _
                Tab:=False, _
                Semicolon:=False, _
                Comma:=False, _
                Space:=False, _
                Other:=False
                 'fieldinfo:=Array(Array(0, 1), Array(34, 1), Array(23, 1), Array(23, 1))         

        End With

    Loop

ExitHandler:
    Set xWb = Nothing
    Set xTempWb = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description, , "3D"
    Resume ExitHandler

Application.ScreenUpdating = True

End Sub

If a file fails to import correctly after running that above macro, I'll manually import the file. I recorded the process and the code is as follows:

     With ActiveSheet.QueryTables.Add(Connection:="TEXT;filepath\textfile.txt", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "filename"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(33, 24, 24)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With

I have tried adding the fieldinfo parameter to the TextToColumns statement above (it's commented out) to ensure all the imports are set with the correct fixed widths. It works but when I compare the results of the macro with and without that line of code, the imports don't look alike. The column widths are messed up.

So basically the first macro works for most of the files I want to import. When it does not, I manually import the file. I would like to find a solution where each file is imported correctly without manual intervention. I'm not sure if rewriting the macro replacing texttocolumns with querytable makes sense, or how I should go about that?

Unfortunately I cant really provide a text file example because of the data and formatting involved with how they are created.

Thanks for any help
cheers

[Bob Phillips]

If you can't post the files it will be very hard to figure out what is going on.

[p45cal]

try:
FieldInfo:=Array(Array(0, 1), Array(33, 1), Array(57, 1))
you might need to fine tune the numbers a bit.
The first number in each pair is the zero-based position of the start of the column, and they need to be ascending. The second number of each pair is the data type/format (General in this case).

[p45cal]

try:
FieldInfo:=Array(Array(0, 1), Array(33, 1), Array(57, 1))
you might need to fine tune the numbers a bit.
The first number in each pair is the zero-based postion of the start of the column, and they need to be ascending. The second number of each pair is the data type/format (General in this case).
In the above example
column1 will be characters 0 to 32 (that's 33 characters in all)
column2 will be characters 33 to 56 (that's 24 characters)
column3 will be characters 57 to the end (as many characters as are left in the source string).

If you want the 3rd column to be limited to 24 characters then add another pair:
FieldInfo:=Array(Array(0, 1), Array(33, 1), Array(57, 1), Array(81, 9))
which will skip all characters from 81 to the end of the source string.

[Bacchus]

try:
FieldInfo:=Array(Array(0, 1), Array(33, 1), Array(57, 1))
you might need to fine tune the numbers a bit.
The first number in each pair is the zero-based position of the start of the column, and they need to be ascending. The second number of each pair is the data type/format (General in this case).

Oh wow! It is always something simple is it not?

I thought the position parameter for the array was relative to the previous one. I guess I misread the notes on that. After making an adjustment, everything is behaving as expected!

Go raihb maith agat!

[Bacchus]

try:
FieldInfo:=Array(Array(0, 1), Array(33, 1), Array(57, 1))
you might need to fine tune the numbers a bit.
The first number in each pair is the zero-based postion of the start of the column, and they need to be ascending. The second number of each pair is the data type/format (General in this case).
In the above example
column1 will be characters 0 to 32 (that's 33 characters in all)
column2 will be characters 33 to 56 (that's 24 characters)
column3 will be characters 57 to the end (as many characters as are left in the source string).

If you want the 3rd column to be limited to 24 characters then add another pair:
FieldInfo:=Array(Array(0, 1), Array(33, 1), Array(57, 1), Array(81, 9))
which will skip all characters from 81 to the end of the source string.

Yes, that was my issue. I miss understood the array parameter.

Go raihb maith agat!