Excel 2007 VBA "operation is not allowed when the object is closed"

makeitwork09 · Mar 2, 2012

I am using Excel 2007.

The data being extracted from the following code is in Microsoft SQL Server 2005.

At the line below I am getting the error "operation is not allowed when the object is closed". I searched and searched the web, but I cannot figure out what is wrong.

I did ?sSQL in the Immediate Window, then pasted the results in SQL Server to verify nothing was wrong. Indeed, in SQL Server, the code ran without error.

Any ideas?

Code:

If Not rsData.EOF Then

Code:

Sub DataDumpQuery()

    Dim cmdl As ADODB.Command
    Dim objConn As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim objField As ADODB.Field
    Dim lOffset As Long
    Dim sConnect As String
    Dim sSQL As String
    Dim ServerName As String
    
    Application.ScreenUpdating = False
    
    ServerName = ThisWorkbook.Sheets("Notes").Range("D3").Value

    sSQL = "SET NOCOUNT ON;"
    sSQL = sSQL & vbCrLf
    
    sSQL = sSQL & "if OBJECT_ID('tempdb..#impairments' ,'u') is not null drop table #impairments"
    sSQL = sSQL & vbCrLf
    
    sSQL = sSQL & "select"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "i.accounting_date,i.participant,i.loan,i.tran_sub_type,i.cash_date,i.loan_kind"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "into #impairments"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "from (select ROW_NUMBER() OVER (partition by d.loan,d.participant ORDER BY"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   d.accounting_date asc) as [rownum],d.accounting_date,d.participant,d.loan,d.tran_sub_type,d.cash_date,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   l.loan_kind"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " from LMS_V11_YE_2011..ddethist d"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " join LMS_V11_YE_2011..loanhist l on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "l.loan = d.loan and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "l.accounting_date = d.accounting_date"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " join LMS_V11_YE_2011..partmast u on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "u.participant = d.participant"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " where d.tran_sub_type = 'bkvaladj'"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   and l.loan_kind not like 'sf%'"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   and u.investor_type = 'internal'"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   and d.participant != 'nylim'"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " group by d.accounting_date,d.participant,d.loan,d.tran_sub_type,d.cash_date,l.loan_kind) as i"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "where i.rownum = 1"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "order by i.loan"
    sSQL = sSQL & vbCrLf
    
    
    sSQL = sSQL & "if OBJECT_ID('tempdb..#allocation' ,'u') is not null drop table #allocation"
    sSQL = sSQL & vbCrLf
    
    sSQL = sSQL & "select h.loan,f.loan_xref,h.participant,h.accounting_date,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "case when g.port_loan = 'Y' then "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "g.port_name"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "case when charindex(rtrim(t.loan), t.loan_name) != 0 then rtrim(substring(t.loan_name,1,charindex(rtrim(t.loan), t.loan_name)-1)) else t.loan_name end"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "    end as [loan_name],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "sum(case"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when q.total_value is null and o.appr_final_val is null then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_int_due_bal_p/r.number_of_properties"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when isnull(q.total_value,0) = 0 then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_int_due_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "(isnull(o.appr_final_val,0) / q.total_value) * h.inv_int_due_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "end) as [int_due_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "sum(case"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when q.total_value is null and o.appr_final_val is null then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_int_coll_p/r.number_of_properties"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when isnull(q.total_value,0) = 0 then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_int_coll_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "(isnull(o.appr_final_val,0) / q.total_value) * h.inv_int_coll_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "end) as [int_coll_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "sum(case when q.total_value is null and o.appr_final_val is null then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_prepd_int_bal_p/r.number_of_properties"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when isnull(q.total_value,0) = 0 then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_prepd_int_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "(isnull(o.appr_final_val,0) / q.total_value) * h.inv_prepd_int_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "end) as [prepd_int_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "sum(case when q.total_value is null and o.appr_final_val is null then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_accrd_int_bal_p/r.number_of_properties"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when isnull(q.total_value,0) = 0 then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_accrd_int_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "(isnull(o.appr_final_val,0) / q.total_value) * h.inv_accrd_int_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "end) as [accrd_int_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "o.system_type,o.prop_type"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "into #allocation"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "from (select p.loan,p.participant,p.accounting_date,isnull(sum(p.int_due_bal_p),0) as [inv_int_due_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      isnull(sum(p.prepd_int_bal_p),0) as [inv_prepd_int_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      isnull(sum(p.int_coll_p),0) as [inv_int_coll_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      isnull(sum(p.accrd_int_bal_p),0) as [inv_accrd_int_bal_p]"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      from LMS_V11_YE_2011..porthist p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      group by p.loan,p.participant,p.accounting_date) as h"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..nyl_property_appr as o on h.loan = o.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..nyl_loan_appr_value as q on h.loan = q.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join (select l.loan,count(code) as number_of_properties "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " from LMS_V11_YE_2011..loancoll l WITH (NOLOCK)"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " where l.release_date is null "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " group by l.loan) as r on "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "r.loan = o.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..loanhist as t on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "t.loan = h.loan and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "    t.accounting_date = h.accounting_date"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..partmast as a on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "a.participant = h.participant"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..nyl_loanxref as f on "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "f.loan = h.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..nyl_extgeneralh as g on "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "g.loan = h.loan and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "    g.accounting_date = h.accounting_date"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "where t.loan_kind not like 'sf%' and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  a.investor_type = 'internal' and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      h.participant not like 'nylim%'"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "group by h.loan,f.loan_xref,h.participant,h.accounting_date,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "case when g.port_loan = 'Y' then "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "g.port_name"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "case when charindex(rtrim(t.loan), t.loan_name) != 0 then rtrim(substring(t.loan_name,1,charindex(rtrim(t.loan), t.loan_name)-1)) else t.loan_name end"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "    end,o.system_type,o.prop_type"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "order by h.loan,h.accounting_date"
    sSQL = sSQL & vbCrLf
    
    
    sSQL = sSQL & "select a.loan,a.loan_xref,l.loan_list,a.loan_name,a.participant,a.accounting_date,a.int_due_bal_p,a.int_coll_p,a.prepd_int_bal_p,a.accrd_int_bal_p,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "       i.cash_date,p.imp_acctg_date,p.pint_coll_p,p.pint_due_bal_p,p.pprepd_int_bal_p,p.paccrd_int_bal_p,a.system_type,a.prop_type"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "from #allocation a"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "join #impairments i on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "i.loan = a.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join (select t.loan,t.participant,t.accounting_date  as [imp_acctg_date],m.accounting_date,m.cash_date,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "                  t.int_due_bal_p as [pint_due_bal_p],t.int_coll_p as [pint_coll_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  t.prepd_int_bal_p as [pprepd_int_bal_p],t.accrd_int_bal_p as [paccrd_int_bal_p],t.system_type,t.prop_type"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " from #allocation t"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " join #impairments m on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  m.loan = t.loan and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "                      m.participant = t.participant"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "     where"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " case when year(m.cash_date) = (select year(pri_year_end) from accounts) and m.accounting_date = t.accounting_date then 1"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "                      when year(m.cash_date) < (select year(pri_year_end) from accounts) and t.accounting_date = (select pri2_year_end from accounts) then 1"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "                      else 0"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "                 end = 1) as p on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "p.loan = a.loan and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "p.participant = a.participant and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "p.cash_date = i.cash_date and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "p.prop_type = a.prop_type"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "-- the following lists the loans belonging to an xref number"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join (SELECT c1.loan_xref,c1.accounting_date,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  loan_list = substring((SELECT ( '| ' + rtrim(c2.loan) )"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   FROM LMS_V11_YE_2011..nyl_loanxrefh c2"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   WHERE c1.loan_xref = c2.loan_xref and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "                             c1.accounting_date = c2.accounting_date"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   GROUP BY "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  c2.loan_xref,c2.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "   FOR XML PATH( '' )), 3, 1000 )"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  FROM LMS_V11_YE_2011..nyl_loanxrefh c1"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  GROUP BY c1.loan_xref,c1.accounting_date) as l on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "a.loan_xref = l.loan_xref and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "a.accounting_date = l.accounting_date"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "where a.accounting_date = (select pri_year_end from LMS_V11_YE_2011..accounts)"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  and a.int_due_bal_p + a.int_coll_p + a.prepd_int_bal_p + a.accrd_int_bal_p != 0"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "group by "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "a.loan,a.loan_xref,l.loan_list,a.loan_name,a.participant,a.accounting_date,a.int_due_bal_p,a.int_coll_p,a.prepd_int_bal_p,a.accrd_int_bal_p,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "       i.cash_date,p.imp_acctg_date,p.pint_coll_p,p.pint_due_bal_p,p.pprepd_int_bal_p,p.paccrd_int_bal_p,a.system_type,a.prop_type"
    sSQL = sSQL & vbCrLf
    
    
    sSQL = sSQL & "drop table #impairments"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "drop table #allocation"

   
    'create connection string
    sConnect = "Provider=SQLOLEDB;" & _
               "Data Source=" & ServerName & ";" & _
               "Initial Catalog=MS_IL;" & _
               "Integrated Security=SSPI"
        
    'create the connection and recrodset objects
    Set objConn = New ADODB.Connection
    
    'Set CONNECTION timeout property
    objConn.CommandTimeout = 0

    'Create a new command object to process the stored proc
    Set cmdl = New ADODB.Command
    
        With cmdl
            .ActiveConnection = sConnect
            'set COMMAND timeout property - query can time out on either the connection OR the command
            .CommandTimeout = 0
            .CommandText = sSQL
            .CommandType = adCmdText
