nataliek92
New Member
- Joined
- Oct 29, 2014
- Messages
- 40
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.
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
I have the following code which opens a connection to a text file and imports it into my chosen range within my workbook.
Code:
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