vba让用户从MS Access中选择工作表上的范围

Question

下面的子 RangeSelectionPrompt 让用户选择单元格范围。仅当此代码位于 Excel 文件中时，以下代码才有效。

我想知道如何在 MSAccess 中执行此代码。我想捕获用户选择的范围并进行数据操作。有可能吗，还是我抽烟太多了。

通过 Access，我知道如何使用 Excel、检测打开了多少个 WBK、在工作簿中导航，....表单 Excel 我知道如何创建加载项、连接到数据库并在用户选择后传输数据，但这不是我想要的。

Sub RangeSelectionPrompt()
    Dim rng As Range
    Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

    MsgBox "The cells selected were " & rng.Address
End Sub

Answer 1

问题已解决

Option Compare Database

Option Explicit
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim rng As Range


Sub GetUserSelection()
    On Error GoTo ErrExcelinstance

    Set xlApp = GetObject(, "Excel.Application")
    Set xlWB = xlApp.ActiveWorkbook              ' the bug was Set xlWB = xlApp.ActiveWorkbook.ActiveSheet.Select

    RangeSelectionPrompt

    Exit Sub

ErrExcelinstance:


    Select Case Err.Number

    Case 429

        Err.Clear
        MsgBox "No Workbook Open"
        Set xlApp = Nothing
        Set xlWB = Nothing


    Case Else

        Set xlApp = Nothing
        Set xlWB = Nothing
        MsgBox Err.Number & "  " & Err.Description
        Err.Clear

    End Select

End Sub


Sub RangeSelectionPrompt()
    On Error GoTo RangeSelectionPrompterr:

    Set rng = xlApp.InputBox("Select a range", "Obtain Range Object", Type:=8)

    MsgBox "The cells selected were " & rng.Address

    Exit Sub

RangeSelectionPrompterr:


    Select Case Err.Number



    Case 424

        Err.Clear
        MsgBox "User did not perform selection"
        Set xlApp = Nothing
        Set xlWB = Nothing


    Case Else



        Set rng = Nothing
        MsgBox Err.Number & "  " & Err.Description
        Err.Clear

    End Select


End Sub