title: Excel VBA to Run and Export Access Queries
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>For the last query, meant to export and save the output, I run into an error telling me <code>Property is not found.</code>  I've tried changing the <code>DoCmd</code> to <code>TransferSpreadsheet</code>, the format type of <code>acFormalXLS</code> 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?</p>