Operation is not allowed when the object is closed

  • Hi All,


    Am trying to connect to SQL and get data into Excel. My connection string is as follow but it keep falling over on line with error Operation is not allowed when the object is closed:


    Code
    ThisWorkbook.Worksheets("Test").Range("myRange1").CopyFromRecordset rsPubs


    The code is as follow:



    Any help would be so much apprecaited. I have already tried SET NOCOUNT ON in my SQLscript but to no success.

  • Re: Operation is not allowed when the object is closed


    First, confirm that you have the connection string setup right. I guess you mean SQL Server but I am not sure. https://www.connectionstrings.com/sql-server/


    Once you know what it should look like, do this before Open.

    Code
    Debug.Print StrConn


    After a Run, check the value in VBE's Immediate window. Click View and then Immediate Window if not visible.


    After that, you may need to play with the Open options like adOpenStatic by adding more or not using adOpenStatic.


    Here is an old example that might give you an idea or two.

  • Re: Operation is not allowed when the object is closed


    Hi Kenneth,


    Thanks for your reply. It is SQL Server. However i have tried all that. The connection is open but it thinks the recordset is closed which i clearly open with

    Code
    rsPubs.Open Query, cnPubs, adOpenStatic

    .

  • Re: Operation is not allowed when the object is closed


    See if this helps. The connection string has some issues.

  • Re: Operation is not allowed when the object is closed


    Thanks for the suggestion again. Am getting the same error.


    I went through another approach of creating stored procedure from the sql script, passing the parameters from VBA to it, creating tables in SQL and then populating the recordset from it.


    Thanks again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!