“Operation is not allowed when the object is closed”

Question

I'm trying to run the following code, but I keep getting the same error. Could someone please help me? Many Thanks in advance!

Sub new4()
    Dim dateArray(1 To 2, 1 To 2)
    Dim startCell As Range, sectorISIN As Range, bespokeISIN As Range
    Dim Wb As Workbook
    Dim Ws As Worksheet, tempWs As Worksheet
    Dim lastIsinRow As Integer
    Dim btype(1 To 2) As String
    Dim newfilename As String
    Dim broker As String
    Dim startdate As String, endDate As String

    'ADODB memory allocation
    Dim orConn As New ADODB.Connection
    Dim orRst As New ADODB.Recordset
    Dim orCmd As New ADODB.Command
    Dim orTDS As New ADODB.Parameter
    Dim orTDE As New ADODB.Parameter
    Dim orCCY As New ADODB.Parameter
    Dim orBTYPE As New ADODB.Parameter
    Dim orBro As New ADODB.Parameter
    Dim orVar As New ADODB.Parameter
    shtName = ActiveSheet.name
    If shtName = "Monthly" Then
        Set cmdWs = ActiveWorkbook.Worksheets("Monthly")
        srcRowNo = 30
    End If

    'ADODB connection
    Set orConn = getConn(cmdWs.Range("E1").Value)
    orCmd.ActiveConnection = orConn
    orCmd.CommandTimeout = 500
    Set paramWs = ActiveWorkbook.Worksheets("Parameters")
    todayDate = cmdWs.Range("C1").Value
    broker = "Stifel Nicolaus Europe Ltd"
    ccy = "GBP"
    btype(1) = "CB"
    btype(2) = "TM"
    newfilename = cmdWs.Range("D" & srcRowNo)

    ' Connection stuff
    Set orTDS = orCmd.CreateParameter("TradeDateStart", adDBDate, adParamInput, , Null)
    Set orTDE = orCmd.CreateParameter("TradeDateEnd", adDBDate, adParamInput, , Null)
    Set orCCY = orCmd.CreateParameter("inCCY", adVarChar, adParamInput, 3, Null)
    Set orBTYPE = orCmd.CreateParameter("inBType", adVarChar, adParamInput, 2, Null)
    Set orBro = orCmd.CreateParameter("inBroker", adVarChar, adParamInput, 150, Null)
    Set orVar = orCmd.CreateParameter("InVariable", adVarChar, adParamInput, 150, Null)


    orCmd.Parameters.Append orTDS
    orCmd.Parameters.Append orTDE
    orCmd.Parameters.Append orCCY
    orCmd.Parameters.Append orBTYPE
    orCmd.Parameters.Append orBro
    orCmd.Parameters.Append orVar

    ' Set up parameters
    startdate = cmdWs.Range("J" & srcRowNo).Value    ''Monthly
    endDate = cmdWs.Range("K" & srcRowNo).Value    ''Monthly
    todayDate = Format(cmdWs.Range("C1").Value, "dd-mmm-yyyy")
    ' Point to parameters for bespoke isin list
    lastIsinRow = paramWs.Range("BU4").End(xlDown).Row
    Set bespokeISIN = paramWs.Range("BU4:BU" & lastIsinRow)

    'Clear and Fill sector mapping temp table
        orConn.Execute ("delete from TT_exl_sector")
    For Each c In bespokeISIN
        orConn.Execute ("insert into TT_exl_sector values ('" & c.Value & "','" & c.Offset(0, 1).Value & "')")
    Next c
    'Create new file
    Workbooks.Add
    Set Wb = ActiveWorkbook
    Useful_Functions.MarkitColours Wb
    For bcount = 1 To 2
       Set Ws = Wb.Worksheets.Add
       'Set startCell = Ws.Range("A8")

       Ws.name = btype(bcount) & ("_DTL")
       orTDS.Value = startdate
       orTDE.Value = endDate
       orCCY.Value = ccy
       orVar.Value = btype(bcount)

       orCmd.CommandText = "{Call MSA.EXL_STifel_MS(?,?,?,?,?,?)}"
       Set orRst = orCmd.Execute
       **Ws.Range("A8").CopyFromRecordset orRst**
       orRst.Close

    Next bcount

        Application.DisplayAlerts = False
        Sheets("Sheet1").Delete

        ActiveWorkbook.SaveAs fileName:=OutputFileLocation & newfilename, FileFormat:= _
                        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
                        , CreateBackup:=False



    Wb.Close
    Application.DisplayAlerts = False

End Sub

Answer 1

Right after this line:

Set orConn = getConn(cmdWs.Range("E1").Value)

Add this:

orConn.Open

That will open the database connection.