How to delete QueryTable in VSTO?

[Ed White]

How do I delete a QueryTable on an Excel spreadsheet using VB in VSTO?

In VBA, the following works, but I can't get anything to work in VB/VSTO.
ActiveSheet.Cells.Select
Selection.ListObject.QueryTable.Delete

 

[Jialiang Ge [MSFT]]

Hello Ed,

I'm not sure that the following solution is related to this issue because
you did not tell the symptom in VSTO. If this solution is not helpful to
you, please let me know the error code in VSTO, and Office and VSTO
version. Thanks

--- SOLUTION ---
I know of a design change that we use Selection.ListObject.QueryTable in
Excel 2007 and Selection.QueryTable in the previous versions of Excel,
thus, you may need to detect the current office version
(Application.Version), and select Selection.ListObject.QueryTable or
Selection.QueryTable accordingly in the code.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ed White]

I had tried Selection.ListObject.QueryTable before--that was the code the VBA
macro recorder suggested, but it didn't work.

After considerable trial-and-error, I got the following to work from
ThisWorkbook:
Me.Sheets("Sheet1").ListObjects.Item(1).Delete()

or directly from Sheet1:
Me.ListObjects.Item(1).Delete()

As Excel is not zero-based, Item(1) would delete the one QueryTable on Sheet1.

The table I've been trying to delete is actually the new 'Table' in Excel
2007, which is apparently an updated version of a 'ListObject' in Excel 2003,
which may explain the difference in coding. It's confusing because when I
create the Table using the VBA macro recorder, the VBA code callis it a
QueryTable, and likewise with the macro recorder on, when I select and delete
the Table, the VBA again suggests it's a QueryTable. So there is some
inconsistency in the syntax between the VBA and VSTO, which explains my
difficulty in figuring out how to code it in VSTO.

--
Ed

Hello Ed,

I'm not sure that the following solution is related to this issue because
you did not tell the symptom in VSTO. If this solution is not helpful to
you, please let me know the error code in VSTO, and Office and VSTO
version. Thanks

--- SOLUTION ---
I know of a design change that we use Selection.ListObject.QueryTable in
Excel 2007 and Selection.QueryTable in the previous versions of Excel,
thus, you may need to detect the current office version
(Application.Version), and select Selection.ListObject.QueryTable or
Selection.QueryTable accordingly in the code.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jialiang Ge [MSFT]]

Hello Ed,

The day before yesterday, I submitted the issue in this post to the product
group. Today, I get their confirmation that they have logged this problem
into their database for fix. Thank you very much for your time and effort
to report this issue to us! If you have any other concerns, please DON'T
hesitate to tell me.

Thanks and have a very nice day!

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================