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;
            try
            {
 
                // 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 + "'";
                }
                else
                {
                    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;
                oTable.Refresh(false);
 
                //Remove the Connection I made because I don't want users refreshing the data (optional)
                oWB.Connections[1].Delete();
                //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

• Anonymous
  August 31, 2011
  How to use ADO instead of ODBC to make a connection to Excel?

• Anonymous
  September 05, 2011
  Douglas, 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).

• Anonymous
  June 30, 2014
  Excellent! Thanks for this solution

• Anonymous
  August 30, 2015
  Thank you for sharing ...

• Anonymous
  December 14, 2016
  Great! Thanks

• Anonymous
  March 08, 2017
  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.