Question on using the current file for creation of a pivot table.

Background - I get about 100 downloaded text files from our servers based on
a set of input values. These reports manipulated and then are sent to
regional areas. I currently use a VPA macro to move from the text file to a
nice excel spreadsheet, but still create a Pivot tables for each by hand.

The only variable is the file name, and that the files vary in length from 4
lines to 5000 lines.

The macro record provides the following text€¦

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'rdlc100712-05'!R1C1:R1757C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("A/A type text")
.Orientation = xlColumnField
.Position = 1

I have tried and failed to get the program to pull a pre-formatted worksheet
using the following changes to the pivot table (changed the file name to
myfile, and the area to look at to be 5000 rows.

Dir "D:\SapData\SAPworkdir"
myfile = Application.GetOpenFilename("text Files,*.xls")

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'myfile'!R1C1:R5000C9").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("A/A type text")
.Orientation = xlColumnField
.Position = 1

I am trying to use a common pivot table, to draw out a file from my
directory, select all text in the file, and then create the pivot table from
this.

Can someone tell what I have done wrong€¦ I know it is probably a very
simple error, but I cannot for the life of me find it.