How to Programmatically add a QueryTable and Data Connection to Excel

I needed to create an Excel Spreadsheet with a Connection object and link a QueryTable to it.  I could not find a good example of this so I thought I would share.  Please drop me a note if you found this useful!

Code listing for sample (Copy Code):

        private void createProdSheet(string topicText, bool debugView)
            // Start a new workbook in Excel.
            string aProd = topicText;

            Microsoft.Office.Interop.Excel.Application oXL;
            Workbook oWB;
            Workbooks oWBS;
            Worksheet oTemplateSheet;
            Sheets oSheets;
            QueryTables oTables;
            QueryTable oTable;
            Range oRng;

                // Start Excel and get the Application object.
                oXL = new Microsoft.Office.Interop.Excel.Application();
                oXL.Visible = true;
                oXL.ScreenUpdating = false;

                // get the workbooks collection and add a new Workbook to it.
                oWBS = oXL.Workbooks;
                oWB = oWBS.Add();

                // Create a QueryTable that starts at cell A1.
                oSheets = oWB.Sheets;
                // by default when you create a new WB you get 3 sheets, get the first one
                oTemplateSheet = oSheets[1];
                oRng = oTemplateSheet.get_Range("A1");
                oTemplateSheet.Name = "ProductId Assignments";

                // get the QueryTables collection
                oTables = oTemplateSheet.QueryTables;

                string SQLStr;

                // decide how much detail we want and use the appropriate stored procedure
                if (debugView)
                    SQLStr = "EXEC [dbo].[GetMergedPesIDReportForOpsByNameDetailed] @ProductFamily = N'" + aProd + "'";
                    SQLStr = "EXEC [dbo].[GetMergedPesIDReportForOpsByName] @ProductFamily = N'" + aProd + "'";
                object aStrSQL = SQLStr;

                object connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=STIProducts;Data Source=stieditor;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=JSANDERS6;Use Encryption for Data=False;Tag with column collation when possible=False";

                // create a query table with the connection and SQL command
                oTable = oTables.Add(connection, oRng, aStrSQL);
                oTable.RefreshStyle = XlCellInsertionMode.xlInsertEntireRows;

                //Remove the Connection I made because I don't want users refreshing the data (optional)
                //Make sure Excel is visible and give the user control of Microsoft Excel's lifetime.
                oXL.Visible = true;
                oXL.ScreenUpdating = true;
                oXL.UserControl = true;
            catch (Exception theEx)
                MessageBox.Show(theEx.Message, "Error creating prodid sheet");

            oRng = null;
            oTable = null;
            oTables = null;
            oSheets = null;
            oTemplateSheet = null;
            oWB = null;
            oWBS = null;
            oXL = null;

Comments (6)

  1. How to use ADO instead of ODBC to make a connection to Excel?

  2. Jeff Sanders says:


    This is not making a connection to Excel.  This is programatically creating a datasource within Excel to query a db and then hooking up a table to see that data.  If you want a different query string, simply create a datasource within Excel and copy that connection string into the code (but it looks from your question you are attempting to do something different).

  3. Beka says:


    Thanks for this solution

  4. Sachin says:

    Thank you for sharing …

  5. Andrés says:

    Great! Thanks

  6. Alastair McInnes says:

    Thank-you a thousand times! This was driving me mad trying to figure out what to set and when – the MS documentation is lamentable on this subject.

Skip to main content