'            .Refresh False
            Set rsData = .Execute()
    End With

    'Delete Query Results sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False

    ThisWorkbook.Sheets("Data Dump").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add Query Results sheet
    ThisWorkbook.Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Data Dump"
   
    'make sure data is returned
    If Not rsData.EOF Then
        ' Add headers to the worksheet.
        With ThisWorkbook.Sheets("Data Dump").Range("A1")
            For Each objField In rsData.Fields
                .Offset(0, lOffset).Value = objField.Name
                lOffset = lOffset + 1
            Next objField
            .Resize(1, rsData.Fields.Count).Font.Bold = True
        End With

        'dump the contents of the recorrdset onto the worksheet
        ThisWorkbook.Sheets("Data Dump").Range("A2").CopyFromRecordset rsData
        
        'close the recordset
        rsData.Close
        
        'fit the column widths to the data
        ThisWorkbook.Sheets("Data Dump").UsedRange.EntireColumn.AutoFit
        
        ThisWorkbook.Sheets("Data Dump").Move Before:=Sheets(2)
        
    Else
        MsgBox "Error: No records returned. Verify the year entered", vbCritical
        Exit Sub
    End If
    
    'clean up our ADO objects
    If CBool(objConn.State And adStateOpen) Then objConn.Close
    Set objConn = Nothing
    Set rsData = Nothing

    Application.ScreenUpdating = True

