none
C# code to refresh excel data

    Question

  • Hi All ,

    Wondering is there any easy code wrriten in C# to refresh excel data,

    The excel file connects to a table in SQL server table ,need to refresh the excel sheet using C#.

    Thanks


    Sri.Tummala

    Thursday, November 29, 2012 12:20 PM

Answers

  • Hi All,

    The above code is saving the file before refreshing the data so I added system wait for 20 seconds .

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    namespace ST_53932a75e92c44f086535fc017a56e6a.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
            /*
    		The execution engine calls this method when the task executes.
    		To access the object model, use the Dts property. Connections, variables, events,
    		and logging features are available as members of the Dts property as shown in the following examples.
    		To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    		To post a log entry, call Dts.Log("This is my log text", 999, null);
    		To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    		To use the connections collection use something like the following:
    		ConnectionManager cm = Dts.Connections.Add("OLEDB");
    		cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    		Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    		
    		To open Help, press F1.
    	*/
             
            
            
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Documents and Settings\ST84879\Desktop\ROBERT_DATA_SET\TEST.xls");
                Dts.TaskResult = (int)ScriptResults.Success;
            }
         
            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                         
                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
        }
    }


    Sri.Tummala

    • Marked as answer by kali786 Monday, December 3, 2012 12:35 PM
    Monday, December 3, 2012 12:34 PM
  • Hi Ramyakranthi,

    you have to add two refreences to the code one is Microsoft.Office.Core and Microsoft.Office.Interop.Excel

    by downloading office namespace fro c# from internet.

    Office assemblies.

    2007

    http://www.microsoft.com/en-gb/download/details.aspx?id=18346

    2010

    http://www.microsoft.com/en-gb/download/details.aspx?id=3508

    Or search in google as

    o2007pia msi download 

    After downloading the assemblies go to the c# script and add the references in openeing project explorer for c# code


    Sri.Tummala

    • Marked as answer by kali786 Friday, May 3, 2013 1:12 PM
    Friday, May 3, 2013 1:12 PM
  • New c# code which takes 20 seconds to refresh and save the excel file.

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    namespace ST_53932a75e92c44f086535fc017a56e6a.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
            /*
    		The execution engine calls this method when the task executes.
    		To access the object model, use the Dts property. Connections, variables, events,
    		and logging features are available as members of the Dts property as shown in the following examples.
    		To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    		To post a log entry, call Dts.Log("This is my log text", 999, null);
    		To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    		To use the connections collection use something like the following:
    		ConnectionManager cm = Dts.Connections.Add("OLEDB");
    		cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    		Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    		
    		To open Help, press F1.
    	*/
             
            
            
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Documents and Settings\ST84879\Desktop\ROBERT_DATA_SET\Active.xls");
                Dts.TaskResult = (int)ScriptResults.Success;
            }
         
            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                         
                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
        }
    }

    you have to add two refreences to the code one is Microsoft.Office.Core and Microsoft.Office.Interop.Excel

    by downloading office namespace fro c# from internet.

    Office assemblies.

    2007

    http://www.microsoft.com/en-gb/download/details.aspx?id=18346

    2010

    http://www.microsoft.com/en-gb/download/details.aspx?id=3508

    Or search in google as

    o2007pia msi download 

    After downloading the assemblies go to the c# script and add the references in openeing project explorer for c# code



    Sri.Tummala

    • Marked as answer by kali786 Wednesday, May 8, 2013 10:49 AM
    Wednesday, May 8, 2013 10:49 AM

