删除Excel 2007工作簿中的所有QueryTables [英] Deleting all QueryTables in Excel 2007 Workbook

问题描述

使用Excel Interop，我试图遍历所有工作表中的所有QueryTable并将其删除.我有这段代码，可以正常工作:

Using Excel Interop, I'm attempting to loop through all QueryTables in all Worksheets and delete them. I've got this code, which works:

> // loop through each Worksheet
> for (int i = 1; i <= workbook.Sheets.Count; i++)
> {
>     sheet = (Worksheet)workbook.Sheets.get_Item(i);
> 
>     // loop through each queryTable on each Worksheet
>     int queryTableCount = sheet.QueryTables.Count;
>     for (int j = 1; j <= queryTableCount; j++) {
>        sheet.QueryTables.Item(1).Delete();
>     }
> }

这对于某些数据连接/文件来说很好用，但是对于其他数据连接"QueryTables.Count"则返回零-即使我知道存在 个连接.这些连接是在Excel 2007中通过数据"选项卡->从其他来源"->从Microsoft Query"创建的.有没有人遇到这个问题?

This works just fine for some data connections/files, but for others the "QueryTables.Count" returns zero - even though I know that there are connections. The connections were created in Excel 2007 via the Data tab-->From Other Sources-->From Microsoft Query. Has anyone run into this problem?

推荐答案

由于

I figured out the issue, thanks to this post. It turns out that Excel Interop handles query tables differently in the 2003 vs. 2007 versions of Excel. 2007 uses ListObjects, so use the same code to loop through each Worksheet like above, but then use:

// loop through each list object on each Worksheet
if (sheet.ListObjects.Count > 0)
{
   foreach (ListObject obj in sheet.ListObjects)
   {
     obj.QueryTable.Delete();
   }
}

这篇关于删除Excel 2007工作簿中的所有QueryTables的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！