i am new to VB.
i am trying to execute SQL Stored procedures from a macro in excel.
but i fail to do so. i managed to write a code that imports a table, but not to execute a SP or a function.
heres my code:
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=LENOVO-325FBA33\SQLEXPRESS;INITIAL CATALOG=StagingArena;" '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 Genres" ' 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
i am working with ADO commands, and if anyone can direct me to the execute commands.
Edited 6 Years Ago by WaltP: Fixed CODE Tags -- use the PREVIEW button before the SUBMIT button