Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite

I have a project that basically the goal is to generate Excel (Report) starting the Click of a button in Access using VBA.

The contents of this report is the result of a Stored Procedure SQL Server Database.

the line of error:

With MeuExcel.Worksheets(4)
    .QueryTables.Add connection:=rs, Destination:=.Range("A2")
End With

I get is:

invalid procedure call or argument (erro '5')

Complete Code (Edited using Remou User tips):

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=" & strProvider & "Integrated Security=SSPI;Persist Security Info=True;Data Source=" & strNomeServidor & "Initial Catalog=" & 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 > 1) Then
    FormataDadosTabela
Else
    MsgBox ("Não foi encontrado nenhum Distribuidor com esse DT")
End If


End Sub

The strange thing is that the code works when run in Excel but does not work in Access

share|improve this question
Okay, like you, I now have code that works in Excel but does not work in Access. There seems to be a problem adding query tables. I cannot see why as yet. I wonder if an alternative solution would suit? For example, would simply writing the records to a worksheet be suitable? – Remou Jan 4 '12 at 13:08
FYI: I have automated Excel Querytables from Access for years without problem, albeit with Access data as the source. However, I have not tried it in Office 2010. – Rachel Hettinger Jan 4 '12 at 16:52
@Rachel Perhaps you could post some working Access code? – Remou Jan 4 '12 at 18:23
@Remou I updated my answer with sample code. Hope it helps! – Rachel Hettinger Jan 4 '12 at 20:47

3 Answers

up vote 5 down vote

In Access, you need to prefix the Excel application objects with the Excel application instance, for example:

With MeuExcel.Worksheets(4).QueryTables.Add( _
    connection:=recordset, _
    Destination:=Range("A2"))
End With

Furthermore, unless you have a reference to the Excel library, ypu will need to provide the value for built-in Excel constants.

It is a very bad idea to use the name of objects for variables. Do not say:

Dim recordset As recordset
Set recordset = New recordset

Say, for example:

Dim rs As recordset

Or much better:

Dim rs As New ADODB.Recordset

If you have a suitable reference. You can then skip CreateObject.

EDIT

The provider must be the Access OLEDB 10 provider, as used to bind recordsets. This works for me to create a data table via Access using SQL Server: 

strConnect = "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;" _
& "Data Source=XYZ\SQLEXPRESS;Integrated Security=SSPI;" _
& "Initial Catalog=TestDB;Data Provider=SQLOLEDB.1"
share|improve this answer
Thanks for the tips, but did not solve my problem – Predoff Jan 3 '12 at 18:14
up vote 4 down vote

FWIW, two things stand out:

  1. As @Remou pointed out, Excel references need to be qualified. Currently, Range("A2") is unqualified. When running the code in Excel, the ActiveSheet is assumed. However, when running from another application, that application will look for a method or property in its own library called Range, which will give you that error in Microsoft Access.

  2. There isn't any code in the With block, so you can remove the With and End With keywords; when you do this also remove the outer (), like this:

wb.Worksheets(4).QueryTables.Add Connection:=rs, Destination:=wb.Worksheets(4).Range("A2")

Alternatively, shift the With block to the Worksheet level:

With wb.Worksheets(4)
    .QueryTables.Add Connection:=rs, Destination:=.Range("A2")
End With

Update—Access to Excel Sample

This sample code automates Excel from Access, creating a new workbook and adding a Querytable to the first sheet. The source data is an Access table. This runs in Office 2007.

Public Sub ExportToExcel()
  Dim appXL As Excel.Application
  Dim wbk As Excel.Workbook
  Dim wst As Excel.Worksheet
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset

  Set appXL = CreateObject("Excel.Application")
  appXL.Visible = True
  Set wbk = appXL.Workbooks.Add
  Set wst = wbk.Worksheets(1)

  Set cn = CurrentProject.AccessConnection
  Set rs = New ADODB.Recordset
  With rs
    Set .ActiveConnection = cn
    .Source = "SELECT * FROM tblTemp"
    .Open
  End With

  With wst
    .QueryTables.Add Connection:=rs, Destination:=.Range("A1")
    .QueryTables(1).Refresh
  End With

End Sub
share|improve this answer
invalid procedure call or argument – Predoff Jan 4 '12 at 12:20
Thanks, but did not solve my problem :/ – Predoff Jan 4 '12 at 12:20
@Predoff I modified my example to substitute the Workbook object for the Excel Application object. Not sure if this will resolve your problem, but it is more correct. – Rachel Hettinger Jan 4 '12 at 16:49
@Rachel I have also tried to get this to work, and I am fairly sure there is a problem with running in Access. A worksheet that accepts data still will not allow QueryTables.Add, it fails with error 5, as stated by Predoff. The example I used worked perfectly in Excel and was taken from MSDN. – Remou Jan 4 '12 at 17:02
That works perfectly with 2010, so should be the solution for @Predoff. I must check where the difference lies! – Remou Jan 4 '12 at 20:56
show 2 more comments
up vote 0 down vote

You don't say what Office version, but in Excel 2007/10 a QueryTable is a property of a Listobject so your code would be like:

With MeuExcel.Worksheets.ListObjects.Add(Connection:=rs, Destination:=Range("A2")).QueryTable
share|improve this answer
2  
After re-reading the question, I realize that this isn't the issue - as you have a runtime error. I think @RachelHettinger (as usual) has hit the nail on the head. – Doug Glancy Jan 3 '12 at 23:48
Named Argument Not Found – Predoff Jan 4 '12 at 12:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.