Here's a slight modification:
Option Explicit
Sub test()
Dim mystring$, r&, arr
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayStatusBar = False
.Calculation = xlCalculationManual
End With
On Error Resume Next
' Open text file as array
Open "C:\Users\FamilyServer\Desktop\TxtFile.txt" For Input As #1
arr = Split(Input(LOF(1), #1), vbCrLf)
Close
' Create string from re-arranged columns
For r = 0 To UBound(arr)
If Application.IsNumber(Trim(--Left(arr(r), Len(arr(r)) - 1))) Then mystring = mystring & arr(r) Else mystring = mystring & arr(r) & vbCrLf
Next
' Print to sheet row by row
arr = Split(mystring, vbCrLf)
For r = 0 To UBound(arr)
Cells(r + 1, 1) = arr(r)
Next
' Text to columns with comma as delimiter
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True
' Remove unneeded columns
Columns("Q:AB").Delete Shift:=xlToLeft
Columns("L:N").Delete Shift:=xlToLeft
Columns("J:J").Cut
Columns("M:M").Insert Shift:=xlToRight
Columns("E:H").Delete Shift:=xlToLeft
Columns("A:I").AutoFit
With Application
.EnableEvents = True
.DisplayStatusBar = True
.Calculation = xlAutomatic
End With
End Sub
The results should look like this:
---A---- ----B---- ---C--- ----D---- ----E----- ---F---- ----G----- H -I--
1 55555555 111111111 BARKER RICHARD 01/01/1963 11111111 02/07/2006 M 1554
2 55555555 111111111 BARKER RICHARD 01/01/1963 11111111 02/07/2006 M 1554
3 55555555 111111111 BARKER RICHARD 01/01/1963 11111111 02/07/2006 M 1554
4 55555555 111111111 BARKER RICHARD 01/01/1963 11111111 02/07/2006 M 1554
5 55555555 111111111 BARKER RICHARD 01/01/1963 11111111 02/07/2006 M 1554
6 55555555 111111111 BARKER RICHARD 01/01/1963 11111111 02/07/2006 M 1554
7 55555555 111111111 BARKER RICHARD 01/01/1963 11111111 02/07/2006 M 1554
8 55555555 111111111 BARKER RICHARD 01/01/1963 11111111 02/07/2006 M 1554
9 7777777 666666666 CHOATE CAASSSSS 01/01/1963 99999999 02/08/2006 F 941
10 7777777 666666666 CHOATE CAASSSSS 01/01/1963 99999999 02/08/2006 F 941
I have also attached the text file and excel sheet used for testing.
Now as for Access, I don't yet know enough about it to help you, but if this works out for you, then you can post the final code in a new thread on the Access subforum (or a forum dedicated to Access) for further assistance. I have converted code in the past between Excel and Access and it shouldn't be too hard.
Hope this helps.
abousetta
P.S. So what specialty are you in?
Bookmarks