End Sub

Thanks

Andrzejek · Mar 2, 2012

At which line of your code do you get the error?

Have fun.

---- Andy

makeitwork09 · Mar 2, 2012

Andrzejek - I have that in the first code segment of my post.

Thanks

makeitwork09 · Mar 2, 2012

Code:

If Not rsData.EOF Then

xlbo · Mar 4, 2012

why do you have .refresh in yur ADODB.command object?

This may be affecting teh ability to set the result to the recordset which in turn cannot be accessed if it does not exist...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

makeitwork09 · Mar 4, 2012

Geoff

The .refresh is commented out, so that's not the problem.

Thanks

PHV · Mar 4, 2012

Your objConn object is never opened.
You assign a String to the ActiveConnection property instead of a Connection object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

makeitwork09 · Mar 5, 2012

Thanks PHV

I added

Code:

 objConn.Open sConnect

after

Code:

 Set objConn = New ADODB.Connection

.

I also modified the ActiveConnection so that reads as

Code:

ActiveConnection = objConn

, but I received the same error.

I am completely stumped by this one.

PHV · Mar 5, 2012

In fact, replace this:
.ActiveConnection = sConnect
with this:
Set .ActiveConnection = objConn

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

makeitwork09 · Mar 5, 2012

Thanks again PHV, I tried that and the same error returned.

I am thinking there is something within the sSQL that is causing the problem, but on in the Excel Macro because as I mentioned the resulting sSQL returned in the Immediate window runs in Microsoft SQL Server without a problem.

