I was going through an article titled "Excel with Stored Procedures" written by Mr. David Poole where he described about how you can pull the data from the Excel to play with it. As I read the article I immediately went on to try what he described, but ended up nowhere because of an immediate obstacle (see the image below).

After I installed the feature it worked perfectly fine. However, I hit the same wall whenever I tried to exercise the same thing on other PCs, which made me think over an alternate solution to this.

I had a similar issue in a banking organization where I delivered the solution by writing a macro in Excel and giving the .xls file to my users, which they ran on their PCs and pulled the data for their analysis. The issue was that we had an SQL Server database that was driven by a front end application written in ASP.NET. Each table of the database had a trigger which used to record DML operation as a log in another table called audit trail.

Now whenever the users wanted to view the history for a particular table, they used to query the audit trail table using the front end we provided. However, over a period of time the table's size grew rapidly and downloading the report became a big time consuming process.

Moreover, the users started demanding another way out as the data received over HTTP was not properly formatted in the Excel file and they needed to format it for their use.

I'm pasting the code below to show how I did the automation..

Sub Audit_Trail_Bank()
Dim sql As String
Dim fst As String
Dim rs As ADODB.Recordset
Dim con As ADODB.Connection
Dim wsht As Worksheet
Dim row, col, col1 As Integer
Dim frdid

<PART_1>
Set con = New ADODB.Connection
Set rs = New Recordset
row = 2
col = 1
col1 = 1
constr = "Provider=sqloledb;Data Source=yourdatasource;Initial Catalog=databasename;User Id=userid;Password=password"
con.Open constr
frdid = InputBox("Enter Fraud Id", "Fraud Id")

sql = "select '',d.bank_fraud_file_id [Fraud File No.]," & _
" frdnum [RBI No.],table_name [Table Name],column_name [Field Name]," & _
" old_value [Old Value],new_value [New Value]," & _
" b.emp_name [Modified By],a.modified_on [Modified On]," & _
" d.Status,d.reporting_date [Reporting Date],d.Closed," & _
" d.closure_date [Closure Date] from audit_trail a" & _
" left join user_master b on a.modified_by = b.user_id" & _
" left join rbi_mapping c on a.primary_key = c.fraudid and fraudtype = 2" & _
" left join bank_fraud_case d on a.primary_key = d.bank_fraud_id" & _
" where table_name like '%bank%'" & _
" and primary_key in ( select bank_fraud_id from bank_fraud_case where bank_fraud_file_id in" & _
" (" & frdid & ")) order by a.modified_on, primary_key, column_name, table_name"
rs.Open sql, con
</PART_1>

<PART_2>

Set wsht = Worksheets(1)
wsht.Cells.Clear
While col1 < rs.Fields.Count
wsht.Cells(1, col1).Value = rs(col1).Name
wsht.Cells(1, col1).Font.Bold = True
wsht.Cells(1, col1).Columns.AutoFit
col1 = col1 + 1
Wend
</PART_2>
<PART_3>
While Not rs.EOF
While col < rs.Fields.Count
If Not IsNull(rs(col).Value) Then
wsht.Cells(row, col).NumberFormat = "@"
wsht.Cells(row, col).Value = CStr(rs(col).Value)
Else
wsht.Cells(row, col).Value = "--"
End If
col = col + 1
Wend
row = row + 1
col = 1
rs.MoveNext
'Wend
Wend
rs.Close
con.Close
wsht.Columns.AutoFit
wsht.Name = "Bank_Audit_Trail_" & frdid
Beep
MsgBox ("Operation Completed")
End Sub

</PART_3>

I've divided the code in three parts!!

Part-1 shows the typical objects you need to declare i.e. a Recordset object to traverse the fetched data row by row and a connection object to connect to the database.

The important thing (I feel) is that some user interface can be given using simple functions like VB InputBox from which you can allow the users to input the data. However the fact is that you can design fully fledged VB forms with smart custom input validators and bla bla bla ................ , but here we'll keep the things simple.

So in the code above after getting the parameter from the user I prepare a query which is given to the recordset object which pulls the data from the database..

In part-2, reference of the first Excel sheet is taken and the column names are written in a loop in the first row. Finally, the actual data is written in part 3. A few good things about this code is that you can modify the data according to the user needs for e.g. by writing

wsht.Columns.AutoFit
wsht.Name = "Bank_Audit_Trail_" & frdid

I could autofit all the columns and also renamed the sheet so that it increased the readability of data.

Its PERFORMANCE was also pretty good because earlier we used to generate and process the data on IIS which routed that data to browser's end using HTTP which resulted in higher bandwidth consumption (too many users downloading the report @ a time) as well as improper Excel output.

One drawback is that macro execution can be disabled on some machines but I think there are ways to digital sign a macro and then distribute it, you can of course find some links on that. Mail servers can also filter the office documents containing macros.

Still I guess that the benefits of this approach far outweigh the small and petty drawbacks. Hope the approach will help the people look at another way (not immediately acceptable I accept ::) of reporting :))..

HAPPY PROGRAMMING!!