vba MS Access 2003 - Access 表单上的嵌入式 Excel 电子表格

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/2541665/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:29:03  来源:igfitidea点击:

MS Access 2003 - Embedded Excel Spreadsheet on Access form

excelvbaformsms-accessaccess-vba

提问by Justin

Let's say I have an embedded Excel Spreadsheet on a Microsoft Access form. I call the object frame

假设我在 Microsoft Access 表单上有一个嵌入的 Excel 电子表格。我称对象框架

ExcelFrame

and I add a text box on the form called

我在名为的表单上添加了一个文本框

txtA1

and I add a button on the form called

我在名为的表单上添加了一个按钮

cmdInsert

I want to type "Hello World" into the text box, click the button and have it appear in the A1 cell on that spreadsheet. What VBA do I use to accomplish this?

我想在文本框中键入“Hello World”,单击按钮并使其出现在该电子表格的 A1 单元格中。我用什么 VBA 来完成这个?

Thanks

谢谢

采纳答案by HansUp

You can automate Excel, write your value to the worksheet, then update the object frame.

您可以自动化 Excel,将您的值写入工作表,然后更新对象框架。

Private Sub cmdInsert_Click()
    Dim strPath As String
    Dim oExcel As Object
    Dim oSheet As Object

    Set oExcel = CreateObject("Excel.Application")
    oExcel.Visible = True

    strPath = Me.ExcelFrame.SourceDoc
    'Debug.Print strPath '
    oExcel.Workbooks.Open strPath
    Set oSheet = oExcel.ActiveSheet
    'Debug.Print oSheet.Name '

    oSheet.Range("A1").Value = Me.txtA1
    oExcel.ActiveWorkbook.Save
    oExcel.Quit
    Set oSheet = Nothing
    Set oExcel = Nothing
    'acOLEUpdate action requires Enabled = True '
    'and Locked = False '
    Me.ExcelFrame.Enabled = True
    Me.ExcelFrame.Locked = False
    Me.ExcelFrame.Action = acOLEUpdate
    Me.txtA1.SetFocus
    Me.ExcelFrame.Enabled = False
    Me.ExcelFrame.Locked = True
End Sub

Edit: The example was based on an external workbook file which is linkedas the source for the form's object frame.

编辑:该示例基于外部工作簿文件,该文件被链接为表单对象框架的源。

To link a worksheet, choose the "Create from File" radio button, check the "Link" check box, and browse to select the workbook. That's the way I did it with Access 2007. As I recall, it was similar with Access 2003.

要链接工作表,请选择“从文件创建”单选按钮,选中“链接”复选框,然后浏览以选择工作簿。我在 Access 2007 中就是这样做的。我记得,它与 Access 2003 类似。

回答by Patrick Honorez

It's never too late, right ?

永远不会太晚,对吧?

Provide that you have already created the Excel object (as in OP):

假设您已经创建了 Excel 对象(如在 OP 中):

Dim wb As Excel.Workbook, ws As Excel.Worksheet
Set wb = Me.ExcelFrame.Object
Set ws = wb.Worksheets(1)
ws.range("a1")= "Hello world"

Note that this code requires a reference to MS Excel in VBA.

请注意,此代码需要在 VBA 中引用 MS Excel。