How to Import All Files within a Folder to Your Access Table

Data, data, data – without it a database would be pretty useless. So it’s no surprise that one of the most frequently asked questions when it comes to getting that data into your Access table(s) is – how can I make my life easier when I have to regularly import lots (and lots!) of files? As it’s such a common task, there’s obviously a number of ways to skin that particular cat, but here we’re going to look at just one – in my opinion the easiest – especially if you’re doing this on a regular, or even scheduled timeframe. A little planning (and I do mean a little!) and a little VBA magic and you’ll be good to go – read on for to get started…

Importing all Excel files in a folder

For this article, we’re going to look at importing a number of Excel files within a given folder, but you could just as easily import csv files etc – we’ll discuss how to change the code to handle csv files shortly.

Firstly, let’s define what we want the code to do:

“We want to import all files from a given folder into an existing table – we also want to specify whether the files have a header line or not”

Seems simple enough, so let’s look at the code to do this:

Sub ImportfromPath(path As String, intoTable As String, hasHeader As Boolean)

Dim fileName As String

'Loop through the folder & import each file
fileName = Dir(path & "\*.xls")
While fileName <> ""
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, intoTable, path & fileName, hasHeader
   'check whether there are any more files to import
    fileName = Dir()
Wend

End Sub

Checking for problems…

Before we get into the detail about how this code works, it’s important to note that there is no error checking included in the code above – this is deliberate so that the code is kept easily readable but I’d strongly suggest you add error handling code to your database so you keep the chances of Access database corruption to a minimum!

OK – first things first. To keep the code as usable in different circumstances as possible we’re asking for the path name (where the files are stored), the name of the table you want to import these files into, and whether the files in this folder all have a header or not (i.e. do the field names appear on the first row of the spreadsheet).

You’ll notice that the part of code that searches for files – Dir(path & “\*.xls”) – appends a wildcard and file extension to the supplied path. I mention this as it would be very easy to alter the code if, for example, you wanted to only import files that matched a particular naming convention (e.g. all files that have “January”, or “2017” as part of the file name). You could, if you wanted to, even add that filter as a parameter to the subroutine call.

Anyway, if a file matching the given criteria (any “.xls” file in the folder using the in this case), it immediately attempts to import that file using the TransferSpreadsheet command.

Where to change the code to suit your needs…

It is at this point that you could alter the code so that instead of importing a spreadsheet, it imports csv files (using the “TransferText” command) etc

The code then loops until there are no further files to import in that folder.

It’s a small piece of code, but one that you’ll find yourself using over and over again I’m sure!

Author Introduction:

Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL corruption and excel recovery software products. For more information visit www.datanumen.com