QueryTable import keeps appending instead of overwriting.

[David]

Hi,
I'm using Excel 2003 sp3. I've written a vba macro to import text
from a text file :

Sub Create_excel()
Dim WS As Worksheet
Dim QT As QueryTable

Set WS = Worksheets("Sheet1")
Set QT = WS.QueryTables.Add("TEXT;C:\temp\tmp.csv", WS.Range("A1"))
QT.TextFileSpaceDelimiter = True
QT.Refresh 'BackgroundQuery:=False
QT.Delete
End Sub

The first time I run it it runs fine. But the next time the data gets
appended adjacent to the last cell that was written on the previous run, and
not A1. Also even if I modify the text file and and re-run the changes don't
take effect. Can anyone help ?

Thanks.
-david.

 

[Sheeloo]

If there is nothing else which you want to retain on the sheet then use
I have just added the statement
Worksheets("Sheet1").UsedRange.ClearContents

'-------------------
Sub Create_excel()
Dim WS As Worksheet
Dim QT As QueryTable
Worksheets("Sheet1").UsedRange.ClearContents
Set WS = Worksheets("Sheet1")
Set QT = WS.QueryTables.Add("TEXT;C:\Documents and
Settings\Vinamra\Desktop\T1.txt", WS.Range("A1"))
QT.TextFileSpaceDelimiter = True
QT.Refresh 'BackgroundQuery:=False
QT.Delete
End Sub

 

[David]

Hi Sheeloo,
Thanks for the reply. Yes that works. It wasn't really appending - instead
it was always writing to A1 but the previous data was being pushed to the
right.
Also I found that the refresh does take place
when I update the file (I was referring to the wrong file ).

 

[Sheeloo]

Thanks for the feedback. I also did not realize that it was pushing the
existing data to the right...