Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q263498

↑ Back to the top


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

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top


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.
  1. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
  2. Add a CommandButton control to Form1.
  3. On the Project menu, click References.
  4. 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.
  5. Select one of the following:
    • Microsoft ActiveX Data Objects 2.6
    • Microsoft ActiveX Data Objects 2.5
  6. Click OK to close the References dialog box.
  7. 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.

  8. 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
				

↑ Back to the top


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

↑ Back to the top


Keywords: kbexpertisebeginner, kbautomation, kbprb, kbprogramming, KB263498

↑ Back to the top

Article Info
Article ID : 263498
Revision : 6
Created on : 3/29/2007
Published on : 3/29/2007
Exists online : False
Views : 193