Excel VBA运行和导出访问查询

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 DoCmd to TransferSpreadsheet, the format type of acFormalXLS to 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。我已经尝试将DoCmd更改为TransferSpreadsheet,acFormalXLS的格式类型以防止转换问题,但我仍然无法成功完成它。我是否需要将此代码放在模块中而不是将其保留在工作表本身上?想法/帮助?

1 个解决方案

#1

Siddharth identified the issue with this statement:

Siddharth通过以下声明确定了问题:

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 acOutputQuery and acExportQualityPrint.

如果没有对Access对象库的引用,Excel将无法了解Access常量acOutputQuery和acExportQualityPrint。

You would be wise to add Option Explict to 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添加到模块的Declarations部分,然后从VB Editor的主菜单中运行Debug-> Compile。当你这样做时,我怀疑你会发现类似这样的问题...

acApp.DoCmd.OpenQuery "QRYDELETE_PS_ROLE_NAMES", acViewNormal, acEdit

Excel will also know nothing about the Access constants acViewNormal and 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

#1