how delete old querytables?

[Ian Elliott]

Thanks for any help.
I have some sheets in a workbook, with 5 querys each. I want to make a macro
that updates them, and I was going to do a activesheet.querytables.count, and
then do a for loop to refresh each one. But there is more than 5 querytables
on most of the pages. I think that is because when I first started doing this
kind of stuff, I deleted some or copied etc. How can I "find" the other
querytables so as to delete them?
Thanks again.

 

[XP]

Here is a very old sub, but still seems to function.

Paste into a standard code module and run it; the only thing is, it deletes
all QTs; but gives you a count of those in the current sheet and the current
file and you have an option to delete them or not.

You may want to delete them all, then re-run each from scratch and refresh
after that to clear everything out. You probably also have a lot of named
ranges you may need to clear out. It all takes up memory! Let me know if it
works for you!

Sub Display_Delete_All_QueryTables_In_Sheet()

'THIS PROGRAM DISPLAYS A COUNT OF ALL QUERYTABLES IN THE ACTIVESHEET
'WITH THEIR NAMES; THEN DISPLAYS A COUNT OF ALL QUERYTABLES IN THE
'ACTIVE FILE WITH THEIR NAMES; THEN DISPLAYS AN OPTION TO DELETE ALL
'QTS IN THE ACTIVESHEET OR ALL QTS IN THE ACTIVE FILE OR QUIT.

'Ignore errors
On Error Resume Next

'Display QTs in sheet
xQTCount = ActiveSheet.QueryTables.Count
For x = 1 To xQTCount
sMsg = sMsg & ActiveSheet.QueryTables(x).Name & vbCr
Next
MsgBox "Sheet QueryTables (" & ActiveSheet.QueryTables.Count & "):" & vbCr &
sMsg

'Display Qts in file
xSheetCount = ActiveWorkbook.Sheets.Count
For s = 1 To xSheetCount
xQTCount = Sheets(s).QueryTables.Count
q = q + xQTCount
For f = 1 To xQTCount
fMsg = fMsg & Sheets(s).Name & "!" & Sheets(s).QueryTables(f).Name &
vbCr
Next f
Next s
MsgBox "File QueryTables (" & q & "):" & vbCr & fMsg

'Skip if none are found
If q > 0 Then

'Display option for deletion
dMsg = "Yes = Delete ALL querytables in the active sheet" & vbCr
dMsg = dMsg & "No = Delete ALL querytables in the file" & vbCr
dMsg = dMsg & "Cancel = Quit program"
xResponse = MsgBox(dMsg, vbYesNoCancel)

'Cancel
If xResponse = vbCancel Then End

'Yes - delete sheet QTs
xQTCount = ActiveSheet.QueryTables.Count
For x = 1 To xQTCount
ActiveSheet.QueryTables(x - n).Delete
n = n + 1
Next x

'No - delete file QTs
xSheetCount = ActiveWorkbook.Sheets.Count
For s = 1 To xSheetCount
xQTCount = Sheets(s).QueryTables.Count
For q = 1 To xQTCount
Sheets(s).QueryTables(q - n).Delete
n = n + 1
Next q
Next s
End If

End Sub

 

[XP]

Just an FYI, as I recall everytime you run a QT from scratch (i.e. not a
refresh) a new QT object is created and added to the sheet. This object is
not visible, but takes up memory. If you keep rerunning them without deleting
the QT object they eventually inflate the size of your file and everything
slows way down. I think it is the same thing with named ranges; ie everytime
the query is rerun, a new named range is added.

I used to just re-run the QTs from scratch every time and I kept two subs
beneath the QT code that would delete the QT and the last named range
everytime the code was rerun. That way everything was cleaned up on each run.
I'm sure in this forum I would get a lecture from someone about how
inefficient that was, but hey, it worked for me for years!

HTH