从MS Access启动Excel和Reference UserForm对象 [英] Launch Excel and Reference UserForm object from MS Access

问题描述

我这里有一个MS Access数据库应用程序，我想使用VBA启动Excel文件，并使用默认值填充此文件内UserForm对象上的字段.我正在努力寻找可用于从Excel应用程序外部引用UserForm对象的语法.

I have an MS Access database application here and I'd like to launch an Excel file using VBA and populate fields on a UserForm object within this file with default values. I'm struggling to find the syntax I can use to reference the UserForm object from outside the Excel application.

希望这是有道理的.到目前为止，这是我的简单代码.

Hopefully this makes sense. Here's my simple code so far.

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
Set xlWB as xlApp.Workbooks.Open("[My file path goes here]")

xlApp.Visible = True

这时我想要做的是访问名为UserForm1的文件中的UserForm对象，该文件包含一个名为TextBox1的文本框并设置其值.

What I'm attempting to do at this point is access a UserForm object within this file named UserForm1 that contains a textbox called TextBox1 and set it's value.

我可以在Excel中使用VBA到UserForm1.TextBox1 = "Test"来做到这一点，并且可以正常工作.如果可以，请尝试从MS Access外部进行此操作.我在这里遗漏了什么明显的东西吗?

I can do this using VBA within Excel to UserForm1.TextBox1 = "Test" and this works. Trying to do this externally from MS Access if I can. Am I missing something obvious here?

推荐答案

在这里，我将描述一种有效的方法，我不确定是否还有其他方法可以实现此目的，但是该解决方案已经过测试，并提供了一种通用的机制从另一个VBA应用程序中调用一个VBA应用程序中的表单.

I will describe here a method that works, I am not sure if there are other methods to achieve this, but this solution is tested and provides a general mechanism to invoke a form in one VBA application from another VBA application.

1)在excel工作簿中，添加一个宏，将其命名为ShowFormWithParams(param1，param2等).此宏以非模式模式(非阻塞)显示表单，以便您可以在表单显示时继续执行代码.

1) In the excel workbook, add a macro, call it for example ShowFormWithParams(param1, param2 etc). This macro shows the form in non-modal mode (non-blocking), so that you can continue executing your code while the form shows up.

    ' placed in code Module1
    Sub showFormWithValues(lbl1 As String, txt1 As String, chk1 As Boolean)
        With UserForm1
            .Show False
            .Label1.Caption = lbl1
            .TextBox1.Text = txt1
            .CheckBox1.Value = chk1
        End With
    End Sub

2)在另一个应用程序中，例如Word，Access，甚至另一个Excel Workbook，您可以通过以下方式调用此宏并为其提供适当的参数:

2) In the other application, say Word, Access, or even another Excel Workbook, you can invoke this macro and give it the appropriate parameters in the following way:

Sub mySub()
    Dim xlApp As Excel.Application, xlWB As Excel.Workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\Test.xlsm") ' specify file
    xlApp.Visible = True
    xlApp.Run "Module1.showFormWithValues", "ABCD", "foooo", True
End Sub

现在该表格显示出来，并且已经显示了所需的值.

Now the form shows up and displays already the desired values.

我应该说，必须有某种方法来获取用户窗体的句柄并从VBA代码中显式控制它，但这似乎是一项艰巨的任务，我还没有尝试过.希望这会有所帮助.

I should say that there must be some way to get a handle to the userform and control it explicitly from VBA code, but that seems a difficult task and I did not try it yet. Hope this helps.

这篇关于从MS Access启动Excel和Reference UserForm对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！