Knowledge Base

Add method of QueryTables with Excel automation generates run-time error message "5"

Article ID: 263498

Article Last Modified on 3/29/2007

APPLIES TO

Microsoft Office Excel 2007
Microsoft Office Excel 2003
Microsoft Excel 2002 Standard Edition
Microsoft Excel 2000 Standard Edition
Microsoft ActiveX Data Objects 2.5
Microsoft ActiveX Data Objects 2.6

This article was previously published under Q263498

SYMPTOMS

When you automate Microsoft Excel, if you call the Add method of the QueryTables collection and supply an ADO recordset for the Connection argument, you receive the following error message at run time:

Run-time Error '5':
Invalid procedure call or argument

CAUSE

The problem is caused by the manner in which ActiveX Data Objects (ADO) uses marshalling for recordsets. ADO server-side cursors do not marshal cross-process.

RESOLUTION

To work around this problem, do one of the following:

Use a client-side cursor.
Use another technique for transferring the recordset to Excel. See the "References" section of this article for resources.

MORE INFORMATION

Steps to Reproduce Behavior

The following steps demonstrate how you might receive this error while automating Excel from a Visual Basic client. However, you should note that this problem can occur with any client that automates Microsoft Excel and is not specific to Visual Basic Automation clients.

Create a new Standard EXE project in Visual Basic. Form1 is created by default.
Add a CommandButton control to Form1.
On the Project menu, click References.
Click the Object Library for your version of Excel. For example, click one of the following options:
- For Microsoft Office Excel 2007, select Microsoft Excel 12.0 Object Library.
- For Microsoft Office Excel 2003, select Microsoft Excel 11.0 Object Library.
- For Microsoft Excel 2002, select Microsoft Excel 10.0 Object Library.
- For Microsoft Excel 2000, select Microsoft Excel 9.0 Object Library.
Select one of the following:
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.5
Click OK to close the References dialog box.

Add the following code to the Click event of the CommandButton:

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook

    Dim xlSheet As Excel.Worksheet
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim oQueryTable As Excel.QueryTable
    
    'Start a new workbook in Excel
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.UserControl = True
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    
    'Connect to local SQL Server. You will need to replace <username> and <strong password>
    'with the User ID and password of an account who has appropriate permissions.
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB.1;Data Source=YourServer;" & _
            "Password=<strong password>;User ID=<username>;Initial Catalog=Northwind"
    
    'Generate the recordset
    Set rs = New ADODB.Recordset
    rs.Open "Select * from Products", cn
    
    'Create the query table on the worksheet
    Set oQueryTable = xlSheet.QueryTables.Add(rs, xlSheet.Cells(1, 1))
    oQueryTable.Refresh
    
    'Close the recordset and the connection
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

NOTE: This code generates a recordset from the sample database Northwind on SQL Server. In the connection string, change "YourServer" to the name of your SQL Server.

Press the F5 key to run the application, and then click the CommandButton.

Results: The line of code that attempts to add the QueryTable generates the run-time error message "5."

To correct the error, modify the code so that the recordset uses a client-side cursor. In the sample code, change the following

'Generate the recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from Products", cn

to:

'Generate the recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * from Products", cn

REFERENCES

For additional information regarding different techniques for transferring data to Excel from an external source, click the article numbers below to view the articles in the Microsoft Knowledge Base:

247412 INFO: Methods for Transferring Data to Excel from Visual Basic

246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation

Additional query words: runtime automating createobject cursorlocation aduseclient kbnoOfficeAlertID XL2007

Keywords: kbexpertisebeginner kbautomation kbprb kbprogramming KB263498

Microsoft KB Archive/263498

Contents