importing data into access 2

This is the code I have done in access (ignore some of the file direction links). I will put one more post up that continues this code
Public Sub speedCounts_Click()
SelectOpenCopySpeed
'preAccessSpeed
End Sub


Sub SelectOpenCopySpeed()

Dim i As Long
Dim vaFiles As Variant

vaFiles = Application.GetOpenFilename("TBL Files (*.tbl), *.tbl", _
Title:="Select files", MultiSelect:=True)

addNewSpeed

If IsArray(vaFiles) Then
For i = LBound(vaFiles) To UBound(vaFiles)

dataTransferSpeed (vaFiles(i))

Next i
End If

End Sub


Sub addNewSpeed()
Set NewBook = Workbooks.Add
With NewBook
.Title = "Converted TBL Files"
.Subject = "Traffic Counter Database"
End With
Application.DisplayAlerts = False
Worksheets("sheet2").Delete
Worksheets("sheet3").Delete
Application.DisplayAlerts = True
Worksheets(Sheets(1).Name).Name = "completedCounterData"

'puts in column headers

Worksheets("completedCounterData").Range("A1").Select
ActiveCell.FormulaR1C1 = "recordDates"
Worksheets("completedCounterData").Range("B1").Select
ActiveCell.FormulaR1C1 = "recordTimes"
Worksheets("completedCounterData").Range("C1").Select
ActiveCell.FormulaR1C1 = "Class"
Worksheets("completedCounterData").Range("D1").Select
ActiveCell.FormulaR1C1 = "Speed"
Worksheets("completedCounterData").Range("e1").Select
ActiveCell.FormulaR1C1 = "Unclass"
Worksheets("completedCounterData").Range("F1").Select
ActiveCell.FormulaR1C1 = "directionID"
Worksheets("completedCounterData").Range("G1").Select
ActiveCell.FormulaR1C1 = "description"
Worksheets("completedCounterData").Range("H1").Select
ActiveCell.FormulaR1C1 = "counterID"
Worksheets("completedCounterData").Range("I1").Select
ActiveCell.FormulaR1C1 = "fileNumber"
Worksheets("completedCounterData").Range("J1").Select
ActiveCell.FormulaR1C1 = "area"
Worksheets("completedCounterData").Range("K1").Select
ActiveCell.FormulaR1C1 = "site"
Worksheets("completedCounterData").Range("L1").Select
ActiveCell.FormulaR1C1 = "location"
Worksheets("completedCounterData").Range("M1").Select
ActiveCell.FormulaR1C1 = "downloadDate"

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Steve\Desktop\counterDB\tempSpeedRTC.XLS"
Application.DisplayAlerts = True

End Sub

Sub dataTransferSpeed(strWbkName As String)

Dim wbkToCopy As Workbook
Set wbkToCopy = Workbooks.Open(Filename:=strWbkName)
Workbooks.OpenText Filename:= _
strWbkName, Origin:=932, StartRow:=1 _
, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=True, Other:=False, OtherChar:=":", FieldInfo:=Array(Array(1, 2 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1)), TrailingMinusNumbers:=True
createSheets
formatSheetsSpeed
dataSortSpeed

Worksheets("fileData").Activate

Worksheets("fileData").Select.SelectAll
Selection.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:="false"
Application.DisplayAlerts = True

Workbooks("tempSpeedRTC.xls").Activate
Worksheets("completedCounterData").Activate
Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste


Worksheets("completedCounterData").Activate
Worksheets("completedCounterData").Cells.Select
Selection.EntireColumn.AutoFit


End Sub

Sub createSheets()
'creates a new worksheet and separates the data between the fileSpec & fileData worksheets
Sheets(Sheets(1).Name).Name = "fileData"
Sheets.Add
Sheets("Sheet1").Name = "fileSpec"
Worksheets("fileData").Range("1:12").Cut
ActiveSheet.Paste Destination:=Worksheets("fileSpec").Range("A1")
End Sub


Sub formatSheetsSpeed()

'formats the fileData worksheet
Worksheets("fileData").Activate

Worksheets("fileData").Activate
Worksheets("fileData").Rows("1:13").Select
Selection.Delete Shift:=xlUp
Worksheets("fileData").Rows("2:2").Select
Selection.Delete Shift:=xlUp
Worksheets("fileData").Range("A1").Select
Selection.Delete Shift:=xlToLeft


End Sub

Sub ****()

