强制Excel使用SQL查询运行宏 [英] Force Excel to run macro using SQL Query

问题描述

我需要运行包含宏的Excel文件(.xls或.xlsm)，它会减小其他excel文件的大小并将其另存为同一文件夹中的其他文件.当我从文件夹中手动打开文件时，它可以正常工作，它可以立即运行并创建缩小的文件.

I have a requirement to run the Excel file (either .xls or .xlsm) which has macros in it and it reduces the size of the other excel file and save it as different file in same folder. It works fine when I open the file manually from the folder, it runs immediately and creates the shrinked file.

但是要求是从SQL查询中打开Excel并运行它.我正在尝试通过我的SQL查询中的这两种方法来做到这一点.

But the requirement is to open the Excel from SQL query and run it. I am trying to do it with these two ways in my SQL query.

(1) EXEC [master].dbo.xp_cmdshell 'c:\temp\MyExcelShrink.xls'
(2) EXEC  xp_cmdshell  'dtexec /f c:\temp\MyPackage.dtsx'

dtsx软件包具有相同的逻辑来打开Excel并运行它，但是这些代码都不起作用.

The dtsx package has the same logic to open the Excel and run it, but none of these code working.

推荐答案

使用xp_cmdshell时，启动的过程在SQL Server服务登录名的安全上下文中运行.该帐户可能未启用桌面交互功能，或者可能缺少Excel在启动时运行宏所需的其他权限.

When you use xp_cmdshell, the process that starts runs in the security context of the SQL Server service login. That account may not have desktop interactivity enabled or may lack other permissions that Excel requires to run macros at start-up.

请参见SQL Server安全博客上的此xp_cmdshell文章有关xp_cmdshell的更多信息，包括不建议使用的建议:一般来说，您必须避免使用xp_cmdshell，如果可能，应该删除对其的任何依赖."

See this xp_cmdshell article on the SQL Server Security Blog for more information about xp_cmdshell, including a recommendation that it shouldn't be used: "Generally speaking, you must avoid using xp_cmdshell, and if possible, you should remove any dependency on it."

这篇关于强制Excel使用SQL查询运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！