by April 3, 2003 0 comments



To enter data in Excel, you can either type it in or import it from different sources. It’s easy to import data from a standard
.CSV format, but what about importing it from a database? You can directly run SQL queries on a database
from Excel. 

The traditional method was to create a Macro and write a long Visual Basic code by using ADO connection to get connected with the source. But, now with the help of SQLRequest method, you can directly execute an SQL query from an Excel worksheet or a macro. It then returns the result as an array, directly to the Excel worksheet.
Connect to the database We tried this using MS-SQL 2000 Server for the database and Excel from MS Office XP. To query the database, start Excel, go to the Tools menu and click on Add-Ins. Now select the ODBS Add-In check box. If you don’t find it, then it was probably not installed during the Office installation. One easy way is to go to http://office.microsoft. com/downloads/2002/xlodbc.aspx and download xlodbc.exe from there and run it.

Adding this Reference will
enable the module to use
SQL Queries

Next, create a System DSN (System Data Source Name) by going to Control panel>Administrative tools>ODBS (Data Source). Then go to System DSN tab and click on the Add button. 

Now, select your Server type (in our case it was the SQL server; you can choose according to you infrastructure) and click on Finish. It will ask for the DSN name (the name by which your connection will be recognized), description of DSN and the SQL Server name. After filling the appropriate data click on Next. It will now ask for the authentication method. Select SQL-Server Authentication and enter the Login Id as
“sa”.

Click on Next, and it will ask you to select the table name. After selecting the appropriate table name click on Next and Finish.

Retrieve data
To retrieve data from the data source to Excel, insert the formula in a cell in the following format: 
=SQL.REQUEST(connection_string, output_ref, Driver_prompt, Query_text,
col_names_logical)

In this, connection_string is the System DSN name. output_ref is a cell reference where you want the completed connection string placed. The latter is used to get the whole table as in array in the Excel sheet, which is discussed below.

Driver_prompt(optional) specifies when the driver prompt will be displayed. If this field is kept blank, it takes a value of 2 by default, which means only the Driver Dialogue box will be displayed if the provided specification is not sufficient for the connection.

Query_text is the actual query that is executed on the SQL server.

Col_names_logical is a Boolean variable that describes that the field name of any row will be displayed (True) or not (False) in the Excel sheet.

Now let’s put some actual data into the formula.
=SQL.REQUEST(“DSN=xlcon”, A1, 2, “select name from tab1 where &$C$5& > age”, False)

Here, it will try to connect to an SQL Server via the xlcon DSN and execute a query to print the value from the name field, which has a greater value of age field than the value in C5 cell.

Macros
Now that we’ve written a simple SQL query in Excel, let’s create a Macro that will print the entire table from the database into the Excel sheet. To do this, press Alt+ F11 to start the Visual Basic editor. Go to the Tool menu, click on References and select XLODBC.XLA. Finally, add the following code to the sheet1 code window:

Private databaseName As Variant
Private returnArray As Variant
Private queryString As Variant
Private Sub Worksheet_Activate()
databaseName = “xlcon”
queryString = _
“SELECT * FROM tab1 “
returnArray = SQLRequest(“DSN=” & databaseName, _
queryString, _
Worksheets(“Sheet1”).Range(“A1”), _
2, True)
‘ nested Loop Starts Here
For i = LBound(returnArray, 1) To UBound(returnArray, 1)
For j = LBound(returnArray, 2) To UBound(returnArray, 2)
Worksheets(“Sheet1”).Cells(i, j).Formula = _
returnArray(i, j)
Next j
Next I
‘ nested Loop Ends Here
End Sub

Here, we are using a DSN called xlcon similar to the previous example. First of all, the values are set in the different variables. So that the databaseName contains the name of the DSN (xlcon), queryString contains the actual query to be executed on the Data Source and the returnArray contains the result generated or received after the execution of the query through SQLRequest Method. Now a nested loop is created that will place the data one-by-one from the returnArray to the Excel sheet. 

Anindya Roy

Related Articles

Secure and Monitor your Internet Gateway

Import Data to MS SQLá

No Comments so far

Jump into a conversation

No Comments Yet!

You can be the one to start a conversation.

Your data will be safe!Your e-mail address will not be published. Also other data will not be shared with third person.