delete connection

[ecawilson]

The following code gives me the "Run-time error '9': Subscript out of range" error on the last line, but only the first time it is run in a blank workbook.

mypath = [windows network path]<WINDOWS Path Network>
myfile = [text file name]<FILENAME extention without>
With Sheets(1).QueryTables.Add(Connection:="TEXT;" & mypath & myfile & ".txt", Destination:=Range("$A$1"))
.Name = myfile
.TextFileParseType = xlFixedWidth
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(3, 19, 15, 5, 12)
.Refresh
End With
ThisWorkbook.Connections(myfile).Delete

The first time this is run, the text file is imported just fine, but the connection can't be deleted. As a matter of fact, in the debug window, "print activeworkbook.Connections.Count" returns 0. Then, if I choose the 'Data' tab, 'Connections' button, my connection appears, can be counted, and I can resume the code.

I haven't tried the Wait function, but when I let it sit for a minute after choosing 'Debug', there is no change. Yet, when quickly looking at the connections I can complete the code in less than 5 seconds.

The second and subsequent times it is run, it works just fine. I check to be sure, and there are no connections listed before I run it, but this time when I break before the 'delete' line, I get a count of 1 connection. Checking the connections window, I confirm that I see only 1 connection, and viewing this window does not change my connections count.

How can I delete the connection right after creating the table?

(the why: I inherited a report that has a few hundred connections in it, all to files that the end users don't have access to. Somehow, this mess has become business critical, but takes excel a long time to finally give up trying to refresh the connections. I just want to delete all the connections before the end user gets their hands on it.)

Windows XP, Excel 2007

Thanks much for the help.
Christopher

 

[ecawilson]

Found a workaround, replacing

ThisWorkbook.Connections(myfile).Delete

with

Sheets(1).QueryTables(1).Delete

. This project will function fine like this, but in my next project I will want to reference the query table after I delete the connection, the naming of the query tables is confusing for me (can't figure out why and how it is replacing [and not appending] digits at the end of 'myfile', and not always sequentially), and also I can't figure out why my blank workbook outputs 4 as the response to print sheets(1).querytables.count.

Would still be greatful for help in understanding how to correctly delete the connection.

 

[ecawilson]

No connection experts here? Bumping just in case.