All replies

  • Any particular reason to do this with C# instead of using a VBA macro in the Excel file?

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Thursday, November 29, 2012 12:27 PM
  • Does the excel worksheet refresh if it is open byitself without C#?  What setting are your using in the excel worksheet for refreshing the connection when it is opened by itself?

    jdweng

    • Proposed as answer by bahar_Agi Tuesday, May 7, 2013 8:26 AM
    • Unproposed as answer by bahar_Agi Tuesday, May 7, 2013 8:26 AM
    Thursday, November 29, 2012 1:37 PM
  • Hi Pieter,

    I want this code in SSIS script task using C# ,My requiremnt is to refresh the microsfot query in excel and email it everything should be atumated ,

    The data in the table which excel looks at gets refreshed by truncating and loading every day.

    Thanks


    Sri.Tummala

    Thursday, November 29, 2012 1:40 PM
  • Hi Joel,

    The excel is sent over email to different country the excel file should refreshed,saved and emailed to the particular person all using SQL server integration services,

    Need c# code to usd in script task to refresh trhe excel file.

    Thanks


    Sri.Tummala

    Thursday, November 29, 2012 1:42 PM
    1. Add references to all the requisite COM interops for Excel;
    2. GeObject on the Ecel workbook, setting visibility to none;
    3. Refresh.


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Thursday, November 29, 2012 1:43 PM
    1. Add references to all the requisite COM interops for Excel;
    2. GeObject on the Ecel workbook, setting visibility to none;
    3. Refresh.


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Hi Pieter,

    Any example please.

    Thanks


    Sri.Tummala

    Thursday, November 29, 2012 2:25 PM
  • I not sure which method of refreshing is best in this case.  I just want to list the various options

    In Excel - you have to set the refresh method on the object in the workbook

    1)  Only when the workbook is opened

    2) Periodic - Specify a time like every 30 minutes

    3) Background - check database automatically for chnages

    In C#

    4) Update on command beside the options set in workbook


    jdweng

    Thursday, November 29, 2012 4:27 PM
  • Hi Tummala,

    Maybe you can try to use Mark Designer in Excel with C# to update data connected to SQL sever.

    This article presents the method about how to realize mark designer in Excel. Maybe you can get something from it.

    http://www.c-sharpcorner.com/UploadFile/d2dcfc/C-Sharp-create-excel-report-chart-contained-with-mark-designe/

    Friday, November 30, 2012 3:26 AM
  • Hi All,

    I tried this code to refesh excel file but its not refreshing the data from sql server table  need help can anyone sort this out please.

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    namespace ST_53932a75e92c44f086535fc017a56e6a.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
            /*
    		The execution engine calls this method when the task executes.
    		To access the object model, use the Dts property. Connections, variables, events,
    		and logging features are available as members of the Dts property as shown in the following examples.
    		To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    		To post a log entry, call Dts.Log("This is my log text", 999, null);
    		To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    		To use the connections collection use something like the following:
    		ConnectionManager cm = Dts.Connections.Add("OLEDB");
    		cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    		Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    		
    		To open Help, press F1.
    	*/
             
            
            
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Documents and Settings\ST84879\Desktop\ROBERT_DATA_SET\TEST.xls");
                Dts.TaskResult = (int)ScriptResults.Success;
            }
         
            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = true;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
        }
    }


    Sri.Tummala

    • Proposed as answer by Ramyakranthi Wednesday, May 8, 2013 6:34 AM
    Monday, December 3, 2012 12:06 PM
  • Hi All,

    The above code is saving the file before refreshing the data so I added system wait for 20 seconds .

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    namespace ST_53932a75e92c44f086535fc017a56e6a.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
            /*
    		The execution engine calls this method when the task executes.
    		To access the object model, use the Dts property. Connections, variables, events,
    		and logging features are available as members of the Dts property as shown in the following examples.
    		To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    		To post a log entry, call Dts.Log("This is my log text", 999, null);
    		To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    		To use the connections collection use something like the following:
    		ConnectionManager cm = Dts.Connections.Add("OLEDB");
    		cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    		Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    		
    		To open Help, press F1.
    	*/
             
            
            
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Documents and Settings\ST84879\Desktop\ROBERT_DATA_SET\TEST.xls");
                Dts.TaskResult = (int)ScriptResults.Success;
            }
         
            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                         
                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
        }
    }


    Sri.Tummala

    • Marked as answer by kali786 Monday, December 3, 2012 12:35 PM
    Monday, December 3, 2012 12:34 PM
  • 

    i got this error when i run the above code please help me.

    i am using sql server 2012,in script task i copied the above code and just i chaged the excel workbook location.

    Thursday, May 2, 2013 1:53 PM
  • Which line of code is giving the failure?

    You also have to change the following line

    cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

    the above code is expecting a SQL server to be running on the same PC as your VS code (localhost) and the SQL server has a database called SQLNCLI10


    jdweng

    Thursday, May 2, 2013 2:06 PM
  • i copied the above code,it build-ed successfully

    when i execute the script task i got that error

    so,where i need to put the above code.

    Thursday, May 2, 2013 2:32 PM
  • I rewrote the code above to use the oledb method instead of the DTS method.  Instead of using the Interop.Excel library you can use the oledb to also read and write an excel workbook.  The oledb runs much quicker than the Interop.Excel method except oledb you only read and write text (not formating) and the oledb requires the excel workheet to start in row 1 and column 1.  Also the oledb will stop reading a workbook as soon as it finds an empty cell in row 1 or column A.

    using System;
    using System.Data;using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    using System.Data.OleDb;
    namespace ConsoleApplication1
    {
        class Program
        {
            OleDbConnection conn;
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Documents and Settings\ST84879\Desktop\ROBERT_DATA_SET\TEST.xls");
            }
            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                conn = new OleDbConnection();
                conn.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
                conn.Open();
                conn.Close();
     
            }
        }
    }


    jdweng

    Thursday, May 2, 2013 2:48 PM
  • i got the same error

    SQLNCLI10 this is not database name

    how  we get the provider name 

    Friday, May 3, 2013 5:58 AM
  • using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    using System.Data.OleDb;
    namespace ConsoleApplication1
    {
        class Program
        {
            OleDbConnection conn;
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"E:\DATA.xls");
            }
            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                conn = new OleDbConnection();
                conn.ConnectionString = "Data Source=localhost;Initial Catalog=ADSO9-PC;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
                conn.Open();
                conn.Close();
    
            }
        }
    }

    This is the code what i modified

    here ADS09-PC is server name

    i got same error

    Friday, May 3, 2013 6:38 AM
  • when i run the above code,i got this error.

    please help me

    Friday, May 3, 2013 7:59 AM
  • I carefully went through al the code that you posted and made changes based on code that I used before.  I removed all references to the DTS library.  This code should not give the same error message.  If it does, then the error is coming from the database.  I also changed the connection statement.  I think you need to include the folder name SQLEXpress (or equivalent folder on PC where the database is located).

    If you get the error then put a break point (F9) in the code below and step (F11) through this module to find verify if this code is generating your error.

    using System;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Data.OleDb;
    namespace ConsoleApplication1
    {
        class Program
        {
            OleDbConnection conn;
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Documents and Settings\ST84879\Desktop\ROBERT_DATA_SET\TEST.xls");
            }
            private void ExcelRefresh(string Filename)
            {
                var excelApp = new Excel.Application();            
                excelApp.DisplayAlerts = false;
                Excel.Workbook excelbk = excelApp.Workbooks._Open(Filename,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                //Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                //Workbook.Save();
                Workbook.Close(true, Type.Missing, Type.Missing);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                conn = new OleDbConnection();
                conn.ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";
                conn.Open();
                conn.Close();
     
            }
        }
    }


    jdweng

    Friday, May 3, 2013 9:09 AM
  • i am new to c#

    i got bellow error.

    The name 'Workbook' does not exist in the current context

    can you please tell me the which reference i need to add

    i already added

    Microsoft.Office.Interop.Excel
     
    Friday, May 3, 2013 11:23 AM
  • Sorry, I forgot to change the line below

    From : Excel.Workbook excelbk =

    To : Excel.Workbook  Workbook =

    or

    From :

                 Workbook.Close(true, Type.Missing, Type.Missing);
                excelApp
    .Quit();
               
    Workbook = null;
    to :

                excelbk.Close(true, Type.Missing, Type.Missing);
                excelApp
    .Quit();
               
    excelbk = null;


    jdweng

    Friday, May 3, 2013 12:06 PM
  • i added the above code

    then build succeed

    when i run the task same error came 

    Friday, May 3, 2013 12:29 PM
  • I'm not sure which of the two errors you are still getting.  I made the workbook object in the code below excelbk.  Note line 3 below I have "Excel =" which is a shortcut so you son't have to type the full name of the Interop library.  I also removed the DTS library from the to of the module (using ....).  There is no refernce to the DTS library in the code below so you should not get the DTS error.  If you do then the problem is some place also in the code.

    using System;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Data.OleDb;
    namespace ConsoleApplication1
    {
        class Program
        {
            OleDbConnection conn;
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Documents and Settings\ST84879\Desktop\ROBERT_DATA_SET\TEST.xls");
            }
            private void ExcelRefresh(string Filename)
            {
                var excelApp = new Excel.Application();            
                excelApp.DisplayAlerts = false;
                Excel.Workbook excelbk = excelApp.Workbooks._Open(Filename,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                //Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                //Workbook.Save();
                excelbk.Close(true, Type.Missing, Type.Missing);
                excelApp.Quit();
                excelbk = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                conn = new OleDbConnection();
                conn.ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";
                conn.Open();
                conn.Close();
     
            }
        }
    }


    jdweng

    Friday, May 3, 2013 12:52 PM
  • Hi Ramyakranthi,

    you have to add two refreences to the code one is Microsoft.Office.Core and Microsoft.Office.Interop.Excel

    by downloading office namespace fro c# from internet.

    Office assemblies.

    2007

    http://www.microsoft.com/en-gb/download/details.aspx?id=18346

    2010

    http://www.microsoft.com/en-gb/download/details.aspx?id=3508

    Or search in google as

    o2007pia msi download 

    After downloading the assemblies go to the c# script and add the references in openeing project explorer for c# code


    Sri.Tummala

    • Marked as answer by kali786 Friday, May 3, 2013 1:12 PM
    Friday, May 3, 2013 1:12 PM
  • i am new to c#

    i got bellow error.

    The name 'Workbook' does not exist in the current context

    can you please tell me the which reference i need to add

    i already added

    Microsoft.Office.Interop.Excel
     

    Hi Ramyakranthi,

    you have to add two refreences to the code one is Microsoft.Office.Core and Microsoft.Office.Interop.Excel

    by downloading office namespace fro c# from internet.

    Office assemblies.

    2007

    http://www.microsoft.com/en-gb/download/details.aspx?id=18346

    2010

    http://www.microsoft.com/en-gb/download/details.aspx?id=3508

    Or search in google as

    o2007pia msi download 

    After downloading the assemblies go to the c# script and add the references in openeing project explorer for c# code


    Sri.Tummala



    Sri.Tummala

    Friday, May 3, 2013 1:13 PM
  • Hi kali

    u posted the code but have u tried using script task in ssis.

    excel refresh data from sql table,

    if so could u please test in script task and post the code.

    if you do that is very grateful to me.

    Saturday, May 4, 2013 5:43 AM
  • using System;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Data.OleDb;
    namespace ConsoleApplication1
    {
        class Program
        {
            OleDbConnection conn;
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\DATA.xls");
            }
            private void ExcelRefresh(string Filename)
            {
                var excelApp = new Excel.Application();
                excelApp.DisplayAlerts = false;
                Excel.Workbook excelbk = excelApp.Workbooks._Open(Filename,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                //Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                //Workbook.Save();
                excelbk.Close(true, Type.Missing, Type.Missing);
                excelApp.Quit();
                excelbk = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                conn = new OleDbConnection();
                conn.ConnectionString = "Data Source=ADS09-PC;Initial Catalog=SAMPLE;Integrated Security=SSPI";
                conn.Open();
                conn.Close();
    
            }
        }
    }

    the above is the what i used in script component in ssis

    used References

    microsoft.office.interop.excel

    system

    system.data

    system.windows.forms

    entry point

    main

    These are the details what i used but

    when i build the code it sucessed

    when i execute the task i got bellow error

    if possible can you please send the tested code in ssis script task

    requirement is

    excel workbook refreshment data from the sql server table using script task in ssis

    iam facing same problem since 3 days

    please help me.

    Saturday, May 4, 2013 6:34 AM
  • New c# code which takes 20 seconds to refresh and save the excel file.

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    namespace ST_53932a75e92c44f086535fc017a56e6a.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
            /*
    		The execution engine calls this method when the task executes.
    		To access the object model, use the Dts property. Connections, variables, events,
    		and logging features are available as members of the Dts property as shown in the following examples.
    		To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    		To post a log entry, call Dts.Log("This is my log text", 999, null);
    		To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    		To use the connections collection use something like the following:
    		ConnectionManager cm = Dts.Connections.Add("OLEDB");
    		cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    		Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    		
    		To open Help, press F1.
    	*/
             
            
            
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Documents and Settings\ST84879\Desktop\ROBERT_DATA_SET\Active.xls");
                Dts.TaskResult = (int)ScriptResults.Success;
            }
         
            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);
                         
                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
        }
    }

    you have to add two refreences to the code one is Microsoft.Office.Core and Microsoft.Office.Interop.Excel

    by downloading office namespace fro c# from internet.

    Office assemblies.

    2007

    http://www.microsoft.com/en-gb/download/details.aspx?id=18346

    2010

    http://www.microsoft.com/en-gb/download/details.aspx?id=3508

    Or search in google as

    o2007pia msi download 

    After downloading the assemblies go to the c# script and add the references in openeing project explorer for c# code



    Sri.Tummala

    • Marked as answer by kali786 Wednesday, May 8, 2013 10:49 AM
    Wednesday, May 8, 2013 10:49 AM
  • I'm trying to get this going in a new environment with VS 2013 (SSDT - BI) and Excel 2013. I've tried installing the Office 2010 PIA.  But I still can't see those Microsoft.Office.* Assemblies in the Reference Manager for the Script Task.

    Does anyone know how to get this working in this environment?

    Friday, August 15, 2014 5:53 AM
  • I'm trying to get this going in a new environment with VS 2013 (SSDT - BI) and Excel 2013. I've tried installing the Office 2010 PIA.  But I still can't see those Microsoft.Office.* Assemblies in the Reference Manager for the Script Task.

    Does anyone know how to get this working in this environment?

    Hi Mike

    I have in the same situation than you and I achieve my goal using this code

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;
    namespace ST_53932a75e92c44f086535fc017a56e6a.csproj
    {
        [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
       /* [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]*/
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
            /*
    		The execution engine calls this method when the task executes.
    		To access the object model, use the Dts property. Connections, variables, events,
    		and logging features are available as members of the Dts property as shown in the following examples.
    		To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    		To post a log entry, call Dts.Log("This is my log text", 999, null);
    		To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    		To use the connections collection use something like the following:
    		ConnectionManager cm = Dts.Connections.Add("OLEDB");
    		cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    		Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    		
    		To open Help, press F1.
    	*/
    
    
    
            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Comunio\Equipos.xlsx");
                Dts.TaskResult = (int)ScriptResults.Success;
            }
    
            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                System.Threading.Thread.Sleep(80000);
    
                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
        }
    }

    In the first momento it seemed that it didn´t work but after test with the time to wait for refresh the file I see it works fine.

    I hope It can be useful for you too.

    Regards

    Tuesday, September 15, 2015 7:56 PM
  • Thanks man,

    I was able to use the code. As I work with reporting and databases I only use c# if I need it for my script tasks, I do not completely understand how it works but it did the trick. I also had to do few changes. My complete script task code is below. I had to add some references in order to list them at the beginning. I used this code to refresh an .xlsm file which gets data from the database with the multiple queries and populates several pivot tables and use macro to adjust chart axis.

    The code is below:

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.SqlServer.Dts.Tasks;
    using Microsoft.CSharp;
    using System.Collections;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;

    namespace ST_53932a75e92c44f086535fc017a56e6a.csproj
    {
        [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            #region VSTA generated code
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion
            /*
    The execution engine calls this method when the task executes.
    To access the object model, use the Dts property. Connections, variables, events,
    and logging features are available as members of the Dts property as shown in the following examples.
    To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    To post a log entry, call Dts.Log("This is my log text", 999, null);
    To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    To use the connections collection use something like the following:
    ConnectionManager cm = Dts.Connections.Add("OLEDB");
    cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    To open Help, press F1.
    */



            public void Main()
            {
                // TODO: Add your code here
                ExcelRefresh(@"C:\Users\doniluga\Desktop\5A5 STEAK LOUNGE 08-2017 MAR Summary.xlsm");
                Dts.TaskResult = (int)ScriptResults.Success;
            }

            private void ExcelRefresh(string Filename)
            {
                object NullValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.DisplayAlerts = false;
                Microsoft.Office.Interop.Excel.Workbook Workbook = excelApp.Workbooks.Open(
                   Filename, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue,
                   NullValue, NullValue, NullValue, NullValue, NullValue);
                Workbook.RefreshAll();
                System.Threading.Thread.Sleep(20000);

                Workbook.Save();
                Workbook.Close(false, Filename, null);
                excelApp.Quit();
                Workbook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }
        }
    }

    Thursday, February 1, 2018 1:12 AM