Run a macro program in Excel using SharePoint Timer

Demand:
Because of the large amount of data in Excel, which contains several macro programs to read data from other data sources, running a macro takes a long time, in order not to affect the use, the need to run the Excel macro (no parameters) every night, Excel on the common disk.

Solution:
SharePoint wrote a timejob,timejob every day to run a macro.
Write Timejob Reference http://www.cnblogs.com/batter152/p/4705316.html

Problems that you may encounter at run time
Error–retrieving the COM CLASS FACTORY for COMPONENT and CLSID {00020812-0000-0000-c000-000000000046} FAILED DUE to the Following error:80070005 ACCESS is DENIED. (EXCEPTION from hresult:0x80070005 (e_accessdenied)).
or an error without permission, or an error.
Microsoft Excel cannot access the Filee is several possible reasons: the file name or path does not exist. the file was being used by another program. the workbook Save has the same name as a currently open workbook.

This can be solved by doing the following two steps:

1. Create a desktop shortcut
You need to create a folder manually, and put the Excel run shortcut under this folder
64-bit system: C:\Windows\SysWOW64\config\systemprofile\Desktop
32 for the system: C:\Windows\System32\config\systemprofile\Desktop

2. Modify the permissions of the application program
64-bit system running DCOMCNFG
32-bit system run MMC-32, and then add Component Services
Expand services–> computers–> My computer–> DCOM Config For example, this account is IIS80 Port application pool account

Locate Microsoft Excel application CLSID {00020812-0000-0000-c000-000000000046}
Point Security tab Modification, point Identity tab modified as follows

You can also change the default, right-click My Computer

The following code is attached to the C # run macro program

Reference

Using Excel = Microsoft.Office.Interop.Excel;
Using Microsoft.Office.Core;

DLL:Microsoft.Office.Interop.Excel

and set the DLL property embed Interop types to false, otherwise it will also error

1. How to use

protected void Page_Load (object sender, EventArgs e)        {            try            {                //return object                Objrtn = new Object (); c5/>//gets a Excelmacrohelper object                //executes the macro in the specified Excel, and displays Excel                spsecurity.runwithelevatedprivileges when executed (delegate ()                {                    //runmoc (@ "C:\Library\test.xlsm", "Thisworkbook.gettime", NULL, out Objrtn, false);                    String Source = @ "\\xxxxx\Test\TEST.xlsm";                    String destination = @ "C:\LIBRARY\TEST.XLSM";                    File.Copy (destination,source,true);                    Runmoc (source, "test.xlsm! Test.test ", NULL, out Objrtn, false);                                    });            catch (System.Exception ex)            {                lb_error. Text = ex. Message;            }        }

2. Operating methods

<summary>///Perform macros in Excel///</summary>//<param name= "Excelfilepath" >excel file Path </param>//<param name= "MacroName" > Macro name </param>//<param name= "parameters" > macro parameter Group </param>//<param name= "Rtnvalue" > Macro return value </param>//<param name= "Isshowexcel" > execution Show excel</param> public void Runmoc (String excelfilepath,string macroname,object[] Parameters,out object RtnVa Lue,bool isshowexcel) {//Create Excel Object Example Excel.applicationclass oexcel = new Excel.applicationc            Lass (); try {//check if the file exists if (!                File.exists (Excelfilepath)) {throw new System.Exception (Excelfilepath + "file does not exist"); #region Call macro to process//prepare to open Excel file when default Parameter object omissing = SYSTEM.R Eflection.                Missing.Value;    Prepares the parameter group object based on whether the parameter group is empty            Object[] paraobjects;                if (parameters = = null) {paraobjects = new object[] {macroName}; } else {//macro parameter leader degree int paralength = parameters.                    Length;                    Paraobjects = new Object[paralength + 1];                    Paraobjects[0] = MacroName;                    for (int i = 0; i < paralength; i++) {paraobjects[i + 1] = parameters[i];                    }}//Determine if Excel is visible if required to execute if (isshowexcel) {                Make the created object visible oexcel.visible = true;                }//Create Workbooks object Excel.Workbooks obooks = oexcel.workbooks;                Create Workbook object Excel._workbook obook = null;                    Opens the specified Excel file obook = oBooks.Open (                    Excelfilepath, omissing,                                        omissing, omissing, omissing,                                        omissing, omissing,                                        omissing, omissing, omissing,                                        omissing, omissing,                                   omissing, omissing, omissing                ); Perform the macro Rtnvalue = this in Excel.                RunMacro (oexcel, paraobjects);                Save changes Obook.save ();                Exit Workbook Obook.close (False, omissing, omissing); #Endregion//Release Workbook object System.Runtime.InteropServices.Marshal.ReleaseComObject (obook);                obook = null;                Release Workbooks Object System.Runtime.InteropServices.Marshal.ReleaseComObject (obooks);            obooks = null;            } catch (Exception ex) {throw ex;                } finally {//close Excel oExcel.Quit ();                Releases the Excel object System.Runtime.InteropServices.Marshal.ReleaseComObject (oexcel);                oexcel = null; Call garbage collection GC.            Collect (); }}///<summary>//Executive macro///</summary>//<param name= "oapp" >excel Objects </param>///<param Name= "Orunargs" > Parameters (the first parameter is the specified macro name followed by the parameter value of the specified macro) </param>//&LT;RETURNS&G    t; macro return value </returns> Private Object RunMacro (Object oapp, object[] orunargs)    {try {//declares a return object Objrtn; Reflection mode performs macro Objrtn = Oapp.gettype ().                                                        InvokeMember ("Run",                                                        System.Reflection.BindingFlags.Default |                                                        System.Reflection.BindingFlags.InvokeMethod, NULL,                                                     Oapp, Orunargs                );            Returns the value return OBJRTN; } catch (Exception ex) {//If there is an underlying exception, throw the underlying exception if (ex. InnerException.Message.ToString (). Length > 0) {throw ex.                innerexception;   } else {                 Throw ex; }            }        }

3.VBA code, module name is test

Sub Test ()  with Thisworkbook.sheets ("Sheet1")    =. Cells (1111121        2) =        time3 Date    End  with End Sub

　　

Run a macro program in Excel using SharePoint Timer