用于运行和导出访问查询的 Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19665651/
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
Excel VBA to Run and Export Access Queries
提问by Nate
I have the following code that kicks off a series of queries in an Access database from Excel. When these queries are run by themselves in Access, they work fine and succeed in generating the right file, but when I convert the macros to run in Excel using a button click, I run into some problems. See my code below:
我有以下代码从 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
For the last query, meant to export and save the output, I run into an error telling me Property is not found.I've tried changing the DoCmdto TransferSpreadsheet, the format type of acFormalXLSto prevent conversion issues, but I still cannot get it to complete successfully. Do I need to put this code in a module instead of keeping it on the sheet itself? Thoughts/help?
对于最后一个旨在导出和保存输出的查询，我遇到了一个错误，告诉我Property is not found.我已尝试更改DoCmdtoTransferSpreadsheet的格式类型acFormalXLS以防止转换问题，但我仍然无法成功完成它。我是否需要将此代码放在模块中而不是将其保留在工作表本身上？想法/帮助？
采纳答案by HansUp
Siddharth identified the issue with this statement:
悉达多通过以下声明确定了问题：
acApp.DoCmd.OutputTo acOutputQuery, "QRY_HIGH", "ExcelWorkbook(*.xlsx)", OutputFile, _
False, "", , acExportQualityPrint
Without a reference to the Access object library, Excel will know nothing about the Access constants acOutputQueryand acExportQualityPrint.
如果没有对 Access 对象库的引用，Excel 将一无所知 Access 常量acOutputQuery和acExportQualityPrint.
You would be wise to add Option Explictto the module's Declarations section and then run Debug->Compile from the VB Editor's main menu. When you do that, I suspect you will discover similar problems with the lines such as this ...
您最好添加Option Explict到模块的声明部分，然后从 VB 编辑器的主菜单运行调试->编译。当你这样做时，我怀疑你会发现类似这样的行有类似的问题......
acApp.DoCmd.OpenQuery "QRYDELETE_PS_ROLE_NAMES", acViewNormal, acEdit
Excel will also know nothing about the Access constants acViewNormaland acEdit. However, if your intent was to execute "action" queries (INSERT, UPDATE, DELETE, etc), it's good that those constants are unrecognized. Otherwise you would be opening those queries in Design View instead of executing them.
Excel 对 Access 常量acViewNormal和acEdit. 但是，如果你的目的是为了执行“行动”查询（INSERT，UPDATE，DELETE等），这是很好的，这些常数是无法识别的。否则，您将在设计视图中打开这些查询而不是执行它们。
Consider a different approach ...
考虑一种不同的方法......
Const dbFailOnError As Long = 128
'acApp.DoCmd.SetWarnings False ' leave SetWarnings on!
acApp.CurrentDb.Execute "QRYDELETE_PS_ROLE_NAMES", dbFailOnError
acApp.CurrentDb.Execute "QRYDELETE_PS_ROLE_USER", dbFailOnError
