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
"XP" wrote:
> 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
>
>
> "Ian Elliott" wrote:
>
> > 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.
|
Similar Threads
