-
Solved: Import External data into Excel
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.
-
Import External data into excel
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules