Excel - External Data: Named Ranges go on increasing.......

[junoon]

Hi,

I am importing a text file (which gets updated every time by a Avaya
system autogenerated vbscript file), into an excel sheet, using this
Autpen macro:

1] I want to use a Dynamically Defined Range name "PTA" to select the
imported data, which is not happening. When i run the macro Manually or
open worksheet, it adds PTA defined name (OK...but not refering to
Sheet1), but also adds PTA_1, PTA_1, etc...for each refresh or
open...which i dont want to happen. I only want it to use the same
dynamically defined range name i.e. PTA.....

2] I know that the minimum time-limit is 60 secs, can i lower that to
30 secs or 15 secs...is there a macro which can create a custom refresh
time???

3] is it possible to delete the names created, (if no solution is
available for above Q1), on workbook_close(), along with deleting Query
& clearing contents.....

P.S: The solution for Q1 is very important to me......

Heres the code...


****************************************************
Option Explicit

Sub Autpen()
'Dim SomeName As Name
With ThisWorkbook
.Worksheets("Sheet1").Activate
.Names.Add Name:="PTA",
RefersTo:="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))",
Visible:=True
End With

With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"TEXT;C:\pta.txt", Destination _
:=Range("A1"))
' .Name = ThisWorkbook.Names("PTA")
.Name = "PTA"
.FieldNames = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlOverwriteCells
.SaveData = True
.AdjustColumnWidth = True

' Refresh file not less than 60 secs (1 min). HERE, CAN I HAVE A CUSTOM
REFRESH MACRO.....
.RefreshPeriod = 1

'For Text file
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileTabDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=True
End With
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

*************************************************************************

PLEASE HELP ASAP!