vba 如果使用 vb 脚本调用宏将被禁用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10530299/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro gets disabled if called using a vb script
提问by justin3250
I am trying to call my excel macro using vbs. Here is a snippet of my code.
我正在尝试使用 vbs 调用我的 excel 宏。这是我的代码片段。
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Folder\Test_PO.xls")
objExcel.Application.Visible = True
objExcel.Application.Run "C:\Folder\Test_PO.xls!Data_Analysis"
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit
Now the problem here is that i am able to open the file but the macro somehow gets disabled here and shows me 'macro may not be present or may be disabled'. I am sure i am calling correct macro name but as soon as the file is opened the Add-ins tab where i had configured the macro to run from gets dissapeared.This does not open if i open the file manually , i can see the tab and run the macro from the tab itself. Any suggestions how i could overcome this problem and get the macro to run ?
现在这里的问题是我能够打开文件，但宏不知何故在此处被禁用并显示给我'macro may not be present or may be disabled'。我确定我正在调用正确的宏名称，但是一旦打开文件，我将宏配置为从中运行的加载项选项卡就会消失。如果我手动打开文件，这不会打开，我可以看到选项卡并从选项卡本身运行宏。我有什么建议可以克服这个问题并使宏运行吗？
回答by Siddharth Rout
Try this
尝试这个
Dim objExcel, objWorkbook
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Folder\Test_PO.xls")
objExcel.Visible = True
objExcel.Run "Data_Analysis"
objWorkbook.Close
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing
WScript.Echo "Finished."
WScript.Quit
EDIT
编辑
If the macro is in a module then the above will help. If the macro is in a sheet say, Sheet1 then replace the line
如果宏在模块中，则上述内容会有所帮助。如果宏在工作表中，请说 Sheet1 然后替换该行
objExcel.Run "Data_Analysis"
with
和
objExcel.Run "sheet1.Data_Analysis"
FOLLOWUP
跟进
Try this code.
试试这个代码。
Dim objExcel, objWorkbook, ad, FilePath
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
For Each ad In objExcel.AddIns
If ad.Name = "Converteam.xla" Then
FilePath = ad.Path & "\Converteam.xla"
Exit For
End If
Next
objExcel.Workbooks.Open (FilePath)
Set objWorkbook = objExcel.Workbooks.Open("C:\Folder\Test_PO.xls")
objExcel.Run "Data_Analysis_Converteam"
objWorkbook.Close
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing
WScript.Echo "Finished."
WScript.Quit
EXPLANATION:
说明：
When you use CreateObject, the Add-Ins are not installed by default. Please see this link.
使用 时CreateObject，默认情况下不会安装加载项。请看这个链接。
Topic: Add-ins do not load when using the CreateObject command in Excel
主题：在 Excel 中使用 CreateObject 命令时加载项不加载
Link: http://support.microsoft.com/kb/213489/
链接：http: //support.microsoft.com/kb/213489/
You have to load the Add-In and then call the relevant macro. Also the name of your macro is not Data_Analysisbut Data_Analysis_Converteam
您必须加载加载项，然后调用相关的宏。您的宏的名称也不是Data_Analysis但Data_Analysis_Converteam
HTH
HTH
回答by assylias
To add to Siddhart's answer - you can load the addins you require in a VBScript like this:
要添加到 Siddhart 的答案 - 您可以像这样在 VBScript 中加载您需要的插件：
objExcel.RegisterXLL("analys32.xll") 'For XLL addins
objExcel.Workbooks.Open(objExcel.LibraryPath & "\analysis\atpvbaen.xla") 'For standard XLA addins
objExcel.Workbooks.Open("C:\Program Files\MyAddins\MyAddin.xla") 'for custom XLA addins
