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