Excel VBA到SQL Server，没有SSIS。

Excel problem: User clicks a button and VBA parses an input file, putting data into cells in the spreadsheet. Then she mails copies of the spreadsheet to people who do work with the data.

Excel问题:用户单击一个按钮，VBA解析一个输入文件，将数据放入电子表格中的单元格中。然后，她将电子表格的副本邮寄给使用这些数据的人。

I am to replace this with SSRS or ASP or Sharepoint displaying the data from SQL Server.

我将用SSRS或ASP或Sharepoint来替换它，它们显示来自SQL Server的数据。

In order to work on this without interrupting the current process, I'd like to have the Excel VBA, each time it writes a row to the spreadsheet, also insert it into the SQL Server DB via stored proc.

为了在不中断当前进程的情况下处理这个问题，我希望使用Excel VBA，每次它向电子表格写入一行时，也要通过存储的proc将其插入到SQL Server DB中。

I can have it write the row in CSV to a file for later SSIS import, but I'd rather go direct to the DB.

我可以让它用CSV将行写入文件中，以便以后的SSIS导入，但我宁愿直接到DB。

I know how to do it in VB.Net but I've never written data in VBA (often read data into recordset but not written).

我知道怎么用VB。但我从来没有在VBA中编写数据(通常将数据读入记录集但不写入)。

I'd prefer to pass the values as params to a stored proc, but I could generate the slower INSERT command for each row if I have to.

我更希望将值作为params传递给存储的proc，但是如果需要的话，我可以为每一行生成更慢的INSERT命令。

3 个解决方案

#1

From VBA, the easiest data-access library to use is ADO. Add a reference to "Microsoft ActiveX Data Objects Library" so that you can use the ADODB.* objects.

在VBA中，使用最简单的数据访问库是ADO。添加对“Microsoft ActiveX数据对象库”的引用，以便您可以使用ADODB。*对象。

To execute a stored proc (which in your case will add a record to a table), you could do it:

要执行存储的proc(在您的情况下，它将向表添加一条记录)，您可以这样做:

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

…惰性方式(直接创建SQL语句，不使用参数对象;这很容易导致sql注入攻击):

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 deals with encoding of all parameters, and is thus not prone to SQL-injection hacks - either deliberate or accidental):

…或者正确的方法(处理所有参数的编码，因此不容易被sql注入破坏——不管是故意的还是偶然的):

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

#2

How do you read data with VBA?

如何使用VBA读取数据?

If you use ADO recordsets: Have a look at the ADODB.Command class; this allows you to execute SQL or stored procedures and pass parameters to it (Google for ado command example).

如果您使用ADO记录集:请查看ADODB。命令类;这允许您执行SQL或存储过程并将参数传递给它(对于ado命令示例，谷歌)。

If you use DAO recordsets: The Execute method of your DAO database allows you execute SQL statements.

如果您使用DAO记录集:DAO数据库的执行方法允许您执行SQL语句。

#3

In the long run, people are finally beginning to accept a better way: automation (not a button click) reads the file directly into the DB (SSIS), and people who need the data look at a report instead of an e-mailed Excel file.

从长远来看，人们终于开始接受一种更好的方式:自动化(而不是按钮点击)直接将文件读入DB (SSIS)，而需要数据的人查看报告而不是电子邮件的Excel文件。

#1