The reason I believe the macro does not like the sSQL is because I have a few other macros that use the same code without a problem. The difference between those sSQL statements and the one above is, the one above is using temporary tables.

xlbo · Mar 5, 2012

Looks like it is the temp table

The fix is to set NOCOUNT ON but it seems you aredoing that at the top of your SQL...I wonder if you need a "GO after that statement to bring it into effect?

alternatively see here:

http://support.microsoft.com/kb/235340

which seems to suggest that you can put it in a differetn place...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

makeitwork09 · Mar 13, 2012

Thanks Geoff,

I tried changing my ADO code to the way it is done in the link that you provided, but I am still striking out with this.

xlbo · Mar 13, 2012

maybe try converting the SQL to a stored proc (along with the NOCOUNT addition) and then executing the stored proc?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

makeitwork09 · Mar 15, 2012

Thanks Geoff, I would do that, but the database currently referenced in the SQL code will not always be the same.

I am not sure how, or if, in a stored procedure I can pass the database name as a parameter.

makeitwork09 · Mar 15, 2012

Alright, I am getting somewhere.

In the code I show above, there are two temporary tables. The final "select" statement is a joining of those two tempoarty tables.

I decided to try to determine what was happening by breaking up the SQL code into pieces. As such, I commented out everything after the first temporary table and added the following at the end of it

Code:

    sSQL = sSQL & "select * from #impairments"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "drop table #impairments"

When I ran the subroutine, it ran without an error.

I then did the following:
(1) Uncommented the coded for the second temporary table
(2) Removed the following code (directly above code)

Code:

    sSQL = sSQL & "select * from #impairments"
    sSQL = sSQL & vbCrLf

(3) added the following

Code:

    sSQL = sSQL & "select * from #allocation"
    sSQL = sSQL & vbCrLf

Doing this step resulted in the original error. That is an indication that Excel VBA does not like the multiple temporary tables. Now I need to determine how to make it like them.

Progress

makeitwork09 · Mar 15, 2012

Ugh!! I was wrong about the above. When I commented out the first temporary table to see if the second tempoary alone would work without the error, it did not.

I then uncommented the first temporary table, then placed a very simple temporary table after it followed by a select * for that simple temorary table and it ran without an issue. Therefore, there is something about the second tempoarty table that Excel VBA does not like.

I am wondering if it has anything to do with what I have put in bold.

Code:

