我怎样才能捕捉从我的C＃代码在Microsoft Access VBA调试错误？ [英] How can I capture a Microsoft Access VBA debug error from my C# code?

问题描述

我打开几个Microsoft Access文件，并从每一个内执行功能的C＃程序

I have a C# program that opens several Microsoft Access files, and executes functions from within each one.

从本质上讲，代码看起来是这样的：

Essentially, the code looks something like this:

Microsoft.Office.Interop.Access.Application app =
    new Microsoft.Office.Interop.Access.Application();

app.Visible = true;
app.OpenCurrentDatabase(accessFileFullPath, false, "");

//Call the function
app.Eval(function);

然而，当在VBA代码中出现调试错误，我想捕获它在我的C＃ 。节目

However, when a debug error occurs in the VBA code, I would like to trap it in my C# program.

请不要的答案：捕获错误在你的VBA程序。对于我不会进入的原因，这是不可能的。

Please don't answer: "trap the error in your VBA program". For reasons that I will not get into, this is not possible.

这是我过去所使用的方法是将一个线程间歇监控句柄任何Visual Basic中调试窗口（FindWindowEx Win32函数返回一个非零值）。我不喜欢这种方法，并且不希望继续使用它。

A method that I have used in the past is to have a thread intermittently monitor for a handle to any Visual Basic Debug window (the FindWindowEx Win32 function returns a nonzero value). I do not like this method, and don't want to continue to using it.

我发现的此线程，它适用于Microsoft Excel中。从本质上讲，它使用了 Microsoft.VisualBasic.CallByName（）功能，这显然可以被困在一个try / catch块，而无需用户交互。不过，我一直没能得到这与微软Access--的工作，主要是因为我无法弄清楚如何使用这个命令来调用函数/子。

I found this thread, which applies to Microsoft Excel. In essence, it uses the Microsoft.VisualBasic.CallByName() function, which apparently can be trapped in a try/catch block, without user interaction. However, I have not been able to get this to work with Microsoft Access-- primarily because I cannot figure out how to call the function/sub using this command.

任何！建议将衷心感谢

编辑：正如我在下面的答案之一提到的，我已经试过包裹评估（）在try / catch块，我的C＃程序似乎忽略它，直到用户点击了微软的Visual Basic错误对话框中的结束按钮。我不希望任何用户交互，而是想陷阱在我的C＃程序处理VBA错误。

As I mentioned in one of the answers below, I have tried wrapping the Eval() in a try/catch block and my C# program seems to ignore it, until a user hits the "End" button on the "Microsoft Visual Basic" error dialog. I do not want any user interaction, but rather want to trap the VBA error for handling in my C# program.

推荐答案

更新：由于某些原因，我已经贴在前面的代码就只有在访问文件格式是2000年。我已经证实，这种新的代码也适用于Access 2002和2010文件的工作。

Update: For some reason, the previous code I had posted had only worked when the Access file format was 2000. I have confirmed that this new code also works with Access 2002 and 2010 files.

中的代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using VBA = Microsoft.Vbe.Interop;

