Excel VBA for SQL Server without SSIS

Question

The problem with Excel: the user clicks the button, and VBA analyzes the input file, placing the data in cells in a spreadsheet. She then sends copies of the spreadsheet to those who work with the data.

I have to replace this with SSRS or ASP or Sharepoint, displaying data from SQL Server.

To work on this without interrupting the current process, I would like to have Excel VBA, every time it writes a row to a spreadsheet, also insert it into the SQL Server database through a stored procedure.

I can write a string in CSV to a file for later importing SSIS, but I would rather go directly to the database.

I know how to do this in VB.Net, but I never wrote data in VBA (I often read data in a recordset, but did not write).

I would prefer to pass the values ​​as params to the stored proc, but I could generate a slower INSERT command for each row if I need to.

Answer 1

From VBA, the simplest data access library is ADO. Add a link to the Microsoft ActiveX Data Objects Library so you can use ADODB objects. *.

To execute the stored procedure (which in your case will add an entry to the table), you can do this:

... lazy way (creating SQL statements directly, without using Parameter objects, this is subject to SQL injection hacks):

Public Sub AddFoo _ ( _ strServer As String, _ strDatabase As String, _ strUsername As String, _ strPassword As String, _ lFooValue As Long _ ) ' Build the connection string Dim strConnectionString As String strConnectionString = "Driver={SQL Server}" _ & ";Server=" & strServer _ & ";Database=" & strDatabase _ & ";UID=" & strUsername _ & ";PWD=" & strPassword ' Create & open the connection Dim oConnection As Connection Set oConnection = New Connection oConnection.ConnectionString = strConnectionString oConnection.Open ' Build the SQL to execute the stored procedure Dim strSQL As String strSQL = "EXEC AddFoo " & lFooValue ' Call the stored procedure Dim oCommand As Command Set oCommand = New Command oCommand.CommandType = adCmdText oCommand.CommandText = strSQL oCommand.ActiveConnection = oConnection oCommand.Execute oConnection.Close End Sub 

... or the correct way (which concerns the encoding of all parameters and, therefore, is not subject to SQL injection hacks - intentional or random):

 Public Sub AddFoo _ ( _ strServer As String, _ strDatabase As String, _ strUsername As String, _ strPassword As String, _ lFooValue As Long _ ) ' Build the connection string Dim strConnectionString As String strConnectionString = "Driver={SQL Server}" _ & ";Server=" & strServer _ & ";Database=" & strDatabase _ & ";UID=" & strUsername _ & ";PWD=" & strPassword ' Create & open the connection Dim oConnection As Connection Set oConnection = New Connection oConnection.ConnectionString = strConnectionString oConnection.Open ' Build the SQL to execute the stored procedure Dim strSQL As String strSQL = "EXEC AddFoo " & lFooValue ' Create the command object Dim oCommand As Command Set oCommand = New Command oCommand.CommandType = adCmdStoredProc oCommand.CommandText = "AddFoo" ' Create the parameter Dim oParameter As Parameter Set oParameter = oCommand.CreateParameter("foo", adParamInteger, adParamInput) oParameter.Value = lFooValue oCommand.Parameters.Add oParameter ' Execute the command oCommand.ActiveConnection = oConnection oCommand.Execute oConnection.Close End Sub 

Answer 2

How do you read data using VBA?

If you are using the ADO recordset: look at the ADODB.Command class; this allows you to execute SQL or stored procedures and pass parameters to it (example of the Google command for ado).

If you are using the DAO recordset: the Execute method of your DAO database allows you to execute SQL queries.

Answer 3

In the end, people begin to take the better way: automation (not the click of a button) reads the file directly into the database (SSIS), and people who need data look at the report instead of the e-mail Excel file.