我有以下代码,用于在Excel中启动一系列在Access数据库中的查询。当这些查询在Access中单独运行时,它们可以正常工作并成功地生成正确的文件,但是当我将宏转换为通过按钮单击在Excel中运行时,就会遇到一些问题。请查看我的下面的代码:
Sub AccessImport()
Dim acApp As Object
Dim MyDatabase As String
Dim question As String
question = MsgBox(Prompt:="Are you sure you want to complete this action? Running this process is lengthy and could take a couple minutes to complete.", Buttons:=vbYesNo, Title:="Run SOD Matrix")
If question = vbYes Then
MyDatabase = "directory string"
OutputFile = "output string"
'open the database and apend the combination table to existing
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase (MyDatabase)
acApp.Visible = True
acApp.UserControl = True
acApp.DoCmd.SetWarnings False
acApp.DoCmd.OpenQuery "QRYDELETE_PS_ROLE_NAMES", acViewNormal, acEdit
acApp.DoCmd.OpenQuery "QRYDELETE_PS_ROLE_USER", acViewNormal, acEdit
acApp.DoCmd.OpenQuery "QRYDELETE_SOD_TBL", acViewNormal, acEdit
acApp.DoCmd.OpenQuery "QRYAPPEND_PS_ROLE_NAMES", acViewNormal, acEdit
acApp.DoCmd.OpenQuery "QRYAPPEND_PS_ROLE_USER", acViewNormal, acEdit
acApp.DoCmd.OpenQuery "QRYAPPEND_SOD_TBL", acViewNormal, acEdit
'acApp.DoCmd.OpenQuery "QRY_HIGH", acViewNormal, acEdit
acApp.DoCmd.OutputTo acOutputQuery, "QRY_HIGH", "ExcelWorkbook(*.xlsx)", OutputFile, _
False, "", , acExportQualityPrint
acApp.DoCmd.SetWarnings True
acApp.CloseCurrentDatabase
acApp.Quit
Set acApp = Nothing
Else
MsgBox ("Process has been cancelled.")
Exit Sub
End If
MsgBox ("Process has completed successfully.")
End Sub
对于最后一个查询,我想要导出并保存输出结果,但是遇到了错误提示:属性未找到
。我尝试将DoCmd
更改为TransferSpreadsheet
,将acFormalXLS
的格式类型更改以防止转换问题,但仍无法成功完成。我需要将此代码放入模块中而不是保留在表格本身吗?有什么想法/帮助吗?
Const acOutputQuery As Long = 1
和Const acExportQualityPrint as Long = 0
。 - Siddharth RoutacOutputQuery
和acExportQualityPrint
是ACCESS常量。Excel不会理解它们。请将我在上面评论中提供的代码放在您的代码顶部。 - Siddharth Rout