Kobus Burger

Kobus Burger

  • NA
  • 10
  • 1.5k

Coding Excel queries under .NET

Oct 12 2020 9:33 AM
I need guidance on how to code Excel queries using .NET. It is easy to code Excel queries with VBA. The Excel macro recorder will guide you to produce code similar to the following:
  1. Sub Macro2()  
  2.     ActiveWorkbook.Queries.Add _  
  3.     Name:="TabWBS112288", _  
  4.     Formula:="let Source = Excel.CurrentWorkbook(){[Name=""TabWBS112288""]}[Content] in Source"  
  5.       
  6.     Workbooks("WBS test.xlsx").Connections.Add2 _  
  7.     Name:="Query - TabWBS112288", _  
  8.     Description:="Connection to the 'TabWBS112288' query in the workbook.", _  
  9.     ConnectionString:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=TabWBS112288;Extended Properties=""""", _  
  10.     CommandText:="SELECT * FROM [TabWBS112288]", _  
  11.     lCmdtype:=2  
  12. End Sub  

With .NET I use the Excel interop but it seems not have the same query objects as VBA. The interop has workbookconnections and models but not queries.

I need to manipulate Excel queries using C# in Visula Studio but I do not know how to.

How can I code the example VBA in C#?

Answers (7)