VBA Import all lines of data from a variable text file

[Lelewiwi]

I have a text file which includes lines of text as shown below:

21599999902230123129285103920952123456789DOE, JOHN 0000000000000000000000000000000000000000000000000000000000000000000P019680201202112080000000000164221642200085013959
21599999902230123129326103860439123456788SMITH, JANE D0000000000000000000000000000000000000000000000000000000000000000000P019811011202111090000000000086460864600030002594

There can be an unlimited amount of lines in this text file. I have a working macro to split the information the way I need but I would like to add some vba to import the information automatically. Text file name will change every time and the import needs to keep each individual line separate from the previous. Number of characters per line can also vary. Insert into Excel will start at cell A1.

Currently, I am opening the text file, Ctrl A, copying and manually pasting into Excel, then I run the current macro which splits the data into columns and sheets.

Thanks for the help!

 

[rlv01]

Depending on the details, there is likely several ways to do this. One way:

Sub FileOpenText1()
    Dim PickCancelled As Boolean
    Dim FilePathName As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt", 1
        .Title = "File Choice"
        .AllowMultiSelect = False
        
        PickCancelled = Not CBool(.Show)
        
        If PickCancelled Then
            FilePathName = ""
            MsgBox "User Cancelled"
            Exit Sub
        Else
            FilePathName = .SelectedItems(1)
        End If
    End With
    
    Workbooks.OpenText Filename:=FilePathName, Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
    
    'Run your macro to split the data

End Sub

 

[Lelewiwi]

Works great until the last line when I get an error "Method 'OpenText' of object 'Workbooks' failed."

 

[rlv01]

I just tested it again, and it works for me. Try this version with fewer .OpenText parameters/

Sub FileOpenText1()
    Dim PickCancelled As Boolean
    Dim FilePathName As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt", 1
        .Title = "File Choice"
        .AllowMultiSelect = False
        
        PickCancelled = Not CBool(.Show)
        
        If PickCancelled Then
            FilePathName = ""
            MsgBox "User Cancelled"
            Exit Sub
        Else
            FilePathName = .SelectedItems(1)
        End If
    End With
    
    Workbooks.OpenText Filename:=FilePathName, DataType:=xlFixedWidth
        
    'Run your macro to split the data
    
End Sub