8

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

Improve this question
4
  • 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? Commented Jan 4, 2012 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. Commented Jan 4, 2012 at 16:52
  • @Rachel Perhaps you could post some working Access code? Commented Jan 4, 2012 at 18:23
  • @Remou I updated my answer with sample code. Hope it helps! Commented Jan 4, 2012 at 20:47

3 Answers 3

Reset to default
5

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"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

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
Improve this answer

6 Comments

invalid procedure call or argument
@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 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.
That works perfectly with 2010, so should be the solution for @Predoff. I must check where the difference lies!
It appears to be in the connection string, though why I do not yet see, because the connection string I used produced a perfectly valid recordset.
|
0

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
Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.