Text file Array - then Import

[jcw5107]

Below is code that works just awesome in Excel. I need some help in doing a smiliar thing in Access... I'm pretty sure it can be done, just not sure how to manipulate this code or find a post that I can use...
Below is an example of the text file, and below that is the code from an Excel macro....
Any suggestions or examples I can use..??
Thanks in advance..!!
jcw5107

From: Doe John (xxxxxx)
Sent: Tuesday, March 13, 2007 11:46 AM
To: Doe John (xxxxxx)
Subject: Parts Required For Scheduled Work


United Parcel Service
03/13/07 15:46 04:04 JCW Page: 1
Parts Required For Scheduled Work

Tail Schd Schd Eng Task Chgd By Added By Task
No Gtwy Date Ref Number Rev Pos ApndxApndxApndxApndx NoV? Date Date No
====================================================================================================================================
N120UP KCGN 03/24/07 EO A300-3210-22610 C E 798639N 03/12/07 15:54 02/27/07 20:28 0
------------------------------------------------------------------------------------------------------------------------------------
N120UP KCGN 03/24/07 TC 724100-P1-1-L-460 S 397034N 02/27/07 20:58 02/27/07 20:58 0
------------------------------------------------------------------------------------------------------------------------------------
N120UP KCGN 03/24/07 TC 725300-P2-1-L-460 S 397041N 02/27/07 21:00 02/27/07 21:00 0
------------------------------------------------------------------------------------------------------------------------------------
N120UP KCGN 03/24/07 TC 792203-P4-1-L-460 S 397073N 03/12/07 15:55 02/27/07 20:30 0
------------------------------------------------------------------------------------------------------------------------------------
N120UP KCGN 03/24/07 TC 801301-P1-1-L-460 S 397075N 03/12/07 15:55 02/27/07 21:01 0
------------------------------------------------------------------------------------------------------------------------------------
N124UP KCGN 03/24/07 CK PERIODIC SERVICE 12 S 396908N 03/12/07 16:58 02/27/07 21:19 0
PS12 IS EQUIVALENT TO THE P12 LISTED ON THE TALLY SHEET


Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(4, 1), Array(7, 1), Array(11, 1), _
Array(22, 1), Array(46, 1), Array(47, 1), Array(63, 1), Array(64, 1), Array(83, 1), Array( _
92, 1), Array(93, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("D").EntireColumn.AutoFit
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<=999", Operator:=xlAnd
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("H:K").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Needs Reviewed"
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Range("B1").Select
ActiveCell.FormulaR1C1 = "Aircraft Type"
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Range("C1").Select
ActiveCell.FormulaR1C1 = "Number"
Columns("D").Select
Columns("C:C").EntireColumn.AutoFit
Range("D1").Select
ActiveCell.FormulaR1C1 = "Gateway"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Old Gateway"
Range("E1").Select
Columns("D").EntireColumn.AutoFit
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "New Gateway"
Columns("F:F").Select
Columns("E:E").EntireColumn.AutoFit
Range("F1").Select
ActiveCell.FormulaR1C1 = "Date Scheduled"
Columns("F:F").Select
Columns("F:F").EntireColumn.AutoFit
Range("G1").Select
ActiveCell.FormulaR1C1 = "EO Number"
Columns("G:G").Select
Columns("G:G").EntireColumn.AutoFit
Range("H1").Select
ActiveCell.FormulaR1C1 = "Rev Level"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Eng Position "
Columns("I:I").Select
Columns("I:I").EntireColumn.AutoFit
Range("J1").Select
ActiveCell.FormulaR1C1 = "Task"
Columns("K:K").Select
Columns("J:J").EntireColumn.AutoFit
Sheets("Formulas").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("B:B").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="<=999", Operator:=xlAnd
Cells.Select
Selection.Copy
Sheets("Sheet3").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Type"
Range("C3").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Planning Task"
Rows("1:1").Select
Selection.AutoFilter
Range("A3").Select
'
End Sub

 

[CautionMP]

jcw5107,
Create a linked table that points to your source file. When you run through the wizard make sure you choose Fixed Width and set the column boundries the same as [tt]FieldInfo[/tt] arguments from above.

This will give you a table with your data and all the other 'junk'. To replicate your sort run a query against the linked/imported table that only pulls records where the first field (column) starts with N [tt][Field] Like "N*"[/tt].

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)