这可能是一个很简单的问题，但我一直在努力，去年4-5小时W / O型的成功。 ：（

我有一个简单的打开一个excel文件，一个C＃控制台应用程序。这个excel文件有Workbook_Open（）事件，它运行我的宏。我的宏仅重命名工作表Sheet1到RenameSheet1在活动工作表。

我可以运行从IDE我的C＃项目。我要运行这个项目从SQL作业（SQL Server 2008中）。我该怎么办呢？请帮我得到这个工作。谢谢你。

根据SilverNinnjas的建议创建一个代理帐户：

- 创建一个包含域帐户凭据CORP \ PowerUser1和密码

  CREATE CREDENTIAL PowerUser1具有标识= N'CORP \ shress2'，SECRET = N'P @ ssw0rd
走
使用[MSDB]
走
 

- 创建一个新的代理名为ExcelProxy并指派高级用户凭据它

  EXEC msdb.dbo.sp_add_proxy
@ proxy_name = N'ExcelProxy'，
@ credential_name = N'PowerUser1'，
@启用= 1
 

- 格兰特ExcelProxy访问的CmdExec子系统

  EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@ proxy_name = N'ExcelProxy'，
@subsystem_name = N'CmdExec'
 

- 授予登录的为testUser使用权限ExcelProxy

  EXEC msdb.dbo.sp_grant_login_to_proxy
@login_name = N'shress2'，
@ proxy_name = N'ExcelProxy'
走
 

我米仍然得到同样的错误 xecuted为用户：股份有限公司\ shress2。

未处理的异常信息：System.Runtime.InteropServices.COMException：Microsoft Excel不能访问该文件   E：\ data_extracts \ RenameSheets.xlsm。   有几种可能的原因：   文件名或路径不存在。   该文件正被另一个程序。   您试图保存工作簿具有相同的名称为当前打开的工作簿。在Microsoft.Office.Interop.Excel.Workbooks.Open（字符串文件名，对象UpdateLinks，只读对象，对象格式，对象口令，对象WriteResPassword，对象IgnoreReadOnlyRecommended，对象起源，对象分隔符，可编辑的对象，对象通知，目标转换器，对象AddToMru，本地对象，对象CorruptLoad）在T_OpenExcel.Program.Main（字串[] args）在C：\ Users \用户shress2 \文档\ Visual Studio 2010的\项目\ T_OpenExcel \ T_OpenExcel \的Program.cs：行24进程退出$ C $Ç-532462766。该步骤失败。

任何原因？我热切地等待着任何反馈。非常感谢。

@SilverNinja， 下面是我的C＃code：

 使用系统;
使用System.Collections.Generic;
使用System.Linq的;
使用System.Text;
使用Excel =的Microsoft.Office.Interop.Excel;
使用的System.Threading;


命名空间T_OpenExcel
{
类节目
{
    静态无效的主要（字串[] args）
    {

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        //Excel.Worksheet xlWorkSheet;

        反对misValue = System.Reflection.Missing.Value;

        xlApp =新Excel.Application（）;
        xlApp.Visible = TRUE;
        xlWorkBook = xlApp.Workbooks.Open（\\\\ MYSERVER \\ data_extracts \\ RenameSheets.xlsm，0，假，5，，，假的，Microsoft.Office.Interop.Excel.XlPlatform.xlWindows， \ t的，假的，假的，0，真正的，1，0）;

       xlApp.DisplayAlerts = FALSE;
       xlWorkBook.SaveAs（\\\\ MYSERVER \\ data_extracts \\ RenameSheets.xlsm，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Excel.XlSaveAsAccessMode.xlNoChange，Type.Missing， Type.Missing，Type.Missing，Type.Missing，Type.Missing）;


                  xlWorkBook.Close（真，misValue，misValue）;
        xlApp.DisplayAlerts = TRUE;

        xlApp.Quit（）;
    }

    私有静态无效RunMacro（Excel.Workbook xlWorkBook，对象[] P）
    {
        //抛出新的NotImplementedException（）;
    }
}
}
 

解决方案

