Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import data with microsoft query | Excel Programming | |||
Method or Data Member Not Found error, after connecting to Microsoft SQL Server | Excel Programming | |||
How do I import Microsoft Word files into Microsoft Excel? | New Users to Excel | |||
How do I import data from Microsoft Project 2003 to Excel? | Excel Discussion (Misc queries) | |||
Generate XLS on server without Microsoft Excel | Excel Programming |