Jacob：在Excel文件中调用vb函数，而不调用“打开”声明 [英] Jacob : calling vb function in Excel file without invoking "Open" statement

问题描述

我使用Jacob调用位于Excel文件中的一个宏的VB函数。

这是我的代码：[我传递文件和vb函数名称作为参考下面的java方法]

  private static void callExcelMacro（File file，String macroName）{
 ComThread.InitSTA（）; 
 
 final ActiveXComponent excel = new ActiveXComponent（Excel.Application）; 
 
 try {
 //如果属性设置为true，则会打开excel 
 excel.setProperty（Visible，new Variant（false））; 
 
 
 final Dispatch workbooks = excel.getProperty（Workbooks）。getDispatch（）; 
 // String eventSink = null; 
 int id = Dispatch.get（工作簿，Count）。getInt（）; 
 System.out.println（le nbre+ id）; 
 Dispatch.call（工作簿，添加）; 
调度workBook = Dispatch.call（工作簿，Open，file.getAbsolutePath（））。toDispatch（）; 
 // new DispatchEvents（sourceOfEvent，eventSink，progId）
 
 // new DispatchEvents（workBook，w，Excel.Application）; 
 //System.out.println(\"lerésultat+ eventSink）; 
 
 //d.safeRelease（）; 
 Variant V1 = new Variant（file.getName（）+ macroName）; 
 //调用宏
 final Variant result = Dispatch.call（excel，Run，V1）; 
 
 //保存并关闭
 //Dispatch.call(workBook，Save）; 
 
 com.jacob.com.Variant f = new com.jacob.com.Variant（true）; 
 // Dispatch.call（workBook，Close，f）; 
 
} catch（Exception e）{
 e.printStackTrace（）; 
} finally {
 
 excel.invoke（Quit，new Variant [0]）; 
 ComThread.Release（）; 
} 
} 
  

代码运行正常，但我的问题是我不想打电话给

 调度工作簿= Dispatch.call（工作簿，打开，file.getAbsolutePath（） ）.toDispatch（）; 
  

其中显示了默认宏执行的操作（带有输入字段的接口）。

有没有办法运行VB功能而不打开Excel文件？

谢谢。

解决方案

有没有办法在Excel工作簿中执行VBA功能，而不打开文件...

当然，您可以通过禁用Excel Application对象上的事件来阻止AUto_open宏运行。

在Excel VBA中我们这样做：

  Application.enableevents = false 
  

（经常与其他设置，如ScreenUpdating和DisplayAlerts）

也许你使用：

  excel.setProperty（EnableEvents，new Variant（false））; 
  

我希望指针你的方向正确（哈哈，繁荣热潮！）

Philip

I'm using Jacob to call a VB function that resides in a Macro in an Excel file.

Here is my code : [I pass the file and the vb function name as paramaters to the below java method]

private static void callExcelMacro(File file, String macroName) {
    ComThread.InitSTA();

    final ActiveXComponent excel = new ActiveXComponent("Excel.Application");

    try {
        // This will open the excel if the property is set to true
        excel.setProperty("Visible", new Variant(false));


        final Dispatch workbooks = excel.getProperty("Workbooks").getDispatch();
        //String    eventSink = null ;
        int id = Dispatch.get(workbooks, "Count").getInt();
        System.out.println("le nbre" + id);
        Dispatch.call(workbooks, "Add");
        Dispatch workBook = Dispatch.call(workbooks, "Open", file.getAbsolutePath()).toDispatch();
        //new DispatchEvents(sourceOfEvent, eventSink, progId)

        //new DispatchEvents(workBook, w , "Excel.Application");
        //System.out.println("le résultat"+eventSink);      

        //d.safeRelease();
        Variant V1 = new Variant( file.getName() + macroName);
        // Calls the macro
        final Variant result = Dispatch.call(excel, "Run", V1);

        // Saves and closes
        //Dispatch.call(workBook, "Save");

        com.jacob.com.Variant f = new com.jacob.com.Variant(true);
        //  Dispatch.call(workBook, "Close", f);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {

        excel.invoke("Quit", new Variant[0]);
        ComThread.Release();
    }
}

The code runs fine, but my problem is that I don't want to call the instrcution

Dispatch workBook = Dispatch.call(workbooks, "Open", file.getAbsolutePath()).toDispatch();

which displays me what the default macro executes (an Interface with input fields).

Is there a way to "Run" the VB function without "Opening" the Excel file ?

Thanks.

解决方案

there is no way to execute a VBA function in an Excel workbook without opening the file...

Of course, you can prevent the AUto_open macro running by disabling events on the Excel Application object.

In Excel VBA we do this like so:

Application.enableevents=false

(frequently in conjunction with other settings like ScreenUpdating and DisplayAlerts)

in Java maybe you use:

excel.setProperty("EnableEvents", new Variant(false));

I hope that pointers you in the right direction (lol, boom boom !)

Philip

这篇关于Jacob：在Excel文件中调用vb函数，而不调用“打开”声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！