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
MS Access 2003 - Embedded Excel Spreadsheet on Access form
提问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。