Solved: Import External data into Excel

[abhay_547]

Hi All,

I am trying to import some data using below code from SQL server to my excel. I just want to know How can I user my user id and and password to as login details instead of integrated security = sspi

[VBA]Sub Dataextract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(DB-77716EFB0314\SQLEXPRESS);INITIAL CATALOG=meta_data;"

'Use an integrated login.

strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
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 Cost Center mapping"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsPubs

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub
[/VBA]

Thanks for your help in advance.

[abhay_547]

Hi All,

Finally I got it. It's working now. I use the below code for the same.

[VBA]Public ADOCn As ADODB.Connection
Public adoRS As ADODB.Recordset
Public gstrConnString As String
Public Sub OpenDB()
gstrConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Persist Security Info=False;Initial Catalog=sap_data;" _
& "Data Source=DB-77716EFB0313\SQLEXPRESS"

Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = gstrConnString
ADOCn.Open gstrConnString
End Sub
[/VBA]

Any how, thanks a lot for your help.