Exporting Sql Server Table to MS Excel

Here is the Coding for Exporting the any Sql Server to Excel Sheet using VBA

a. Take an excel sheet and place the Database Name and Table name in any 2 cells as below



In my Sheet i placed the Database Name and Table name on cells M12 and cells M14 respectively.

Place a button just below them and enter the following code by creating a new Module in Visual Basic Editor(alt + F11)


Sub DataExtract()

Dim tablename As String
Dim Databasename As String
' Create a connection object.
Dim cnPubs As New ADODB.Connection

tablename = Range("M14").Value
Databasename = Range("M12").Value

Worksheets("Sheet2").Cells.ClearContents
Sheet2.Cells.Font.Name = "Tahoma"
Sheet2.Cells.Font.Size = "8"

'Provide the connection string.
Dim strConn As String

cnPubs.Provider = "sqloledb"

'Use the SQL Server OLE DB Provider.
strConn = "DATA SOURCE=Server Name;INITIAL CATALOG =" & Databasename & ";user id = ;password = "

''Change the Connecting String (www.connectionstrings.com)
'Now open the connection.
cnPubs.Open strConn

Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset


With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.

.Open "SELECT * FROM " & tablename
' Copy the records into cell A1 on Sheet1.
Sheet2.Range("A1").CopyFromRecordset rsPubs

' Tidy up
.Close
End With

Sheet2.Rows.Font.Bold = False

Sheet2.Activate
Rows("1:1").Select
Selection.Insert Shift:=xlDown


Dim i As Integer

For i = 0 To rsPubs.Fields.Count - 1
Sheet2.Cells(1, i + 1) = rsPubs.Fields(i).Name
Next

Sheet2.Rows(1).Font.Bold = True


cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub




When the import button is clicked The Sql Server Table will be imported to Sheet 2 as below!!!