Scripting Scripture


Scribe 
 The Microsoft Scripting Runtime Library (scrrun.dll) exposes a couple of objects that we can exploit to increase the power and functionality of VBA.

    Dictionary
    Drive
    FileSystemObject
    TextStream

For today, I’ll look at the FileSystemObject. I’ll take a look at the other objects in future posts.
 
edit: I am going to use Late Binding in the sample snippets below. A discussion on Late / Early Binding is beyond the scope of this post. Please see the “Additional Resources” at bottom for links to detailed explanation of Late / Early Binding.
 
I would like a list of all files in a folder, C:\Data. You may use the FileSystemObject to loop through folders and files. In an Excel file, open the Visual Basic Editor, add a module and paste or enter this code:

Option Explicit
Sub Foo()

    'Author: Winston Snyder
    'Date: 11/26/2013
    'Purpose: Demonstrate looping through files in a folder using the FileSystemObject
    'Comment: Uses Late Binding
    '--------------------------------------

    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim FSO As Object
        Dim fsoFolder As Object
        Dim fsoFile As Object
        Dim strPath As String
        Dim i As Long
        
    'Excel environment - speed things up
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
        
    'Initialize variables
        strPath = "C:\Data\"
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets.Add(After:=Worksheets(Worksheets.Count))
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set fsoFolder = FSO.GetFolder("C:\Data\")
        i = 2

    'List files in folder
        For Each fsoFile In fsoFolder.Files
            ws.Cells(i, 1).Value = fsoFile.Name
            i = i + 1
        Next fsoFile
    
    'Add Header
        ws.Cells(1, 1).Value = "FileName"
    
    'Tidy up
        'Destroy objects
            Set fsoFolder = Nothing
            Set FSO = Nothing
            Set ws = Nothing
            Set wb = Nothing
        
        'Restore Excel environment
            With Application
                .ScreenUpdating = True
                .DisplayAlerts = True
                .EnableEvents = True
                .Calculation = xlCalculationAutomatic
            End With
End Sub

Ole P. Erlandsen has some nice examples on his site using FileSystemObject to loop through Folders and Subfolders to get all kinds of file information.

A Business Case

OK, we looked at some fairly simple code to use the FileSystemObject (FSO) to look at how to get a list of files in a folder and some of each file’s properties. But how can we use FSO in a business case? Again, I’ll be using Late Binding. Additionally, I’ll be using the DoCmd Object of the Microsoft Access Object Model as well as some VBA functions to look at the file names and manipulate string variables.

My goal is to examine each file in a specified location, if the file meets my criteria, I want to manipulate the name of the file into a table name that I can transfer to MS Access. The code below will append “dim” to the beginning of each file name as well as remove the extension from the file name. I am using “dim” in this case because this is code I use to load dimension (dim) tables to MSAccess and later import into PowerPivot. Without further ado:

Option Compare Database

Public Sub ImportLoadFiles()

    'Author: Winston Snyder
    'Date: 11/27/2013
    'Purpose: Load Excel files to Access database tables
    'Comment: Paste the code into a standard module in the VBE in MS Access
    '----------------------------------------------------
    
    'Declare variables
        Dim strPath As String
        Dim strTableName As String
        Dim FSO As Object
        Dim fsoFolder As Object
        Dim fsoFile As Object
        Dim strFileName As String
        
    'Initialize variables
        strPath = DocsPath & "Load Files\"
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set fsoFolder = FSO.GetFolder(strPath)
        
    'Transfer all Excel files that meet criteria to Access database tables
        With DoCmd
                    
            'Turn off warnings
                .SetWarnings False
                
            'Loop and transfer files to database
                For Each fsoFile In fsoFolder.Files
                    
                    'If Excel file, create a name for  dimension (dim) table
                        If InStr(fsoFile.Name, ".xlsx") Then
                            strFileName = Left(fsoFile.Name, Len(fsoFile.Name) - 5)
                            strFileName = "dim" & strFileName
                            
                            'Transfer the spreadsheet to MS Access table
                                .TransferSpreadsheet TransferType:=acImport, _
                                                     SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
                                                     TableName:=strFileName, _
                                                     FileName:=strPath & fsoFile.Name, _
                                                     HasFieldNames:=True
                        End If
                Next fsoFile
                
            'Turn on warnings
                .SetWarnings True
        End With
        
    'Tidy up
        'Destroy objects
            Set fsoFolder = Nothing
            Set FSO = Nothing
End Sub

Note the function call on this line

strPath = DocsPath & "Load Files\"

DocsPath is a function, so we’ll need to create a function to return the USERPROFILE of the host machine which we can then use.

Public Function DocsPath() As String
    
    'Purpose: Get the Environ value for User Docuents
    'Returns: C:\Users\%User Name%\Documents\
    
    DocsPath = Environ$("USERPROFILE") + "\Documents\"
End Function

Also, please note, for simplicity I assume the folder, “Load Files” exists in the “ImportLoadFiles” snippet above. If it does not, the code will generate an error. You can make the code more robust by adding some error handling to check if folders exist and if the folder contains files that you are interested in.

An Example

ExcelToAccess

Create Load File

I need to create a load file and I have a process to do that, but I will not go too far into it in this post – I’ll review the process in a future post. For now, two things are important.

  1. The name of the file must be similar to the name of the target table
  2. The column headers in the load file must match the field names in the target table
File Name

The code above takes care of the file to table naming. Recall – the code trims the file extension and appends “dim” to the front of the file name. Therefore, the filename, “Teams.xlsx” is translated to “dimTeams” which is the name of the target table. You just need to plan your load file names to align in some fashion with the name of the target table in the Access database.

Create Access Table

Field (Column) Names

ColumnFieldName

  1. Add a table to your database
  2. Save the table as “dim” and some descriptive name such as “dimTeam”
  3. Name the first field of the table with a descriptive term plus the suffix “Key”. For example, “TeamKey”
  4. Add another field. Give it a descriptive name that describes what the field will contain such as “TeamName”.
  5. Set the Data Type to the appropriate type such as “Short Text”.
  6. While still selected on the second field, click on “Indexed” in the Field Properties pane. Click on the drop-down, change from the default value of “No” to “Yes (No Duplicates)”.
  7. Save and close the table
  8. Run the “ImportLoadFiles” process to load the table from the Excel file.

In my sample of NFL teams, I loaded 32 records in my first pass as expected. I then deleted 4 records from the table, and deleted all but the same 4 teams plus one extra from the load file. I saved the load file and ran the import process. The 4 missing records were imported as expected. the 5th record was already in the table so it was disregarded by Access.

Additional Resources

Tidy up

    Final Thoughts

    The one load file, one Access table, 32 records is a silly example. But what if you have 10 tables…20…50? Now, hopefully, you see the true value. How do you use FileSystemObject (FSO) in your Excel / Access projects? Let us know in the comments section.

    Downloads

    Download the Excel file and Access database from SkyDrive.

, , ,
Trackback

2 comments untill now

  1. […] FileSystemObject (FSO) […]

  2. […] which is a top level object in Microsoft Scripting Runtime Library (scrrun.dll). I covered FSO previously as part of my series on Microsoft Scripting in […]

Add your comment now