vbScript打开excel但不加载宏/模块？ [英] vbScript opens up excel but doesn't load macro/modules?

问题描述

我在一个非常奇怪的情况下。我创建了一个vbs脚本来打开我的excel文件。我已经在WorkBook_open方法中定义了vba代码。我以为创建一个vbs脚本打开我的excel将调用我的workBook_open方法并在其中执行vba代码。但我错了以下是我的vbs代码。

  filePath =E：\data_extracts\mydata.xlsm
设置oExcel = CreateObject（Excel.Application）
 oExcel.Workbooks.Open（filepath）
 oExcel.Visible = True 
 oExcel.RunRefreshDataFromIQY
 oExcel.ActiveWorkbook.Save 
 oExcel.ActiveWorkbook.Close 
 oExcel.Quit 
设置oExcel =没有
  

在调试时，它在oExcel.RunRefreshDataFromIQY中失败，表示这两个宏不可用或禁用。因此，代码只是让excel应用程序成功完成，这就是它所做的一切。我在module1，module2中有宏代码。如何/在哪里编写以执行我的宏在vbs脚本下面。我的宏/模块必须按顺序执行，我的一些宏是记录的宏。任何帮助深表感谢。谢谢。

感谢您的输入Scott。这是我对我的代码进行了更改

  Dim oExcelApp 
 Dim oExcelWkb 
 
 set oExcelApp = createobject（Excel.Application）
 set oExcelWkb = oExcelApp.Workbooks.Open（\\myserver\data_extracts\TestTOPTMay307.xlsm）
 oExcelWkb.Close True 
 oExcelApp.Quit 
  

但是从命令行运行它，它给我运行时错误对象需要：'关闭。任何想法为什么？为什么不关闭？我究竟做错了什么？谢谢。

解决方案

我刚刚测试了您的代码与我做的一个虚拟文件。当我将代码放在模块中并将其留作公开时，它工作。然而，当我把一个私人模块 - >像工作表级别模块，我得到你的错误。

但是，当我引用私有对象时，代码就会遍历。所以我的答案是取代

  oExcel.RunRefreshDataFromIQY
  / pre> 
 
 
与
  oExcel.Run[yourClassName]。 RefreshDataFromIQY
  
此外，我还在我的文件中放置了一个workbook_event。事件触发成功打开，所以如果你的麻烦，这很可能在事件中的代码。
 
I m in a very weird situation. I created a vbs script that would open my excel file. I had defined vba code in WorkBook_open method. I thought creating a vbs script to open up my excel would invoke my workBook_open method and execute the vba code inside it. But I was wrong. Below is my vbs code.
filePath = "E:\data_extracts\mydata.xlsm"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
oExcel.Visible = True
oExcel.Run "RefreshDataFromIQY"
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
Set oExcel = Nothing
On debugging, it fails at oExcel.Run "RefreshDataFromIQY" saying either macros are not available or disabled. Hence it is the code just opnes up excel application successfully and that's all it does. I have macro codes in module1, module2. How/where do I write to execute my macros in vbs script below. My macros/modules have to be executed in sequence and some of my macros are recorded macros. Any help is much appreciated. Thanks.

Thanks for your input Scott. Here's what I made changes to my code
Dim oExcelApp
Dim oExcelWkb

set oExcelApp = createobject("Excel.Application")
set oExcelWkb = oExcelApp.Workbooks.Open("\\myserver\data_extracts\TestTOPTMay307.xlsm")
oExcelWkb.Close True
oExcelApp.Quit
However on running it from command line, its giving me runtime error Object required: 'Close'. Any idea why? Why is it failing to Close? What am i doing wrong? Thanks.
解决方案
I just tested your code against a dummy file I made. It worked when I placed the code inside a  module and left it as public. However, when I put into a private module -> like worksheet level module, I got the error you got. 

However, when I referenced the private object, the code ran through. So my answer to you is to replace
oExcel.Run "RefreshDataFromIQY"
With
oExcel.Run "[yourClassName].RefreshDataFromIQY"
Also, I placed a workbook_event in my file as well. The event triggered successfully on open, so if there is trouble with yours, it's most likely in the code inside the event.

                        
这篇关于vbScript打开excel但不加载宏/模块？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！