Tip: Integrate Microsoft Excel with SQL Server using OLEDB
Microsoft Excel is an excellent financial analysis tool, but contrary to many users, it makes a lousy database. SQL Server on the other hand is a first class RDBMS and offer several technologies to allow software like Microsoft Excel to interface with it. Using Oledb, Microsoft Excel can also serve as a client and interface with SQL Server.
A developer can elect to send a single value, a range or a whole sheet to SQL Server using standard SQL language and Oledb API.
Excel as a oledb Client
The following code demonstrates capturing a range value. This sub can be called when a user enters a cell or leaves a cell. The Range object can also be used to retrieve a cell value from another cell on the same sheet.
Public Sub SendRangeValueToSQLServer(ByVal Target As Range) On Error GoTo errorHandler Dim rangeValue Dim rangeAddress Dim anotherValue Set rangeValue = Target.Value2 Set rangeAddress = Target.Address Set anotherValue = Target.Worksheet.Cells(2, "A") DbConnection rangeValue, rangeAddress, anotherValue Exit Sub errorHandler: ' MsgBox Err.Description, vbCritical, "Application Error" Resume Next End Sub
This method, DbConnection, uses standard oledb syntax to setup an AdoDB Connection, Command and Parameters.
Sub DbConnection(rangeValue As String, rangeAddress As String, anotherValue As String) On Error GoTo errorHandle Dim cnn As ADODB.Connection Dim cmd As ADODB.Command Dim cnnStr As String Dim rng As ADODB.Parameter Dim rngAddr As ADODB.Parameter Dim rngValue As ADODB.Parameter cnnStr = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=DatabaseName;Integrated Security=SSPI;" Set cnn = New ADODB.Connection cnn.ConnectionString = cnnStr cnn.Open If cnn.State = adStateOpen Then Set cmd = New ADODB.Command 'Set up the command object and execute With cmd .ActiveConnection = cnn .CommandText = "InsertRangeValues" .CommandType = adCmdStoredProc Set rng = .CreateParameter("@rng", adVarchar, adParamInput, 50, rangeValue) .Parameters.Append rng Set rngAddr = .CreateParameter("@rngAddr", adVarchar, adParamInput, 10, rangeAddress) .Parameters.Append rngAddr Set rngValue = .CreateParameter("@rngValue", adVarchar, adParamInput, 20, anotherValue) .Parameters.Append rngValue .Execute End With End If Exit Sub errorHandle: 'Err.Description MsgBox Err.Description, vbOKOnly, "error" End Sub
Oledb Alternate Solution
An alternate version of DbConnection can insert the contents of an entire sheet as if it was a database table.
Sub DbConnection() On Error GoTo errorHandle Dim cnn As ADODB.Connection Dim cmd As ADODB.Command Dim cnnStr As String cnnStr = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=DatabaseName;Integrated Security=SSPI;" Set cnn = New ADODB.Connection cnn.ConnectionString = cnnStr cnn.Open If cnn.State = adStateOpen Then Set cmd = New ADODB.Command 'Set up the command object and execute With cmd .ActiveConnection = cnn .CommandText = "Insert into a sqlTable Select * from ExcelSheetName" .CommandType = adCmdText .Execute End With End If Exit Sub errorHandle: 'Err.Description MsgBox Err.Description, vbOKOnly, "error" End Sub
At a minimum, you need to Microsoft.ActiveX Data Objects depending on the Excel version on your computer. You also may need to use Microsoft ActiveX Data Objects Recordset 2.8 Library or the version corresponding to your Excel version.
Microsoft has put considerable effort in developing an extensive API for data manipulation across all its products. They are all designed for ease of use to help keep the cost of ownership at a minimum and a trough Rapid Application Development environment.
More by this Author
This is a tutorial on using the SAP .Net Connector, NCO 18.104.22.168 for .Net 4 and Visual Studio 2010.
There are several ways to use Objective-C to read, write and create files and directories. This tutorial presents one way, which is adapted for the IOS 5 SDK.
A free and complete software development proposal template with budget, ROI and CAGR formulas and instructions. Detailed instructions to help you prepare a winning software development proposal.
No comments yet.