Excel Developers – who are You?

I had scheduled two posts this week about:
–  .NET books
– Classic COM Add-in for The Transition Case.

However, Simon Murphy has recently published an article about Excel Developers that I find to be very interesting and also very important. Therefore this week’s post is about this subject.

Excel has been around for more then 20 years now and in my opinion it’s about time to both set and discuss best practice, standards and also single out the group of Excel developers. That’s why I believe it’s important to take part of how Simon views the group of Excel Developers.

In my opinion the presented model is indeed a good start but as Simon says:
“There is a third dimension not considered in the diagram, and that is knowledge of the clients business”

Over the time the understanding of the underlying business processes has become my #1 concern, especially when working with large corporates.  It’s not only to understand them but perhaps the most important aspect is to give input how to reflect the processes in Excel based tools. An interaction between the client and the Excel developer will then be possible where the output of the process will be the mutual solution.

For me it would be a welcome development of the ‘landscape’ if the business processes could be reflected in the 3rd dimension in it.

Kind regards,
Dennis

Using Loaders

Loaders vs Front Loaders

Both Loaders and Front Loaders are standalone solutions, developed with tools like classic VB and VB.NET, which target to manipulate other files via automation with a predefined number of actions.

The difference here in this context is that while Front Loaders have a visualized user interface Loaders don’t have it and therefore Loaders are always used in silent mode.

Using Loaders and Front Loaders are, at least for me, one of the more powerful solutions when it comes to manipulating Excel workbooks. Up to this date I have delivered more then 80 clients solutions based on different kind of Loaders and Front Loaders where the majority still are in use.

The real power is achieved when using Loaders together with Windows Scheduled Tasks tool on a Windows server.

Creating a Loader by using VB.NET 2005

The case is based on a scenario where we schedule a task to be executed every Sunday night so when the co-workers start their work on Mondays they have the latest update when they open the workbooks in Excel.

The two Excel workbooks in use are located in the same folder and they should be populated with the latest data from a central production’s database. None of the workbooks contain any code.

The Loader should do the following:

• Start MS Excel
• Open every workbook in the folder
• Add data to the workbooks
• Save and Close the workbooks
• Close MS Excel
• Report status after the processing above by writing to a log file.

The case does not require any user interface as all execution will be done via automation, i e the scheduled task starts the Loader which in return manipulate the workbooks as described above.

In order to keep the solution flexible ( i e hard code the generic but avoid to hard code the less general information)  the case require a text file which should contain the following information:

• The full pathway to the folder
• The SQL queries

A XML-file may be nice to use here but I still prefer to work with ‘traditional’ text files for two reasons:

• They usually don’t contain a large amount of information and therefore not require a structure as XML-files offer.
• I’m both old fashioned and have not yet achieved all the knowledge to fully work with XML-files.

Below is the information that the text file ‘Loader.txt’ contains:

c:\Test\
SELECT * FROM Orders WHERE EmployeeID=‘3’
SELECT * FROM Orders WHERE EmployeeID=‘5’

Using classic ADO or ADO.NET?
The decision in this case is quite easy to do. As long as the Range’s method CopyFromRecordset only support classic ADO (which is also true for Excel 2007!) and we have a larger amount of data classic ADO is the natural choice.

The Code

The code is placed in two modules:

In the Main module:
Module Main
    Sub Main()
        ‘Declare and instantiate a new instance of the class.
        Dim cl As New clLoader
        ‘Call the main function.
        If cl.Update_Workbooks() Then cl.Write_Log (True)
        cl = Nothing
    End Sub
End Module

In a class module:
 ‘Make sure that references are made to the following namespaces:
‘(And also to NET’s ADODB)
Imports System
Imports System.IO
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

