ACC: How to Automate Importing All Files in a Specific Folder

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article explains how you can automate importing all files of a particular type from a specific folder on your hard drive.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

The following example runs a procedure from a command button on a form. The procedure uses an input box to prompt for the folder containing the files you want to import. Then the code loops through all the files in the folder you specify and imports them all.

NOTE: The following example imports Microsoft FoxPro version 3.0 files. You select the file type you want to import by changing the second argument of the TransferDatabase method. If you want to see a list of file types that you can import using TransferDatabase, create a new macro and insert the TransferDatabase action. Then view the list of file types under the Database Type argument. When you are finished, close the macro without saving it.

1. Create a new form not based on any table or query in Design view.

2. Add a command button to the form and set its OnClick property to the following event procedure:

         Private Sub Command0_Click()
         Dim InputDir, ImportFile As String, tblName As String
         Dim InputMsg as String
   

         InputMsg = "Type the pathname of the folder that contains "
         InputMsg = InputMsg & "the files you want to import."
         InputDir = InputBox(InputMsg)
         ' Change the file extension on the next line for the
         ' type of file you want to import.
         ImportFile = Dir(InputDir & "\*.dbf")
   

         Do While Len(ImportFile) > 0
            ' Use the import file name without its extension as the table
            ' name.
            tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))
            ' Change FoxPro 3.0 on the next line to the type of file you
            ' want to import.
            DoCmd.TransferDatabase acImport, "FoxPro 3.0", InputDir, _
                  acTable, ImportFile, tblName
            ImportFile = Dir
         Loop
         End Sub
   
   

3. Open the form in Form view and click the button to begin importing tables.

REFERENCES

For more information about the TransferDatabase method, search the Help Index for "TransferDatabase Method," or ask the Microsoft Access 97 Office Assistant.

For an alternative method to import files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q88764
   TITLE     : ACC: How to Import several dBASE Databases at Once (1.x/2.0)

   ARTICLE-ID: Q141611
   TITLE     : ACC: How to Import several dBASE Databases at Once 95/97