我一直在对此进行大量研究，但我没有找到任何有关如何工作的线索。
我在 Excel 中编写了要在 MS Access 中运行的代码。我已经粘贴了我希望在 Access 中运行的代码。
我找到的所有示例或信息均来自 2003 Access。我正在使用 2016 Access。
Excel 代码
Public Function getworkbook()
' Get workbook...
Dim ws As Worksheet
Dim Filter As String
Dim targetWorkbook As Workbook, wb As Workbook
Dim Ret As Variant
Application.DisplayAlerts = False
Sheets("DATA").Delete
' Sheets("DATA").Cells.Clear
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
Filter = "Text files (*.xlsx;*.xlsb),*.xlsx;*.xlsb"
Caption = "Please Select an input file "
Ret = Application.GetOpenFilename(Filter, , Caption)
If Ret = False Then Exit Function
Set wb = Workbooks.Open(Ret)
wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
' ActiveSheet.Paste = "DATA"
ActiveSheet.Name = "DATA"
ThisWorkbook.RefreshAll
' Application.Quit
Application.DisplayAlerts = True
End Function
我发现并尝试在 Access 中使用的代码。
Public Function runExcelMacro(wkbookPath)
Dim XL As Object
Set XL = CreateObject("Excel.Application")
With XL
.Visible = False
.displayalerts = False
.Workbooks.Open wkbookPath
'Write your Excel formatting, the line below is an example
.Range("C2").value = "=1+2"
.ActiveWorkbook.Close (True)
.Quit
End With
Set XL = Nothing
End Function
首先，清理术语：
VBA 是一种独立的语言，与任何MS Office 应用程序无关。在 Tools\References 下，您将看到Visual Basic for Applications通常是第一个选中的对象。在 Excel、Access、Word、Outlook 等中运行 VBA 的不同之处在于对其对象库的默认访问。具体来说：
在应用程序中运行外部对象库时，例如 MS Access 访问 Excel 对象，您必须通过使用早期或后期绑定的引用来定义和初始化外部对象：
' EARLY BINDING, REQUIRES EXCEL OFFICE LIBRARY UNDER REFERENCES
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(...)
Set ws = wb.Worksheets(1)
' LATE BINDING, DOES NOT REQUIRE EXCEL OFFICE LIBRARY UNDER REFERENCES
Dim xlApp As Object, wb As Object, ws As Object
Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(...)
Set ws = wb.Worksheets(1)
话虽如此，只需保持原始代码几乎完整，但更改定义和初始化。值得注意的是，所有Application调用现在都指向Excel.Application对象，而不是与 Access 的应用程序混淆。另外，避免.Select/ .Activate/ Selection/ ActiveCell/ ActiveSheet/ ActiveWorkbook.
Public Function getworkbook()
' Get workbook...
Dim xlApp As Object, targetWorkbook As Object, wb As Object, ws As Object
Dim Filter As String, Caption As String
Dim Ret As Variant
Set xlApp = CreateObject("Excel.Application")
Set targetWorkbook = xlApp.Workbooks.Open("C:\Path\To\Workbook.xlsx")
xlApp.DisplayAlerts = False
targetWorkbook.Sheets("DATA").Delete
' get the customer workbook
Filter = "Text files (*.xlsx;*.xlsb),*.xlsx;*.xlsb"
Caption = "Please Select an input file "
Ret = xlApp.GetOpenFilename(Filter, , Caption)
If Ret = False Then Exit Function
Set wb = xlApp.Workbooks.Open(Ret)
wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
Set ws = targetWorkbook.Worksheets(targetWorkbook.Sheets.Count)
ws.Name = "DATA"
targetWorkbook.RefreshAll
xlApp.DisplayAlerts = True
xlApp.Visible = True ' LAUNCH EXCEL APP TO SCREEN
' xlApp.Quit
' RELEASE RESOURCEES
Set ws = Nothing: Set wb = Nothing: Set targetWorkbook = Nothing: Set xlApp = Nothing
End Function
顺便说一下，上面可以在任何MS Office 应用程序中运行，因为没有使用父应用程序的对象（这里是 MS Access）！
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句