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:
The code is as follow:
Public Sub copDataOve(Query As String) On Error GoTo 0 Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection Dim rsPubs As ADODB.Recordset Set rsPubs = New ADODB.Recordset Dim strConn As String Dim strServer As String Dim strDB As String strConn = "PROVIDER=SQLOLEDB;" strServer = Trim(ThisWorkbook.Worksheets("Settings").Range("D6").Text) strDB = Trim(ThisWorkbook.Worksheets("Settings").Range("D11").Text) strConn = strConn & "DATA SOURCE=" & strServer & ";INITIAL CATALOG=" & strDB & ";" strConn = strConn & " INTEGRATED SECURITY=sspi;" cnPubs.Open strConn rsPubs.Open Query, cnPubs, adOpenStatic ThisWorkbook.Worksheets("Test").Range("myRange1").CopyFromRecordset rsPubs cnPubs.Close Set rsPubs = Nothing Set cnPubs = Nothing End Sub
Any help would be so much apprecaited. I have already tried SET NOCOUNT ON in my SQLscript but to no success.