使用VBA删除Power Query查询的自定义XML数据 [英] Delete Power Query queries' custom XML data using VBA

问题描述

我在Excel 2010文件中具有Power Query查询，并想使用VBA删除这些查询.到目前为止，我可以通过以下步骤手动实现:

I have Power Query queries in an Excel 2010 file and would like to delete these queries using VBA. so far I can achieve that manually with the following steps:

1. 卸载Power Query加载项
2. 运行文档检查器，然后单击以删除自定义XML数据

宏记录器为步骤2记录以下代码.

The Macro recorder record the following code for step 2.

Sub Makro1()
'
' Makro1 Makro
'
'
ActiveWorkbook.RemoveDocumentInformation (xlRDIPrinterPath)
ActiveWorkbook.RemoveDocumentInformation (xlRDIDocumentProperties)
ActiveWorkbook.RemoveDocumentInformation (xlRDIInactiveDataConnections)
End Sub

但是，使用此宏无法达到相同的结果(= PQ查询代码已从工作簿中删除).

However, using this macro I do not achieve the same result (=PQ query code gone from the workbook).

关于如何实现使用VBA删除PQ查询代码的任何想法?

Any ideas on how to achieve the PQ query codes to be removed using VBA?

推荐答案

对于Excel 2016预览，有新的VBA对象使我们能够以编程方式更好地处理PQ.例如.在以下位置查看Sub DeleteQuery(): https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1

For Excel 2016 preview, there are new VBA objects which enable us to better handle PQ programmatically. E.g. check out Sub DeleteQuery() at: https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1

但是对于那些坚持使用Office 2010的人来说，这似乎还不可能.

However for those of us stuck on Office 2010, this doesn't yet seem possible.

这篇关于使用VBA删除Power Query查询的自定义XML数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！