Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how delete old querytables?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how delete old querytables?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how delete old querytables?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QueryTables | Excel Programming | |||
ActiveSheet.QueryTables.Add | Excel Programming | |||
QueryTables.Add and XML | Excel Programming | |||
QueryTables Add | Excel Programming |