VBA - Delete Connections & Query Tables

[nataliek92]

Hi everyone,

I have the following code which opens a connection to a text file and imports it into my chosen range within my workbook.

Sub ImportTextFile()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    With Worksheets("Prov_Data").QueryTables.Add( _
        Connection:="TEXT;\\Data Provision.txt", _
        Destination:=Worksheets("Prov_Data").Range("$A$1"))
        .Name = "Data Provision"
        .FieldNames = True
        .TextFileTabDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
    
    ThisWorkbook.Connections("Data Provision").Delete

[B][COLOR=#ff0000]    ActiveSheet.QueryTables("Data Provision").Delete[/COLOR][/B]

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

I receive a "Subscript out of range" error message at the ActiveSheet.QueryTables... line (highlighted in red). If I remove this line, my code works fine. If I then go into Data -> Connections I can see that there are no open connections in my workbook, however, if I try to delete any of the data, Excel gives me a error message "The range you deleted is associated with a query that retrieves data from an external source." This tells me that there is still a connection to the text file somehow. I think this is because I am not deleting the query tables, but for some reason I cannot do this.

My question is, does anyone know how to successfully delete all connections to the Query table I am using? I would like the data to be pasted into my worksheet as a value, with no association or connection to an external source.

Also, I would like to refer directly to the sheet which the data is located on, rather than using "ActiveSheet", because I plan to have a code which does not activate the specified sheet.

Any ideas?

Thanks in advance for your help.

Natalie

 

[Andrew Poulsom]

Shouldn't it be?

Worksheets("Prov_Data").QueryTables("Data Provision").Delete

 

[nataliek92]

Shouldn't it be?

Worksheets("Prov_Data").QueryTables("Data Provision").Delete

Sorry, yes I have tried this but it also gives me a "Subscript out of range" error... Really don't understand why!

 

[Andrew Poulsom]

Well the name of the QueryTable is right. What happens if you remove this line?

ThisWorkbook.Connections("Data Provision").Delete

 

[Comfy]

Sorry, yes I have tried this but it also gives me a "Subscript out of range" error... Really don't understand why!

I think it is because the QueryTable isn't called "Data Provision" it is probably called "Data Provision_1"

Your Data connection is given the name "Data Provision" and so your QueryTable name has been incremented by 1, "Data Provision_1"

try one or both of these:

Worksheets("Prov_Data").QueryTables("Data Provision_1").Delete
Worksheets("Prov_Data").QueryTables(1).Delete

If you have multiple Querytable in the worksheet we should change the second line.

 

[nataliek92]

I think it is because the QueryTable isn't called "Data Provision" it is probably called "Data Provision_1"

Your Data connection is given the name "Data Provision" and so your QueryTable name has been incremented by 1, "Data Provision_1"

try one or both of these:

Worksheets("Prov_Data").QueryTables("Data Provision_1").Delete
Worksheets("Prov_Data").QueryTables(1).Delete

If you have multiple Querytable in the worksheet we should change the second line.

Thanks a lot for this, the code now runs without any errors, however, my little check of deleting the data from my worksheet still gives me the error "The range you deleted is associated with a query..."

Any idea what could be causing this?

Is there any way to delete all connections and all queries?

Thanks again for your help

 

[Comfy]

Give this a try:

Sub ImportTextFile()


Dim Cn As Variant
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    With Worksheets("Prov_Data").QueryTables.Add( _
        Connection:="TEXT;\\Data Provision.txt", _
        Destination:=Worksheets("Prov_Data").Range("$A$1"))
        .Name = "Data Provision"
        .FieldNames = True
        .TextFileTabDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
    
    For Each Cn In ThisWorkbook.Connections
        Cn.Delete
    Next Cn
    For Each Cn In ActiveSheet.QueryTables
        Cn.Delete
    Next Cn
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


End Sub

 

[nataliek92]

Give this a try:

Sub ImportTextFile()


Dim Cn As Variant
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False


    With Worksheets("Prov_Data").QueryTables.Add( _
        Connection:="TEXT;\\Data Provision.txt", _
        Destination:=Worksheets("Prov_Data").Range("$A$1"))
        .Name = "Data Provision"
        .FieldNames = True
        .TextFileTabDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
    
    For Each Cn In ThisWorkbook.Connections
        Cn.Delete
    Next Cn
    For Each Cn In ActiveSheet.QueryTables
        Cn.Delete
    Next Cn
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


End Sub

Thank you very much, this works !!

Because the worksheet containing data won't necessarily be activated, I have changed "ActiveSheet" to Worksheets("Prov_Data") which works perfectly.

Thanks again

 

[Comfy]

Thank you very much, this works !!

Because the worksheet containing data won't necessarily be activated, I have changed "ActiveSheet" to Worksheets("Prov_Data") which works perfectly.

Thanks again

You're Welcome.