Jeff Sanders Technical Blog

I am a Microsoft employee that has worked on all aspects of the Web Stack for a long time. I hope these blogs are useful to you! Use this information at your own risk.


<< Go Back

How To Programmatically Add A Querytable And Data Connection To Excel

- 05 Aug 2010

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)
  &nbs p;             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;

        }
<< Go Back