Need to be able to:
1) Allow for a prompt so can navigate to a text file within the text
import wizard
2) Filter the list of dates to remove weekends from the list after the
import has taken place
3) Set up a dynamic (each time this is ran there will be more entries
so this can't be limited to a range) pivot table to group each week
together and provide the Max figure of the number field for that week.


Mini sample of data
Client Date Time Number
888 06/01/2004 11:23:29 381
888 06/01/2004 12:23:29 1
888 07/01/2004 03:23:30 5
888 07/01/2004 04:23:29 6
888 08/01/2004 22:23:29 6
888 08/01/2004 23:23:30 9
888 09/01/2004 00:23:30 7
888 09/01/2004 01:23:30 7
888 15/01/2004 12:23:30 273
888 15/01/2004 13:23:30 225
888 15/01/2004 14:23:30 285
888 15/01/2004 15:23:30 261

Any help on this would be really appreciated - Many Thanks, Al (
)

Al

Try this

Sub ImportTextFile()

Dim cell As Range
Dim Fname As String
Dim Sh As Worksheet
Dim i As Long
Dim Prng As Range
Dim Pt As PivotTable

Fname = Application.GetOpenFilename(",*.txt")

Workbooks.OpenText Filename:=Fname, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 4), Array(3, 1), Array(4, 1))

Set Sh = ActiveWorkbook.Sheets(1)

For i = Sh.Range("b2").End(xlDown).Row To 2 Step -1
If Application.Weekday(Sh.Cells(i, 2).Value) = 7 Or _
Application.Weekday(Sh.Cells(i, 2).Value) = 1 Then

Sh.Cells(i, 1).EntireRow.Delete
End If
Next i

Sh.Range("e1").Value = "Week"

For Each cell In Sh.Range("b2", Sh.Range("b2").End(xlDown)).Cells
cell.Offset(0, 3).Formula = "=WEEKNUM(" & cell.Address & ")"
Next cell

Set Prng = Sh.Range("B2").CurrentRegion

Set Pt = Sh.Parent.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Prng.Address).CreatePivotTable(TableDestination:=R ange("H17"), _
TableName:="PivotTable1")

Pt.PivotFields("Week").Orientation = xlRowField
Pt.PivotFields("Number").Orientation = xlDataField
Pt.PivotFields("Sum of Number").Function = xlMax

End Sub

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Al Mackay" wrote in message
om...
Need to be able to:
1) Allow for a prompt so can navigate to a text file within the text
import wizard
2) Filter the list of dates to remove weekends from the list after the
import has taken place
3) Set up a dynamic (each time this is ran there will be more entries
so this can't be limited to a range) pivot table to group each week
together and provide the Max figure of the number field for that week.


Mini sample of data
Client Date Time Number
888 06/01/2004 11:23:29 381
888 06/01/2004 12:23:29 1
888 07/01/2004 03:23:30 5
888 07/01/2004 04:23:29 6
888 08/01/2004 22:23:29 6
888 08/01/2004 23:23:30 9
888 09/01/2004 00:23:30 7
888 09/01/2004 01:23:30 7
888 15/01/2004 12:23:30 273
888 15/01/2004 13:23:30 225
888 15/01/2004 14:23:30 285
888 15/01/2004 15:23:30 261

Any help on this would be really appreciated - Many Thanks, Al (
)