VSTO & .NET & Excel

August 9, 2010

Working with Folders

Filed under: .NET & Excel, UI Design, VSTO & Excel — Dennis M Wallentin @ 10:48 pm

Introduction

It’s quite often that we need to work with folders in various Excel solutions. It involves creating, deleting, moving, copying and update folders with or without any user interactions.

In this article I will discuss and show the code in order to work with folders. In the first part I will cover the basic processes to handle folders and in the second part of the article I will discuss folders and some user interactions.

Before we start up we should make sure that we have a reference to the System.IO namespace in the project and that we have made an import statement at the top of the class module. We will be using classes and methods that reside in that namespace.

Basic work with Folders
In the first example we will create a new folder named “Code” within a user’s folder system. That is done as the following code snippet shows:

Const NewFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Not Directory.Exists(NewFolder) Then
'If it not already exist we create the folder.
Directory.CreateDirectory(NewFolder)
End If

To delete an empty folder is also quite easy to do which the below code snippet also shows:

Const DeleteFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Directory.Exists(DeleteFolder) Then
'If it exist we delete the folder.
Directory.Delete(DeleteFolder)
End If

But what if we want to delete folders that contain files? The above code will throw an exception if the folder is not empty. By setting the second argument explicit to “True” in the Delete method a recursive deleting of all files is executed. That includes also all the subfolders in the targeting folder. The following code snippet shows it:

Const DeleteFolder As String = "c:\Users\Dennis Wallentin\Document\Code"

If Directory.Exists(DeleteFolder) Then
'If it exist we delete all the files and the folder.
Directory.Delete(DeleteFolder, True)
End If

To move files and subfolders from one location to another is also an easy task to achieve. What should be noted is that the old folder is always deleted and that the new folder cannot exist when the code is being executed. If the folder already exists the code will throw an exception. The code snippet below shows the details.

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Code"
Const Target As String = "c:\Test\Dennis"

If Directory.Exists(Folder) Then
'The new folder cannot exist.
If Not Directory.Exists(Target) Then
'Move file and subdirectories to
'the new created folder and delete
'the old folder.
Directory.Move(Folder, Target)
End If
End If

To copy a folder’s content to a new location requires another strategy then what we so far have been used. Instead we must use the method My.Computer.FileSystem.CopyDirectory which also the following code shows. The last parameter controls if any existing files should be overwritten or not.

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Dennis"
Const Target As String = "c:\Test\Backup"

If Directory.Exists(Folder) Then
'If the destination folder does not exist it
'will automatically be created.
My.Computer.FileSystem.CopyDirectory(sourceDirectoryName:=Folder,
destinationDirectoryName:=Target, overwrite:=True)
End If

The CopyDirectory method has two additional parameters that we can use whereof one can be rather useful here. If we know that we will be handling large files or a great number of files that will take some time to copy then we can let the users know about it. To do so we add the parameter ShowUI to the code as the below code also shows.

'On top of the CLass module
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
'...

Const Folder As String = "c:\Users\Dennis Wallentin\Document\Dennis"
Const Target As String = "c:\Test\Backup"

If Directory.Exists(Folder) Then
'If the destination folder does not exist it
'will automatically be created.
My.Computer.FileSystem.CopyDirectory(sourceDirectoryName:=Folder,
destinationDirectoryName:=Target, showUI:=UIOption.AllDialogs)
End If

When executing the code the following Progress Dialog in Windows 7 is showed (the option UIOption.AllDialog makes it possible):


And as we also can see from the screen shot the user can cancel the operation if necessary. Since the other methods of My.Computer.FileSystem are focused on single items they are of little interest in this context.

If You are used to use the SHFileOperation Function then it has been replaced with the IFileOperation Interface. For more information about the later please see IFileOperation Interface.

Some words about Special Folders
It’s beyond the scope for this article to discuss SpecialFolders but I would like to show a basic example on how we can work with Special Folders.

In the following example we will retrieve the path to My Documents folder for the current user:

Dim MyDocuments As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

MessageBox.Show(MyDocuments.ToString)

As we can see, to get access to the special folders we use the Environment.SpecialFolder.MyDocuments to access to MyDocuments Special Folder. When executing the above code we get the following information:


Folders & User Interactions
The UI design for the two cases, with some user interactions involved, is showed in the below screen shot.

In the first case the user click on a button in order to populate the ListBox control with names of Excel files. To get the file names from the pre-decided location we use the following code in the Click event for the button:

Private Sub btnFixedFolder_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles btnFixedFolder.Click

Const Folder As String = "C:\Users\Dennis Wallentin\Documents\Fixed Folder"

Dim dirInfo As New DirectoryInfo(Folder)
Dim ArrayFiles As FileInfo() = dirInfo.GetFiles("*.xlsx")

Me.lbFixedFolder.DataSource = ArrayFiles
End Sub

When executing the code the outcome is what the below screen shot shows:

If we need to iterate through the collection of files in the array it’s also quite easy to do. The following code shows an example where we extract the files names and add them to the ListBox:

For Each item In ArrayFiles
strBuilder = New StringBuilder(item.Name)
strBuilder.Replace(".xlsx", String.Empty)
Me.lbFixedFolder.Items.Add(strBuilder)
Next

In the next case the users must select a source folder. To let the users choose folder we add the FolderBrowserDialog control to the project. The following code makes the Folder Browser Dialog available to the users and populates the ListBox control with the Excel files from the selected folder:

Private Sub btnUserFolder_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) _
Handles btnUserFolder.Click

Const Title As String = "Select a folder for the documents:"
Me.FolderBrowserDialog1.Description = Title

If Me.FolderBrowserDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

Dim Folder As String = Me.FolderBrowserDialog1.SelectedPath
Dim dirInfo As New DirectoryInfo(Folder)
Dim ArrayFiles As FileInfo() = dirInfo.GetFiles("*.xlsx")

Me.lbUserFolder.DataSource = ArrayFiles

End If

End Sub

When the code is executed the Folder Browser Dialog is launched as the following screen shot also shows:

After the selection of a source folder the Listbox gets populated as the following and final screen shot also shows:

I hope that the above has given some insight on how to work with folders in the VB.NET language.

Kind regards,
Dennis

Advertisements

4 Comments »

  1. Awesome article, Dennis, as always. It couldn’t be clearer and you make it look very easy. 🙂

    Comment by Mike Rosenblum — August 11, 2010 @ 1:04 am

    • Hey Mike,

      Thanks for Your kind words. It always nice to get feedback so it’s highly appreciated that You take the time to do so.

      Take care min vän,
      Dennis

      Comment by Dennis Wallentin — August 11, 2010 @ 1:38 am

  2. Useful piece! I noted that you used Microsoft.VisualBasic.FileIO, I have to look into that – how is it different from System.IO?
    Otherwise, I found interesting that VB.NET lets you could freely use the string “c:\Test\Backup”. That’s pretty cool, in C# you have to use a “string literal” to make that work, @”c:\Test\Backup”, because the language doesn’t like the \ symbol.
    Cheers,
    Mathias

    Comment by Mathias — August 12, 2010 @ 6:21 pm

  3. Mathias,

    We always learn something new from each other whenever we make some blog entries. OK, VB.NET has some advantages compared with C#. But C# has it as well. In the long run C# may be the better option of them two. For me C# is a more “plumbing” language than what VB.NET is.

    Anyway, Microsoft.VisualBasic.FileIO namespace supports the My File system by providing types for it which System.IO does not.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — August 12, 2010 @ 8:51 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: