CODEWITHSUNDEEP
sql-serverexcelvba
Peter Sang-Hee Choi
Peter Sang-Hee Choi

Reputation: 11

SQL query to excel spread sheet use vba macro

I have problem to bring SQL Server data to Excel.

So anyone can help me out how to connect to SQL Server to use vba macro? And how to make vba script to SQL query to Excel spreadsheet?

Thanks.

Upvotes: 0

Views: 20669

Answers (1)

Conrad Lotz
Conrad Lotz

Reputation: 8838

In Office 2007 go to Data tab and then Get External Data you will see the optionto connect to SQL and in 2010 go to Data tab and click on Other Existing Sources and then select SQL Server. This is to pull the data into worksheets to manipulate directly.

In VBA only

In the VBA script editor include the ActiveX Data Objects reference

Here is an code example on how to connect to SQL from VBA

Sub Connect2SQLXpress()
Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oCon = New ADODB.Connection
oCon.ConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=DB1; Trusted_Connection=yes;"
oCon.Open
Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = "Select * From Table1"
oRS.Open
Range("A1").CopyFromRecordset oRS
oRS.Close
oCon.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
End Sub

To enable the Visual Basic editor in Excel read the following link to enable the Developer Tab

This should provide a quick overview to what you require.

Upvotes: 3

Related Questions