You are on the Home/Publications & Training/Case Studies/LINQ to find and open a file page
Google
Web This Site

Using LINQ to find and open in Excel 2007 the newest file in a folder

This is one of two approaches inspired by Dick Kuslekia's post on opening in Excel the newest CSV file in a given folder (http://www.dailydoseofexcel.com/archives/2009/05/01/opening-the-newest-file-in-a-folder-with-vba/).

Compared to my other take on this subject, this technique is closer to Dick's approach in that the user controls when the file is opened.  I also wanted to explore LINQ -- Language-Integrate Query -- in a (semi)useful utility.

The user interface, once built, will look like:

Enter the folder name in the edit box and click the LINQ to find latest CSV button.  The latest CSV file, if a CSV file is found in the folder, will open.

The role of LINQ

Let's start with a caveat, an important caveat.  My knowledge of LINQ is very limited.  I think of it as a 'SQL-like' capability that works with any object that supports it -- strictly speaking it works with any object that supports either the IEnumerable or IEnumerable(Of T) interface -- whatever that means.  Collections and arrays support the interface.

For more on LINQ search the msdn.microsoft.com website.  You may want to start with LINQ in Visual Basic (http://msdn.microsoft.com/en-us/library/bb385100.aspx) or How to: Query for Files with a Specified Attribute or Name (http://msdn.microsoft.com/en-us/library/bb546159.aspx).

I also don't know how useful or popular LINQ will be in the long run.  Microsoft has a history of introducing technologies and products and then abandoning them if they do not fit its new goals.  Will LINQ go the same way?  I don't know.  Consequently, I don't know if an investment in this technology will be rewarding or not.

For the most part, the add-in contains code cobbled together from msdn.microsoft.com samples.

In Visual Studio 2008, start by creating a Excel 2007 add-in named LINQFindNewestFile.  This will create a skeleton VB Project that includes the minimal code to connect the add-in to Excel 2007.

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup(ByVal sender As Object, _

            ByVal e As System.EventArgs) Handles Me.Startup

 

        End Sub

 

    Private Sub ThisAddIn_Shutdown(ByVal sender As Object, _

            ByVal e As System.EventArgs) Handles Me.Shutdown

 

        End Sub

 

End Class

 

Eventually, we will build a Ribbon UI interface to the add-in.  So, we can leave the above skeleton alone and work on the core functionality of the add-in.

Using LINQ to query a collection

LINQ lets one create a VB query that operates against an appropriate data source.  In this case, the data source will be a collection.  So, we create a collection, deferring until later the details on how to create the right kind of collection.

        Dim fileList = GetFiles(root)

 

Given fileList, a collection of file information, we can define a query.  The statement below only defines the query.  It does not execute it.

        Dim fileQuery2 = _

            From file In fileList _

            Where file.Extension = ".csv" _

            Order By file.CreationTime _

            Select file.FullName, file.CreationTime

 

The query looks very much like a SQL statement combined with a For each iteration to loop through all of the elements in a collection.  One of the benefits of LINQ is that since everything is contained within the language the objects are all strongly typed.  So, the compiler and the IDE know what properties are valid for each element of fileList.  This also means Intellisense is available once one types file and presses the period key.

Note that the Intellisense capability may not be available yet because GetFiles remains undefined.

Execution of the query yields a list of elements.  One can either iterate through the elements or access just a single element or get only an aggregate result (like Count) -- results quite similar to the SQL experience.  In this case, since we want the last element of the sorted query result, we execute the query and access the last element with

        Dim newestFile = fileQuery2.Last

 

Once we have the newestFile object, we can open the associated file in Excel with the below code.  While many Excel related constants are available in VB.Net through the Excel.Constants collection, xlDelimited is not one of them.

        Const xlDelimited As Int16 = 1

        xlApp.Workbooks.OpenText(Filename:=newestFile.FullName, _

            Origin:=437, StartRow:=1, DataType:=xlDelimited, _

            TextQualifier:=Excel.Constants.xlDoubleQuote, _

            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _

            Comma:=True, Space:=False, Other:=False)

 

I got the OpenText statement by using the macro recorder in Excel and modified the Filename argument.

Generating a LINQ-compatible collection from a file list

Earlier, we deferred the task of how GetFiles returns a collection of files.  It turns out that GetFiles also uses a LINQ query together with the appropriate system functions to do the needful.  FileSystem.Getfiles returns a collection of file names.  So, the LINQ query works with this collection of names of files in the directory specified by the root variable; for each it uses IO.FileInfo to get information about the file.  Finally, the Return statement returns the LINQ query collection as the function value.

    Function GetFiles(ByVal root As String) _

            As System.Collections.Generic.IEnumerable(Of System.IO.FileInfo)

        Return _

            From file In My.Computer.FileSystem.GetFiles _

                (root, FileIO.SearchOption.SearchAllSubDirectories, _

                    "*.*") _

            Select New System.IO.FileInfo(file)

        End Function

 

That's it.  We are done.  While I cannot comment on the quality of the code generated by VB and LINQ, there is indeed a simplicity to the LINQ approach.  Not only did I did not have to loop through files keeping track of the newest file but the variables resulting from the query were strongly typed.

Put all of the above code together to get in the ThisAddIn.vb file:

Module FindFileByExtension

    Sub Main(ByVal root As String, ByVal xlApp As Excel.Application)

        'Take a snapshot of the folder contents

        Dim fileList = GetFiles(root)

        'Define the query

        Dim fileQuery2 = _

            From file In fileList _

            Where file.Extension = ".csv" _

            Order By file.CreationTime _

            Select file.FullName, file.CreationTime

        'Execute the query

        Dim newestFile = fileQuery2.Last

 

        Const xlDelimited As Int16 = 1

        xlApp.Workbooks.OpenText(Filename:=newestFile.FullName, _

            Origin:=437, StartRow:=1, DataType:=xlDelimited, _

            TextQualifier:=Excel.Constants.xlDoubleQuote, _

            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _

            Comma:=True, Space:=False, Other:=False)

        End Sub

 

    ' Function to retrieve a list of files. Note that this is a copy

    ' of the file information.

    Function GetFiles(ByVal root As String) _

            As System.Collections.Generic.IEnumerable(Of System.IO.FileInfo)

        Return _

            From file In My.Computer.FileSystem.GetFiles _

                (root, FileIO.SearchOption.SearchAllSubDirectories, _

                    "*.*") _

            Select New System.IO.FileInfo(file)

        End Function

End Module

Adding a Ribbon to the Excel 2007 add-in

With the LINQ portion out of the way, we can turn our attention to creating a UI to our add-in.

While I know a lot more about the Office 2007 Ribbon than I know of LINQ, I am not an expert on the sophisticated deployment of the Ribbon in an Excel 2007 .Net add-in.  Below is the minimal code needed to implement a basic ribbon UI.

In the VB.Net project, add a new ribbon item using the Ribbon (XML) template.  By default, this adds two modules to the project, Ribbon1.xml and Ribbon1.vb.

Modify the Ribbon1.xml file to add an edit box and a button

<?xml version="1.0" encoding="UTF-8"?>

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"

    onLoad="Ribbon_Load">

  <ribbon>

    <tabs>

      <tab id="TM" label="TM.Net">

        <group id="MyGroup"

               label="My Group">

          <editBox id="FolderName" label="Enter folder name"

              onChange="EditBoxChange"/>

          <button id="MyButton" label="LINQ to find latest CSV"

              onAction ="ButtonClicked"/>

        </group>

      </tab>

    </tabs>

  </ribbon>

</customUI>

 

The Ribbon1.vb module should look like

<Runtime.InteropServices.ComVisible(True)> _

    Public Class Ribbon1

    Implements Office.IRibbonExtensibility

 

    Private ribbon As Office.IRibbonUI, FolderName As String

 

    Public Sub New()

    End Sub

 

    Public Function GetCustomUI(ByVal ribbonID As String) As String _

            Implements Office.IRibbonExtensibility.GetCustomUI

        Return GetResourceText("LINQFindNewestFile.Ribbon1.xml")

        End Function

 

#Region "Ribbon Callbacks"

    'Create callback methods here. For more information about adding _

    'callback methods, select the Ribbon XML item in Solution Explorer _

    'and then press F1.

    Public Sub Ribbon_Load(ByVal ribbonUI As Office.IRibbonUI)

        Me.ribbon = ribbonUI

    End Sub

 

    Public Sub ButtonClicked(ByVal control As Office.IRibbonControl)

        Main(FolderName, Globals.ThisAddIn.Application)

        End Sub

    Public Sub EditBoxChange(ByVal control As Office.IRibbonControl, _

            ByVal text As String)

        FolderName = text

        End Sub

#End Region

 

#Region "Helpers"

 

    Private Shared Function GetResourceText(ByVal resourceName As String) _

            As String

        Dim asm As Reflection.Assembly = _

            Reflection.Assembly.GetExecutingAssembly()

        Dim resourceNames() As String = asm.GetManifestResourceNames()

        For i As Integer = 0 To resourceNames.Length - 1

            If String.Compare(resourceName, resourceNames(i), _

                    StringComparison.OrdinalIgnoreCase) = 0 Then

                Using resourceReader As IO.StreamReader = _

                        New IO.StreamReader( _

                            asm.GetManifestResourceStream(resourceNames(i)))

                    If resourceReader IsNot Nothing Then

                        Return resourceReader.ReadToEnd()

                    End If

                End Using

            End If

        Next

        Return Nothing

    End Function

 

#End Region

End Class

 

The ThisAddIn class needs a tweak.  The ThisAddIn.vb file should look like:

Public Class ThisAddIn

    Protected Overrides Function CreateRibbonExtensibilityObject() _

            As Microsoft.Office.Core.IRibbonExtensibility

        Return New Ribbon1()

        End Function

    Private Sub ThisAddIn_Startup(ByVal sender As Object, _

            ByVal e As System.EventArgs) Handles Me.Startup

        End Sub

 

    Private Sub ThisAddIn_Shutdown(ByVal sender As Object, _

            ByVal e As System.EventArgs) Handles Me.Shutdown

 

        End Sub

 

End Class

 

That's it.  Build the add-in and use it in Excel 2007.

Summary

This article introduces the use of LINQ and the Office Ribbon in an Excel 2007 add-in.  By design it barely scratches the surface leaving further exploration of the technologies to the reader.