问题描述
限时送ChatGPT账号..我正在尝试使用 vbs 调用我的 excel 宏.这是我的代码片段.
I am trying to call my excel macro using vbs. Here is a snippet of my code.
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 ?
推荐答案
试试这个
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
编辑
如果宏在一个模块中,那么上述内容会有所帮助.如果宏在工作表中,请说 Sheet1 然后替换该行
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
objExcel.Run "Data_Analysis"
与
objExcel.Run "sheet1.Data_Analysis"
跟进
试试这个代码.
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
解释:
当您使用 CreateObject
时,默认情况下不会安装加载项.请参阅此链接.
When you use CreateObject
, the Add-Ins are not installed by default. Please see this link.
主题:在 Excel 中使用 CreateObject 命令时加载项不加载
链接:http://support.microsoft/kb/213489/
您必须加载加载项,然后调用相关宏.此外,您的宏的名称不是 Data_Analysis
而是 Data_Analysis_Converteam
You have to load the Add-In and then call the relevant macro. Also the name of your macro is not Data_Analysis
but Data_Analysis_Converteam
HTH
这篇关于如果使用 vb 脚本调用宏将被禁用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
更多推荐
[db:关键词]
发布评论