How to perform 'insert rows' instead of 'copy rows' using .copyrecordset (excel vba)

praful k

New Member
Joined
Jul 11, 2013
Messages
2
I am new to excel VBA. I have a requirement where I have to copy table values from sql server 2005 to an excel worksheet. I have googled and written a code for the above requirement (listed below).

In this excel sheet there are fixed set of rows which displays legends and dates. These rows should be displayed after the database/table values are printed. As I am using .CopyFromRecordset to copy the records from the recordset to excel sheet, the rows which are displaying legend and dates are overwritten with the database/table values. Please let me know how to perform insert of rows instead of copy. Or is there any way to achieve the above.

---CODE------------

Sub GETSQLSERVERDATA()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim USERID As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

USERID = Range("C1").Value 'Input form excel template.
Server_Name = "" 'Enter server name
Database_Name = "" 'Enter database name
User_ID = "" 'SQL server user id
Password = "" SQL server password

SQLStr = "SELECT END_DATE,PERIOD FROM PERIOD_MAP WHERE USERID='" + USERID + "'"

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic

With Worksheets("Sheet1").Range("A2:D2") ' Enter your sheet name and range here
.ClearContents
.CopyFromRecordset rs
End With

rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,212,933
Messages
6,110,757
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top