Hi All,
Please let me know how to copy the recordset into excel sheet. I am not able to do this using the below coding.
I am getting an Error 403: Class does not support Automation or does not support expected interface.
The below is the code i am writing.
Public cn As New ADODB.Connection
Public objMyCmd As New ADODB.Command
Public objMyRecordset As New ADODB.Recordset
Sub DBConnectionOpen()
Dim constring As String
Dim query As String
Dim rptDate As Date
constring = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName
'cn.Open constring
query = QueryString
ActiveWorkbook.Worksheets("OutputReport").Activate
rptDate = ActiveSheet.Range("B2").Value
'Replace ( string1, find, replacement, [start, [count, [compare]]] )
query = Replace(query, "$", rptDate)
'Set and Excecute SQL Command
objMyRecordset.Open query, constring
ActiveSheet.Range("A3").CopyFromRecordset (objMyRecordset)
objMyRecordset.Close
End Sub
Thanks
Please let me know how to copy the recordset into excel sheet. I am not able to do this using the below coding.
I am getting an Error 403: Class does not support Automation or does not support expected interface.
The below is the code i am writing.
Public cn As New ADODB.Connection
Public objMyCmd As New ADODB.Command
Public objMyRecordset As New ADODB.Recordset
Sub DBConnectionOpen()
Dim constring As String
Dim query As String
Dim rptDate As Date
constring = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName
'cn.Open constring
query = QueryString
ActiveWorkbook.Worksheets("OutputReport").Activate
rptDate = ActiveSheet.Range("B2").Value
'Replace ( string1, find, replacement, [start, [count, [compare]]] )
query = Replace(query, "$", rptDate)
'Set and Excecute SQL Command
objMyRecordset.Open query, constring
ActiveSheet.Range("A3").CopyFromRecordset (objMyRecordset)
objMyRecordset.Close
End Sub
Thanks