Friend Class clLoader
    ‘Implement the interface.
    Implements IDisposable

    Public Sub New()
        ‘Constructors.
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        ‘Important: The Dispose method should never raise any errors.
        ‘In order to avoid that Dispose is called multiply times.
        Static Disposed As Boolean
        If Not Disposed Then
            Disposed = True
            GC.SuppressFinalize (Me)
        End If
    End Sub

    Protected Overrides Sub Finalize()
        ‘Destructors()
        Dispose()
    End Sub

    Friend Function Update_Workbooks() As Boolean
        ‘Make sure that the connection string works properly and
        ‘that You got a running instance of the SQL Server.
        Const Con As String = “Provider=SQLNCLI.1;” & _
                  “Integrated Security=SSPI;” &
                  “Persist Security Info=False;” & _
                  “Initial Catalog=NorthWind;” & _
                  “Data Source=IBM\SQLExpress”

        Dim cnt As New ADODB.Connection
        ‘By setting the following variable to Nothing we eliminate
        ‘the case where it may occur a null reference excepetion for it.
        Dim rst As ADODB.Recordset = Nothing
        ‘Instantiate the Excel variables.
        Dim xlApp As New Excel.Application
        ‘By setting the following variable to Nothing we eliminate
        ‘the case where it may occur a null reference excepetion for it.
        Dim xlWbook As Excel.Workbook = Nothing
        Dim xlWsheet As Excel.Worksheet
        Dim xlData As Excel.Range
        Dim xlDate As Excel.Range
        Dim xlCounter As Short

        Try
            ‘Grab the path to the control file which is the same as for
            ‘the application.
            Dim LoaderFile As String = Environment.CurrentDirectory _
                                                             & “\Loader.txt”
            ‘Check if the control file exist.
            If File.Exists(LoaderFile) Then
                ‘Instantiate a new reader and read the content of the control file.
                Dim Reader As New IO.StreamReader(LoaderFile)
                ‘Split the text per line and populate the array.
                Dim GetValues() As String =  _
                       Split(Reader.ReadToEnd, vbNewLine)
                ‘Close the reader.
                Reader.Close()
                ‘Retrieve the directory where the target workbooks are stored in.
                Dim Dir As DirectoryInfo =  _
                        New DirectoryInfo(GetValues(0))
                ‘We only interested in the Excel files so we create an array which
                ‘will only contain the filtered files.
                Dim xlFiles() As FileInfo = Dir.GetFiles(“*.xls”)
                ‘Control to make sure that it exist
                ‘at least one Excel file in the directory.
                If UBound(xlFiles) <> -1 Then
                    ‘In order to iterate the collection of Excel files.
                    Dim xlFile As FileInfo
                    ‘Iterate through the collection of Excel files.
                    With cnt
                        .CursorLocation = ADODB.CursorLocationEnum.adUseClient
                        .Open (Con)
                    End With
                    For Each xlFile In xlFiles
                        ‘This counter refer to both the collection of Excel files and
                        ‘to each line in the control file that contain the SQL query.
                        xlCounter = CShort(xlCounter + 1)
                        xlWbook = xlApp.Workbooks.Open(xlFile.FullName)
                        xlWsheet =  _
                        CType(xlWbook.Worksheets(2), Excel.Worksheet)
                        With xlWsheet
                            xlData = .Range(“D3”)
                            xlDate = .Range(“B2”)
                        End With
                        ‘Date stamp by using the predefined short date expression.
                        xlDate.Value = Format(Now, “d”)
                        ‘Get the recordset and ‘dump’ it into the open workbook.
                        rst = cnt.Execute(GetValues(xlCounter))
                        rst.MoveFirst()
                        If rst.RecordCount <> -1 Then
                            With xlData
                                ‘Clear the range from previously data.
                                .CurrentRegion.Delete()
                                ”Dump’ the Recordset.
                                .CopyFromRecordset (rst)
                            End With
                        End If
                        ‘Clean up.
                        rst.Close()
                        rst = Nothing
                        ‘Save and close the active workbook.
                        xlWbook.Close(SaveChanges:=True)
                    Next
                Else
                    Write_Log(False, “No Excel files are located in ” _
                                         & Dir.FullName)
                    Return False
                End If
            End If
            xlApp.Quit()
            Return True

        Catch ex As Exception
            ‘Here is just some examples on error handling.
            If ex.Message Like “*BADINDEX” Then
                Write_Log(False, xlWbook.FullName & _
                      ” The required worksheet does not exist.”)
            Else
                Write_Log(False, ex.Message)
            End If
            GC.Collect()
            GC.WaitForPendingFinalizers()
            xlWbook.Close(SaveChanges:=False)
            Marshal.FinalReleaseComObject (xlWbook)
            xlApp.Quit()
            Marshal.FinalReleaseComObject (xlApp)
            Return False
        Finally
            xlData = Nothing
            xlDate = Nothing
            xlWsheet = Nothing
            xlWbook = Nothing
            xlApp = Nothing
            If CBool (rst.State And _
                            ADODB.ObjectStateEnum.adStateOpen) =  _
                          True Then rst.Close()
            rst = Nothing
            If CBool (cnt.State And _
                           ADODB.ObjectStateEnum.adStateOpen) = _
                           True Then cnt.Close()
            cnt = Nothing
        End Try
    End Function

    Friend Sub Write_Log(ByVal Status As Boolean, _
                         Optional ByVal Source As String = “”)
        ‘Instantiate the writer and create the logfile
        ‘(if it doesn’t already exist) and open it to write to it.
        Dim Writer As StreamWriter = File.AppendText(“Log.txt”)
        With Writer
            If Status Then
                .WriteLine (Now & ” – OK “)
            Else
                .WriteLine (Now & ”   **********Error**********   ” & Source)
            End If
            ‘Update the file.
            .Flush()
            .Close()
        End With
        Writer = Nothing
    End Sub
