从Excel VBA获取MS Access表单中的值

What is the best way to check is access form is open and get the value of textbox using Excel VBA.

检查的最佳方法是打开访问表单并使用Excel VBA获取文本框的值。

I mean is there a way to check if MS Access application is running and if it is then check certain form is open then get the value from this form's textbox field.

我的意思是有一种方法来检查MS Access应用程序是否正在运行,如果是,然后检查某个表单是否打开,然后从此表单的文本框字段中获取值。

Something like

  If MSAccess.([Application name]).Forms("FormName").isOpen then
     MyVar = MSAccess.([Application name]).Forms("FormName")![PO Number]
  end if

2 个解决方案

#1

Here is some sample code.

这是一些示例代码。

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function ShowWindow Lib "user32" _
    (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Public Const SW_SHOW = 5
Public Const GW_HWNDNEXT = 2

Sub FindAccess()
Dim WinHandle As Long
Dim objAc As Object

'Form title'
FindWindow vbNullString, "Images"

'use it'
ShowWindow WinHandle, SW_SHOW

'to get the application'
Set objAc = GetObject(, "Access.Application")

'and print a control's value'
Debug.Print objAc.Forms("frmImages").Controls("Description")

Set objAc = Nothing
End Sub

#2

@Remou's Debug.Print will error out if the form isn't open.

如果表单未打开,@ Remou的Debug.Print将会出错。

Most Access developer's import an IsLoaded() function into their database and use it. The code in my version of it (which may or may not be edited from the original MS version) is this:

大多数Access开发人员将IsLoaded()函数导入其数据库并使用它。我的版本中的代码(可能是也可能不是从原始MS版本编辑)是这样的:

  Function IsLoaded(ByVal strFormName As String) As Boolean
   ' Returns True if the specified form is open in Form view or Datasheet view.
    Const conObjStateClosed = 0
    Const conDesignView = 0

    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
       If Forms(strFormName).CurrentView <> conDesignView Then
          IsLoaded = True
       End If
    End If
  End Function

To use that from Excel, you could rewrite it thus:

要从Excel中使用它,您可以重写它:

  Function IsLoaded(ByVal strFormName As String, objAccess As Object) As Boolean
   ' Returns True if the specified form is open in Form view or Datasheet view.
    Const conObjStateClosed = 0
    Const conDesignView = 0
    Const acSysCmdGetObjectState = 10
    Const acForm = 2

    If objAccess.SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
       If objAccess.Forms(strFormName).CurrentView <> conDesignView Then
          IsLoaded = True
       End If
    End If
  End Function

(I didn't test that from Excel, but you get the idea)

(我没有从Excel测试,但你明白了)

#1