Import data from Microsoft SQL Server into Microsoft Excel
I am trying to create a Excel VBA script that I can use to import data from SQL Server into MS Excel. I used the MS support article listed below to create a Sub DataExtract procedure. I completed the instructions from the Microsoft article but was unable to import data from the authors table. No errors were output when running the macro. I am using Excel version 2003 with Microsoft ActiveX Data Object Library version 2.8. Please help resolve the problem with this script or another simple script that will import data from SQL Server into MS Excel. Thanks, http://support.microsoft.com/default.../306125/EN-US/ 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=(local);INITIAL CATALOG=pubs;" '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 Authors" ' 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 |
All times are GMT +1. The time now is 08:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com