End Class

In general we should split up the code in smaller procedures but I made one procedure. Constructors, destructors and IDispose are general requirements when it comes to classes in VB.NET. In this case they are included but not fully used. What is important is to be aware of that IDispose and the Finalize methods can only handle so called unmanaged resources.

In addition to the main task, i e update the workbooks, the case also shows how we can handle files and directories.

Using .NET Framework in this scenario should not be a problem as it only requires to be installed on the Windows Server. 

For all code generating I use a nice utility by the name of VS.NETcodePrint 2005

Please feel free to comment it and also suggest improvements for the solution. 

Kind regards,
Dennis

The Transition Case – VBA Part I

The DataTool Add-in

This is the basic utility that will be used to review the transition from VBA, via classic VB and also via VB.NET, to finally end up on the VSTO platform as an Add-in.

The utility does the following:

• Create a customized menu option on the Worksheet Commandbar – DataTool.
• Create a customized toolbar, which status (visible/hidden and position Top/Floating) is written to / read from the Windows registry.
• Retrieve data from a local SQL Server 2005 database – Northwind – based on a stored procedure and with ADO.
• Add a worksheet and populate it with data and do some calculation – Data Report.
• Add a worksheet, populate it with data and create a Chart – Chart Report.
• Add a worksheet, populate it with data and create a Pivot Table – Pivot Report.
• Monitor the following application events:
  NewWorkbok, NewWorksheet, WorkbookOpen
• It includes a helpfile and also an About form.
• All error handling is left out. Please see Bullen’s et al book, “Professional Excel Development”, and in particular chapter 12 “VBA Error Handling” for an excellent discussion about this subject.

To some degree the above functionality reflects how I view Excel as a tool for personal analyze of data and presentation. With projects like this one the process starts at one point and during the developing it undergo several changes. In the end You realize You could do some parts of it total different. That’s why written requirements are always best to work with. 😉

The following images show the Menu option as well as the Toolbar:  

The VBA Project  

For clarity (if possible) I use some more components in the VBA Project and also some more procedures that may be considered as necessary for a utility of this kind.

I prefer to use the Auto_Open and Auto_Close events rather then using the equal events for the object ThisWorkbook.

The options in the DataTool menu are directly hooked to the procedures while the buttons on the toolbar share the same click event.

The following images shows the VBA Project (If You don’t recognize some of the words it’s due to the fact that I use a Swedish version of Excel 2003 to create this add-in with):

The following link is to the zipped file, CaseXLA.zip, which enabling You to download and take part of the solution:  DataTool – VBA add-in

Note 1: The add-in has been updated based on Will Riley’s input. 

The zipped file contains two files, the XLA file and the CHM-file, i e the help file.

Please feel free to comment it and also if You would like to add or remove anything (in view that it’s a case tool only).

Kind regards,
Dennis 

Develop DB Code Generator Wizard with VB.NET?

Last year I started a new project to develop a COM Add-in for Excel, the DB Code Generator Wizard. For some reasons I decided to delete one vmWare setup where, of course, the code project was developed on. At that time I was not aware of that I had no backup for the utility. What is left of it is only some screenshots. Now I wonder if it of interest that it will be re-created or not.