您只需要选择合适的作业类型的在新建作业步骤编辑。你可以使用任何的的Powershell 或的CmdExec

在命令区域，单击打开按钮以找到您的控制台应用程序编译成可执行文件（EXE）。

如果您有任何参数，在这里添加它们 - 否则配置计划

您可能需要使用提升的权限。要使用提升的权限，只是导航到安全 - >证书在SSMS中，然后右键单击新证书。接下来，配置在代理帐户在 Sql Server的代理 - >代理，然后用鼠标右键单击新代理。配置代理的的CmdExec 并使用证书您previously设置。在SQL代理作业步骤中，您可以选择此凭据运行您的命令时使用。

在SQL作业步骤命令区，你应该输入是这样的：

 练成E：\ data_extracts \ RenameSheets.xlsm
 

This might be a very simple question but I have been trying for last 4-5 hours w/o success. :(

I have a C# console application that simply opens an excel file. This excel file has Workbook_Open() event, which runs my macro. My macro simply renames sheet1 to RenameSheet1 in an active worksheet.

I could run my C# project from IDE. I want to run this project from SQL job(SQL server 2008). How do I do it? Please help me get this working. Thanks.

As per SilverNinnjas' suggestions to create a proxy account:

-- Create a credential containing the domain account CORP\PowerUser1 and its password

CREATE CREDENTIAL PowerUser1 WITH IDENTITY = N'CORP\shress2', SECRET = N'P@ssw0rd'
GO
USE [msdb]
GO

-- Create a new proxy called ExcelProxy and assign the PowerUser credential to it

EXEC msdb.dbo.sp_add_proxy 
@proxy_name=N'ExcelProxy',
@credential_name=N'PowerUser1',
@enabled=1

-- Grant ExcelProxy access to the "CmdExec" subsystem

EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'ExcelProxy', 
@subsystem_name =N'CmdExec'

-- Grant the login testUser the permissions to use ExcelProxy

EXEC msdb.dbo.sp_grant_login_to_proxy 
@login_name = N'shress2', 
@proxy_name=N'ExcelProxy'
GO

I m still getting the same error xecuted as user: CORP\shress2.

Unhandled Exception: System.Runtime.InteropServices.COMException: Microsoft Excel cannot access the file 'E:\data_extracts\RenameSheets.xlsm'. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook. at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad) at T_OpenExcel.Program.Main(String[] args) in C:\Users\shress2\documents\visual studio 2010\projects\T_OpenExcel\T_OpenExcel\Program.cs:line 24. Process Exit Code -532462766. The step failed.

Any reasons why? I am eagerly waiting for any feedback. Thanks a lot.

@SilverNinja, here's my C# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Threading;


namespace T_OpenExcel
{
class Program
{
    static void Main(string[] args)
    {

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        //Excel.Worksheet xlWorkSheet;

        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.Application();
        xlApp.Visible = true;
        xlWorkBook = xlApp.Workbooks.Open("\\\\myserver\\data_extracts\\RenameSheets.xlsm", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

       xlApp.DisplayAlerts = false;
       xlWorkBook.SaveAs("\\\\myserver\\data_extracts\\RenameSheets.xlsm", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


                  xlWorkBook.Close(true, misValue, misValue);
        xlApp.DisplayAlerts = true;

        xlApp.Quit();
    }

    private static void RunMacro(Excel.Workbook xlWorkBook, object[] p)
    {
        //throw new NotImplementedException();
    }
}
}

You just need to pick the appropriate Job Type in the New Job Step editor. You could use either Powershell or CmdExec.

In the Command area, click the Open button to locate your console application compiled executable (exe).

If you have any parameters, add them here - otherwise configure the schedule.

You may have to use elevated permissions. To use elevated permissions, just navigate to Security->Credentials in SSMS and right-click New Credential. Next, configure the Proxy Account under Sql Server Agent->Proxies and right-click New Proxy. Configure the Proxy for CmdExec and use the Credentials you previously setup. In your SQL Agent Job step you can pick this credential to use when running your command.

In the SQL Job Step Command Area, you should type something like this:

excel E:\data_extracts\RenameSheets.xlsm