'Shift the appropriate data between forms
Worksheets("fileData").Activate
Worksheets("fileData").Range("G2").Select
ActiveCell.FormulaR1C1 = "=DAY(RC[-6])"
Worksheets("fileData").Range("H2").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-7])"
Worksheets("fileData").Range("I2").Select
ActiveCell.FormulaR1C1 = "=YEAR(fileSpec!R[5]C[-2])"
Worksheets("fileData").Range("J2").Select
ActiveCell.FormulaR1C1 = "=HOUR(RC[-8])"
Worksheets("fileData").Range("K2").Select
ActiveCell.FormulaR1C1 = "=MINUTE(RC[-9])"
Worksheets("fileData").Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=0,CONCATENATE(RC[-2],RC[-1])*10,CONCATENATE(RC[-2],RC[-1]))"
Worksheets("fileData").Range("L2").Select
Selection.Cut Destination:=Range("M2")
Worksheets("fileData").Range("L2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-5],R1C7,RC[-4],R1C7,RC[-3])"
Worksheets("fileData").Range("G1").Select
ActiveCell.FormulaR1C1 = "/"
Worksheets("fileData").Range("G2:M2").Select
Selection.AutoFill Destination:=Range("G2:M200")
Worksheets("fileData").Range("G2:M1589").Select
Worksheets("fileData").Range("I2").Select
ActiveCell.FormulaR1C1 = "=YEAR(fileSpec!R7C7)"
Worksheets("fileData").Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I200")
Worksheets("fileData").Range("I2:I1589").Select
Worksheets("fileData").Columns("B:B").Select
Selection.NumberFormat = "General"
Worksheets("fileData").Range("L2:M2").Select
Worksheets("fileData").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets("fileData").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:M").Select

Application.CutCopyMode = False
End Sub
Sub dataSortSpeed()

Worksheets("fileSpec").Activate

Worksheets("fileSpec").Range("B12").Select
ActiveCell.FormulaR1C1 = " "

Worksheets("fileSpec").Range("B14").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-11]C,R[-2]C,R[-11]C[1],R[-2]C,R[-11]C[2],R[-2]C,R[-11]C[3],R[-2]C,R[-11]C[4],R[-2]C,R[-11]C[5],R[-2]C,R[-11]C[6])"

Worksheets("fileSpec").Range("B13").Select
ActiveCell.FormulaR1C1 = "=COUNT(fileData!C[-0])"

Count = Worksheets("fileSpec").Range("B13").Value

'Direction
For rwIndex = 2 To Count + 1
For colIndex = 6 To 6
With Worksheets("fileData").Cells(rwIndex, colIndex)
.Value = Worksheets("fileSpec").Cells(2, 8)
End With
Next colIndex
Next rwIndex

'Description
For rwIndex = 2 To Count + 1
For colIndex = 7 To 7
With Worksheets("fileData").Cells(rwIndex, colIndex)
.Value = Worksheets("fileSpec").Cells(14, 2)
End With
Next colIndex
Next rwIndex

'Counter Number
For rwIndex = 2 To Count + 1
For colIndex = 8 To 8
With Worksheets("fileData").Cells(rwIndex, colIndex)
.Value = Worksheets("fileSpec").Cells(5, 3)
End With
Next colIndex
Next rwIndex

'File Name
For rwIndex = 2 To Count + 1
For colIndex = 9 To 9
With Worksheets("fileData").Cells(rwIndex, colIndex)
.Value = Worksheets("fileSpec").Cells(1, 7)
End With
Next colIndex
Next rwIndex

'Area Number
For rwIndex = 2 To Count + 1
For colIndex = 10 To 10
With Worksheets("fileData").Cells(rwIndex, colIndex)
.Value = Worksheets("fileSpec").Cells(2, 2)
End With
Next colIndex
Next rwIndex

'Site Number
For rwIndex = 2 To Count + 1
For colIndex = 11 To 11
With Worksheets("fileData").Cells(rwIndex, colIndex)
.Value = Worksheets("fileSpec").Cells(2, 4)
End With
Next colIndex
Next rwIndex

'Location
For rwIndex = 2 To Count + 1
For colIndex = 12 To 12
With Worksheets("fileData").Cells(rwIndex, colIndex)
.Value = Worksheets("fileSpec").Cells(2, 6)
End With
Next colIndex
Next rwIndex

'Download Date
For rwIndex = 2 To Count + 1
For colIndex = 13 To 13
With Worksheets("fileData").Cells(rwIndex, colIndex)
.Value = Date
End With
Next colIndex
Next rwIndex

End Sub

Can someone remove this link :P I posted it by mistake