namespace CaptureVBAErrorsTest
{
    class CaptureVBAErrors
    {
        public void runApp(string databaseName, string function)
        {
            VBA.VBComponent f = null;
            VBA.VBComponent f2 = null;
            Microsoft.Office.Interop.Access.Application app = null;
            object Missing = System.Reflection.Missing.Value;
            Object tempObject = null;

            try
            {
                app = new Microsoft.Office.Interop.Access.Application();
                app.Visible = true;
                app.OpenCurrentDatabase(databaseName, false, "");

                //Step 1: Programatically create a new temporary class module in the target Access file, with which to call the target function in the Access database

                //Create a Guid to append to the object name, so that in case the temporary class and module somehow get "stuck",
                //the temp objects won't interfere with other objects each other (if there are multiples).
                string tempGuid = Guid.NewGuid().ToString("N");

                f = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_ClassModule);

                //We must set the Instancing to 2-PublicNotCreatable
                f.Properties.Item("Instancing").Value = 2;
                f.Name = "TEMP_CLASS_" + tempGuid;
                f.CodeModule.AddFromString(
                    "Public Sub TempClassCall()\r\n" +
                    "   Call " + function + "\r\n" +
                    "End Sub\r\n");

                //Step 2: Append a new standard module to the target Access file, and create a public function to instantiate the class and return it.
                f2 = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
                f2.Name = "TEMP_MODULE_" + tempGuid
                f2.CodeModule.AddFromString(string.Format(
                    "Public Function instantiateTempClass_{0}() As Object\r\n" +
                    "    Set instantiateTempClass_{0} = New TEMP_CLASS_{0}\r\n" +
                    "End Function"
                    ,tempGuid));

                //Step 3: Get a reference to a new TEMP_CLASS_* object
                tempObject = app.Run("instantiateTempClass_" + tempGuid, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing);

                //Step 4: Call the method on the TEMP_CLASS_* object.
                Microsoft.VisualBasic.Interaction.CallByName(tempObject, "TempClassCall", Microsoft.VisualBasic.CallType.Method);
            }
            catch (COMException e)
            {
                MessageBox.Show("A VBA Exception occurred in file:" + e.Message);
            }
            catch (Exception e)
            {
                MessageBox.Show("A general exception has occurred: " + e.StackTrace.ToString());
            }
            finally
            {
                //Clean up
                if (f != null)
                {
                    app.VBE.ActiveVBProject.VBComponents.Remove(f);
                    Marshal.FinalReleaseComObject(f);
                }

                if (f2 != null)
                {
                    app.VBE.ActiveVBProject.VBComponents.Remove(f2);
                    Marshal.FinalReleaseComObject(f2);
                }

                if (tempObject != null) Marshal.FinalReleaseComObject(tempObject);

                if (app != null)
                {
                    //Step 5: When you close the database, you call Application.Quit() with acQuitSaveNone, so none of the VBA code you just created gets saved.
                    app.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
                    Marshal.FinalReleaseComObject(app);
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}

< STRONG>的详细信息：

据的我挂了由迈克·罗森布拉姆，CallByName到的线程（）可以执行办公室代码C＃和可以捕获VBA异常（ Application.Run（）和 Application.Eval（）似乎用户与调试窗口交互后），只抓到。问题是，CallByName（）需要一个[实例]对象上调用的方法。默认情况下，Excel中有的ThisWorkbook 对象，这是在打开工作簿实例化。 Access没有类似的对象，它是可访问的，据我所知。

According to the thread I had linked to by Mike Rosenblum, CallByName() can execute Office code in C#, and can trap VBA exceptions (Application.Run() and Application.Eval() seem to only get caught after the user interacts with the debug window). The problem is that CallByName() requires an [instantiated] object to call a method on. By default, Excel has the ThisWorkbook object, which is instantiated upon opening a workbook. Access does not have a similar object that is accessible, as far as I know.

的在同一个线程随后的后表明动态地添加代码到Excel工作簿，以便调用的标准模块中的方法。在的ThisWorkbook 这样做是比较琐碎，因为拥有的ThisWorkbook代码隐藏，并自动实例化。 ？但是，我们如何能做到这一点的访问

A subsequent post on the same thread suggests adding code dynamically to the Excel workbook to allow calling of methods in standard modules. Doing so on ThisWorkbook is relatively trivial, because ThisWorkbook has a code-behind and is automatically instantiated. But how can we do this in Access?

该解决方案结合了这两种技术在上面的方式如下：

The solution combines the two techniques above in the following way:

1. 编程方式创建一个新的临时类目标访问文件模块，与调用Access数据库中的目标函数。请记住，这个类的实例化属性必须设置为 2 - PublicNotCreatable 。这意味着，类不是该项目的可创建之外，但它是公共访问。


2. 附加一个新的标准模块到目标Access文件，并创建一个公共函数实例化的类并返回它。


3. 获取你的C＃代码到对象的引用，通过调用步骤VBA代码（2）。这可以使用Access互操作的Application.Run（）来完成。


4. 通话从对象上的方法（3）使用CallByName--它调用的标准模块中的方法，并且是可捕获


5. 当您关闭数据库，你叫 Application.Quit（）与 acQuitSaveNone ，所以没有刚创建获取保存的VBA代码。

1. Programatically create a new temporary class module in the target Access file, with which to call the target function in the Access database. Keep in mind that the Instancing property of the class must be set to 2 - PublicNotCreatable. This means that the class is not creatable outside of that project, but it is accessible publicly.
2. Append a new standard module to the target Access file, and create a public function to instantiate the class and return it.
3. Get a reference to the object in your C# code, by calling the VBA code in step (2). This can be done using Access interop's Application.Run().
4. Call the method on the object from (3) using CallByName-- which calls the method in the standard module, and is trappable.
5. When you close the database, you call Application.Quit() with acQuitSaveNone, so none of the VBA code you just created gets saved.

要获得VBA错误描述，使用E。 。消息，其中E是收到COMException对象

To get the VBA error description, use "e.Message", where "e" is the COMException object.

请确保您添加以下.NET引用到C＃项目：

Make sure you add the following .NET references to your C# project:

Microsoft.Office.Interop.Access
Microsoft.Vbe.Interop
Microsoft.VisualBasic

这篇关于我怎样才能捕捉从我的C＃代码在Microsoft Access VBA调试错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！