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.
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.
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.
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
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.
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.
