Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTable import keeps appending instead of overwriting.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTable import keeps appending instead of overwriting.
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTable import keeps appending instead of overwriting.
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" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTable import keeps appending instead of overwriting.
Thanks for the feedback. I also did not realize that it was pushing the
existing data to the right... "David" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QueryTable Null Import Quirk / Problem | Excel Programming | |||
Import data using QueryTable somehow change sheet cells references | Excel Programming | |||
overwriting formulas | Excel Programming | |||
Changing only source file of pre-existing text import QueryTable? | Excel Programming | |||
Using Querytable.add to import data from a closed workbook | Excel Programming |