title: VBA - Generate Excel File from Access (QueryTable)
body: <p>I have a project that basically the goal is to generate Excel (Report) starting the Click of a button in Access using VBA.</p>
  
  <p>The contents of this report is the result of a Stored Procedure SQL Server Database.</p>
  
  <p>the line of error:</p>
  
  <pre><code>With MeuExcel.Worksheets(4)
      .QueryTables.Add connection:=rs, Destination:=.Range("A2")
  End With
  </code></pre>
  
  <p>I get is:</p>
  
  <pre><code>invalid procedure call or argument (erro '5')
  </code></pre>
  
  <p>Complete Code (Edited using Remou User tips):</p>
  
  <pre><code>Sub GeraPlanilhaDT()
  
  Dim MeuExcel As New Excel.Application
  Dim wb As New Excel.Workbook
  
  Set MeuExcel = CreateObject("Excel.Application")
  MeuExcel.Workbooks.Add
  
  MeuExcel.Visible = True
  
  Dim strNomeServidor, strBaseDados, strProvider, strConeccao, strStoredProcedure As String
  
  strNomeServidor = "m98\DES;"
  strBaseDados = "SGLD_POC;"
  strProvider = "SQLOLEDB.1;"
  strStoredProcedure = "SP_ParametrosLeads_DT"
  
  strConeccao = "Provider=" &amp; strProvider &amp; "Integrated Security=SSPI;Persist Security Info=True;Data Source=" &amp; strNomeServidor &amp; "Initial Catalog=" &amp; strBaseDados
  
  Dim cnt As New ADODB.connection
  Dim cmd As New ADODB.command
  Dim rs As New ADODB.recordset
  Dim prm As New ADODB.parameter
  
  cnt.Open strConeccao
  
  cmd.ActiveConnection = cnt
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = strStoredProcedure
  cmd.CommandTimeout = 0
  
  Set prm = cmd.CreateParameter("DT", adInteger, adParamInput)
  cmd.Parameters.Append prm 
  cmd.Parameters("DT").Value = InputBox("Digite o Código DT", "Código do Distribuidor")
  
  Set rs = cmd.Execute()
  
  Dim nomeWorksheetPrincipal As String
  nomeWorksheetPrincipal = "Principal"
  
  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = nomeWorksheetPrincipal
  
  
  
  With MeuExcel.Worksheets(4)
      .QueryTables.Add connection:=rs, Destination:=.Range("A2")
  End With
  
  
  cnt.Close
  Set rs = Nothing
  Set cmd = Nothing
  Set strNomeServidor = Nothing
  Set strBaseDados = Nothing
  Set strProvider = Nothing
  
  If (ActiveSheet.UsedRange.Rows.Count &gt; 1) Then
      FormataDadosTabela
  Else
      MsgBox ("Não foi encontrado nenhum Distribuidor com esse DT")
  End If
  
  
  End Sub
  </code></pre>
  
  <p>The strange thing is that the code works when run in Excel but does not work in Access</p>