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

Posted on 2009-04-30
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
    LVL 10

    Assisted Solution

    by:Jon von der Heyden
    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

    LVL 2

    Author Comment

    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

    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.
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
    A short article about problems I had with the new location API and permissions in Marshmallow
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now