QueryTables.Delete doesn't work for me

[xml2]

Hi,

I'm trying to import an externel .dat (plain text) file into an excel file. I recorded a macro whilest importing it via the menu, and afterwards edited it. But I noticed that after doing this for like 20 times the excell sheet was getting slower and slower (at the end it took 100% CPU load for about +/- 50sec)

This is the code I use:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;Y:\Scan\OUT\OUT.DAT", _
Destination:=Range("A1&quot)
.Name = "out"
.FieldNames = True
.RowNumbers = False

.....

.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(3, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With

I searched the msdn to close the connections and tried this little peace of code:

For i = 1 To ActiveSheet.QueryTables.Count
ActiveSheet.QueryTables(i).Delete ' this is the line with the error
Next

but I get following error: (is normaly in dutch, but I'll try to translate it correct)

Fault 9 while executing:

The subscript is out of range

 

[xlbo]

Try doing it backwards:
For i = ActiveSheet.QueryTables.Count to 1 step -1
ActiveSheet.QueryTables(i).Delete ' this is the line with the error
Next


Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[xml2]

Sorry to get this thread back up, but I wasn't working this weekend.

Just to say that this worked fine! Ty