从MS Access VBA运行Excel VBA
问题描述:
花了一些时间研究,我无法弄清楚什么是错的。从MS Access VBA运行Excel VBA
这两个文件中的安全设置都是正确的。被调用的子文件在公共模块中是公开的&。
该错误提示“无法运行宏等等等等等等......”
代码:
Private Sub this()
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Visible = True
xl.Workbooks.Open "pathToFile" & "\" & "Dig IT.xlsm", True, False
xl.Run "ThisWorkbook.Module3"
Set xl = Nothing
End Sub
答
相反的"ThisWorkbook.Module3"
,您需要提供的工作簿名称,模块名称和程序名称(假设“Module3”是代码模块的名称,而不是您尝试运行的子/函数)。
我认为,应该是这样的(未经测试)格式:
Private Sub this()
Dim xl As Object, wb as Object
Const MODULE_NAME As String = "Module3" '### MODIFY AS NEEDED!
Const PROC_NAME As String = "macro_name" '### MODIFY AS NEEDED!
Dim xlMacro As String
Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wb = xl.Workbooks.Open("pathToFile" & "\" & "Dig IT.xlsm", True, False)
' builds the string to send to Excel.Run method:
xlMacro = wb.Name & "!" & MODULE_NAME & "." & PROC_NAME
' call the macro in Excel
xl.Run xlMacro
Set xl = Nothing
End Sub
上帝啊IM哑。更改为xl.Run“名称”,它的工作完美。作为回答发布并接收反馈 –