How to Transfer data from SQL Express to Microsoft office excel
By bryan tugade
Have you try to transfer your data fro SQL Express to Microsoft office excel? What if your company wants you to get all the data from
your database and put it in excel. Theres two way i know how to do it. Here it is.
Well the first one is the hardest. The copy and paste method.
1. Copy the Data from the database.
2. Then paste it microsoft excel.
That's the hard part. I guess. : )
The second method is through vba.
1. Put a activeX object control to your worksheet.
2. In design mode double click the created button.
3. Once your in the vba project environment.
4. Create a connection.
5. Once you created a connection to your sql database, set the workbook, sheet and
range to your excel.
6. Then Transfer the data using this code.
excelRange.CopyFromRecordset rs
7. Exit the design mode. Then test you application by clicking the button to see
if its working.
Here is the code :
Option Explicit
Private Sub CommandButton1_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlStr As String
Dim consStr As String
Dim excelRange As Range
Dim ExcelBook As Workbook
Dim ExcelSheet As Worksheet
consStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=db-to-excel;Data Source=your-sql-server\SQLEXPRESS"
sqlStr = "SELECT * FROM user_table"
Set conn = New ADODB.Connection
With conn
.CursorLocation = adUseClient
.Open consStr
.CommandTimeout = 0
Set rs = .Execute(sqlStr)
End With
Set ExcelBook = ActiveWorkbook
Set ExcelSheet = ExcelBook.Worksheets(1)
Set excelRange = ExcelSheet.Range("A3")
excelRange.CopyFromRecordset rs
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
Here is the result
Thats it! Happy coding.
Related FAQs
Do you want to develop in microsoft office environment? Let say you want to add buttons in microsoft excel, you can use the developer tab to create a vba applications. Developer tab is hidden by default in microsoft office. You can show this tab in less than a minute. Here's how.
Assuming that you need to create an application that needs to transfer data from sql server to microsoft excel. The first thing you need to do is to find a way to connect to sql server. We can do this by using visual basic application.
Let say you need to develop an application that incorporate sql database to your Visual basic application(vba) in microsoft excel. So you open a workbook in excel then enabled the developer tab and go to vba window. But unfotunately when your creating a variable to instantiate the adodb, it doesn't show in the intellisense. Do you know why?
It is because you haven't add the microsoft activex data objects 2.8 library to your references.
Here's how to add it to your references.
How to Transfer data from SQL Express to Microsoft office excel (1362 Views)