The following links show the screenshots of the DB Code Generator Wizard:

• DB Code’s Main tab
  In this view there are some central settings that need to be configured. When clicking on the ‘Create Procedure’ button a complete VBA function is created which retrieve the data from the database.
• SQL Query’s tab
  In this view new SQL queries can be created and tested and when clicking on the ‘Test’ button a new tab is viewed with the output of the query.
• Parameter’s tab 
  In case of using parameters for the queries these are controled in this view.
• Setting’s tab
  Here are the general databases and QueryTables settings handled.
• Connection’s tab
  Here You can administrative the available connection strings.
• SQL tab
  In this view You can administrative the configured SQL expressions.

In addition to the above it also offer another utility, Code Snipper, which allows developers to store complete procedures as well as snippet code.  Saving and using the code will be available via a toolbar in the VB-editor.

All information is stored in a local Microsoft Database (so called ‘Access’ database). 

It can be used with most of the common databases. However, one idea I has is to implement stored procedures but it will then only target SQL Server 2000 / 2005.

If it will be re-created it will be developed with VB.NET 2005 and distributed as freeware.

The following requirements must be met in order to use it:

• .NET Framework 2.0 or 3.0 (depending on when it’s released).
• Excel versions 2003 or 2007.

Let me know what You think about it from a general view. More specific, if it interesting to take part of the development via the blog or not. Also if something is missing which You would like to see as part of the utility.

Kind regards,
Dennis

Ps. In my next post (next week) I will introduce the case that will be used for creating a utility for Excel from VBA to VSTO.

Using SQL Server .NET Data Provider to connect to SQL Server 2005

There was some comments on my previously post about how to connect to SQL Server 2005. I decided to create a special post about it and I hope that it can give some guidelines when it comes to create DSN-less connection strings.

The SQL Server .NET Data Provider uses Tabular Data Stream (TDS) to communicate with the SQL Server, which actually is the same protocol that SQL Server use as the native protocol. The advantage is that by using this Data Provider the performance increase heavily compared with going via the OLE DB layer or the ODBC layer. The CLR (Common Language Runtime) don’t need to marshal the COM data types to .NET CLR data types each time a SQL Server’s database is accessed.

The drawback is that we can’t build connection strings by using the Data Link Wizard in Excel 2000 – 2003 in the same way as we can do for OLE DB Providers and ODBC drivers. It requires that we know all the information before creating the connection strings. 

In Excel 2007 we can use the Data Connection Wizard which also requires that we have all the required information available before building the connection.

Workaround – Creating a connection string in .NET

An alternative approach to retrieve the connection string is to:

• Create a project in VB.NET.
• Start the wizard for connecting to a database, Tools | Connect to database…
• Complete the required information in the main dialog and test the connection.
• Click on the Advanced… button.
• Copy the connection string in the bottom of the dialog.

Syntax – Local connection

In order to connect to a local running instance of SQL Server 2005 the following syntax can be used:

“Provider=SQLNCLI.1;
“Integrated Security=SSPI;
“Persist Security Info=False;
“Initial Catalog=Name of the database;
“Data Source=Name of the Computer\Name of the running instance of SQL Server”

The following connection string is valid for me when connecting to my local server:

“Provider=SQLNCLI.1;
“Integrated Security=SSPI;
“Persist Security Info=False;
“Initial Catalog=AdventureWorks;
“Data Source=IBM\SQLLocal” 

Syntax – Remote connection 

In order to connect to a remote SQL Server 2005 the following syntax can be used:

“Provider=SQLNCLI.1;
“Persist Security Info=False;
“User ID=User;
“Initial Catalog=Name of the database associated with the User ID;
“Data Source=IP-number”

The following connection string is valid for me when connecting to my remote server:

“Provider=SQLNCLI.1;
“Persist Security Info=False;
“User ID=sa;
“Initial Catalog=AdventureWorks;
“Data Source=xx.xxx.xxx.xx”

The above connection strings does not save the password for any UserID which will require some additional processing. Personally I use a ‘Data Access Component’ (DLL) to do the work.

Although I have not tested it but the above connection strings should also work with the Express Edition of SQL Server 2005. 

Kind regards,
Dennis