sSQL = sSQL & "if OBJECT_ID('tempdb..#allocation' ,'u') is not null drop table #allocation"
    sSQL = sSQL & vbCrLf
    
    sSQL = sSQL & "select h.loan,f.loan_xref,h.participant,h.accounting_date,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "case when g.port_loan = 'Y' then "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "g.port_name"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "case when charindex(rtrim(t.loan), t.loan_name) != 0 then rtrim(substring(t.loan_name,1,charindex(rtrim(t.loan), t.loan_name)-1)) else t.loan_name end"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "    end as [loan_name],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "sum(case"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when q.total_value is null and o.appr_final_val is null then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_int_due_bal_p/r.number_of_properties"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when isnull(q.total_value,0) = 0 then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_int_due_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "(isnull(o.appr_final_val,0) / q.total_value) * h.inv_int_due_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "end) as [int_due_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "sum(case"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when q.total_value is null and o.appr_final_val is null then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_int_coll_p/r.number_of_properties"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when isnull(q.total_value,0) = 0 then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_int_coll_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "(isnull(o.appr_final_val,0) / q.total_value) * h.inv_int_coll_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "end) as [int_coll_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "sum(case when q.total_value is null and o.appr_final_val is null then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_prepd_int_bal_p/r.number_of_properties"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when isnull(q.total_value,0) = 0 then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_prepd_int_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "(isnull(o.appr_final_val,0) / q.total_value) * h.inv_prepd_int_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "end) as [prepd_int_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "sum(case when q.total_value is null and o.appr_final_val is null then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_accrd_int_bal_p/r.number_of_properties"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "when isnull(q.total_value,0) = 0 then"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "h.inv_accrd_int_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "(isnull(o.appr_final_val,0) / q.total_value) * h.inv_accrd_int_bal_p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "end) as [accrd_int_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "o.system_type,o.prop_type"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "into #allocation"
    sSQL = sSQL & vbCrLf
    [b]sSQL = sSQL & "from (select p.loan,p.participant,p.accounting_date,isnull(sum(p.int_due_bal_p),0) as [inv_int_due_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      isnull(sum(p.prepd_int_bal_p),0) as [inv_prepd_int_bal_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      isnull(sum(p.int_coll_p),0) as [inv_int_coll_p],"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      isnull(sum(p.accrd_int_bal_p),0) as [inv_accrd_int_bal_p]"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      from LMS_V11_YE_2011..porthist p"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      group by p.loan,p.participant,p.accounting_date) as h"[/b]
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..mit_property_appr as o on h.loan = o.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..mit_loan_appr_value as q on h.loan = q.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join (select l.loan,count(code) as number_of_properties "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " from LMS_V11_YE_2011..loancoll l WITH (NOLOCK)"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " where l.release_date is null "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & " group by l.loan) as r on "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "r.loan = o.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..loanhist as t on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "t.loan = h.loan and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "    t.accounting_date = h.accounting_date"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..partmast as a on"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "a.participant = h.participant"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..mit_loanxref as f on "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "f.loan = h.loan"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "left outer join LMS_V11_YE_2011..mit_extgeneralh as g on "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "g.loan = h.loan and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "    g.accounting_date = h.accounting_date"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "where t.loan_kind not like 'sf%' and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "  a.investor_type = 'internal' and"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "      h.participant not like 'nylim%'"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "group by h.loan,f.loan_xref,h.participant,h.accounting_date,"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "case when g.port_loan = 'Y' then "
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "g.port_name"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "else"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "case when charindex(rtrim(t.loan), t.loan_name) != 0 then rtrim(substring(t.loan_name,1,charindex(rtrim(t.loan), t.loan_name)-1)) else t.loan_name end"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "    end,o.system_type,o.prop_type"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "order by h.loan,h.accounting_date"
    sSQL = sSQL & vbCrLf

PHV · Mar 15, 2012

I'd replace all [inv_ with inv_

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

makeitwork09 · Mar 15, 2012

PHV - I will give it try.

Note, that the above code is all of the code for the second temporary table. The portion of code I put in bold is me simply wondering if that is the reason why this temporary table is not working.

Thanks

makeitwork09 · Mar 15, 2012

Changing [inv_...] to inv_... was not the issue. I will keep working to figure this out.

makeitwork09 · Mar 26, 2012

Thank the LORD!!

After taking a break from this for a week, I have FINALLY got it to work.

I modified my variables to be as follows:

Code:

    Dim sConnect As String
    Dim sSQL As String
    Dim oQt As QueryTable
    Dim ServerName As String
    Dim BkName As String

With that, I got rid of the ADO (command and recordsets) related code.

I replaced

Code:

    'create connection string
    sConnect = "Provider=SQLOLEDB;" & _
               "Data Source=" & ServerName & ";" & _
               "Initial Catalog=MS_IL;" & _
               "Integrated Security=SSPI"
        
    'create the connection and recrodset objects
    Set objConn = New ADODB.Connection
    
    'Set CONNECTION timeout property
    objConn.CommandTimeout = 0

    'Create a new command object to process the stored proc
    Set cmdl = New ADODB.Command
    
        With cmdl
            .ActiveConnection = sConnect
            'set COMMAND timeout property - query can time out on either the connection OR the command
            .CommandTimeout = 0
            .CommandText = sSQL
            .CommandType = adCmdText
'            .Refresh False
            Set rsData = .Execute()
    End With

with

Code:

    Set oQt = Workbooks(BkName).Sheets("Data Dump").QueryTables.Add(Connection:=sConnect, Destination:=Workbooks(BkName).Sheets("Data Dump").Range("A1"), Sql:=sSQL)

    oQt.Refresh

I am starting to wonder if my problem was related to the connection. It does not matter now, I have it working.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel 2007 VBA &quot;operation is not allowed when the object is closed&quot;

Technical User

Programmer

Technical User

Technical User

MIS

Technical User

MIS

Technical User

MIS

Technical User

MIS

Technical User

MIS

Technical User

Technical User

Technical User

MIS

Technical User

Technical User

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor

Excel 2007 VBA "operation is not allowed when the object is closed"