[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How do I execute a query on an Excel workbook/worksheet using VSTO?

Posted on 2009-04-30
Medium Priority
Last Modified: 2013-11-10

I've created an add-in for MS Excel with Visual Studio and VSTO, and I'd like to access data from an opened workbook with an SQL query in C#.

I know how to do that with an OleDb connection on an Excel file, but here the workbook is opened and I'd like to make the query on the data stored in.

Would you have some ideas to do that?
Question by:JulienVan
  • 2
LVL 10

Assisted Solution

by:Jon von der Heyden
Jon von der Heyden earned 2000 total points
ID: 24268532
Not especially my area of expertise, but I have done similar before and pointed back to my open workbook.  See example below:

ActiveSheet.QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";Jet OLEDB:Engine Type=35"), Destination:=Range("A1"))

Open in new window


Author Comment

ID: 24268872
Thanks for your comment, it seems to be a good way to do that.
But does using ActiveWorkbook.FullName as data source implicate that the workbook needs to be saved? Because I'd like to not change the state of the workbook when I execute a query.
LVL 10

Accepted Solution

Jon von der Heyden earned 2000 total points
ID: 24269430
As far as I know the workbook has to be saved down if you want to query.  All of my models where I have done this have been saved, most of my code was learned using the recorder when following steps shown in this tutorial. http://www.exceluser.com/explore/msquery1_1.htm
So perhaps the solution for you will be to take you data table(s) to a new workbook and save it down to your temp file location.  You can find your temp file location using Environ$("temp").  Query that table, return your results and then you can delete the temp file at the end of the routine.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question