Import all files in a directory

[eao]

I have 307 files that I need to import. The files are identical schematically, and they will all be imported to the same table. How do I automate this process?

 

[RickSpr]

I'm assuming these are text files.

1. Isolate the files from any with the same extension that you don't want to import. This is so you can use the Dir() function with a wildcard spec *.xxx (where xxx is the file extension).

2. Link one of them using the Text Import Wizard. In the wizard, set up all the field specifications carefully. Name the linked table MyLinkedFile.

3. Build an Append query over MyLinkedFile that will append its rows to your target table. Save it as MyImportQuery.

4. If necessary, add a reference to the DAO library.

5. Paste the following code into a standard module:

Public Sub ImportFiles()
    Const Folder = "[red]C:\My Documents[/red]"  ' Path to your files
    Const Extension = "[red]xxx[/red]"  ' Your file extension
    Const FileSpec = Folder & "\*." & Extension
    Dim strFilePath As String, strFile As String
    Dim db As DAO.Database, tdf As DAO.TableDef, qdf As DAO.QueryDef
    Dim fn As Integer

    On Error GoTo ErrorHandler
    Set db = CurrentDb()
    strFilePath = Dir(FileSpec)
    Do While Len(strFilePath) > 0
        strFile = Mid$(strFilePath, Len(Folder) + 2)
        fn = fn + 1
        SysCmd acSysCmdSetStatus, "Processing file #" & fn
        Set tdf = db.TableDefs("MyLinkedFile")
        tdf.Connect = "Text;DATABASE=" & Folder & ";TABLE=" & strFile
        tdf.RefreshLink
        Set tdf = Nothing
        Set qdf = db.QueryDefs("MyImportQuery")
        qdf.Execute
        Set qdf = Nothing
        strFilePath = Dir()
    Loop
    MsgBox "Import complete"
ErrorExit:
    SysCmd acSysCmdClearStatus
    Set qdf = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Runtime error (" & Err.Number & "):" & vbCr _
        & Err.Description, vbExclamation
    Resume ErrorExit
End Sub

6. Back up your database, of course.

7. Put the cursor in the ImportFiles procedure and press F5.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[eao]

I should have mentioned that these are Excel files. Is this the only line I would have to change, and if so, what do I change it to?

tdf.Connect = "Text;DATABASE=" & Folder & ";TABLE=" & strFile

 

[eao]

How about something like this?

tdf.Connect = "EXCEL 8.0; Database=C:\My Documents\Book1.xls"

 

[eao]

4. If necessary, add a reference to the DAO library.

This seems necessary as I receive a 'User defined type not defined' error here:

Dim db As DAO.Database

How do I add the reference to the DAO library? Do I add the reference to this method?

 

[scottian]

heres a simpler way of doing it, less code

you may need to alter the transfer.text to transfer spreadsheet.

=====================================
Function importfiles()

Dim FileName As String
FileName = ""
FileName = Dir("Path to the folder where the files are stored" & "*extension of the files i.e .xls")
If FileName <> "" Then
Do Until FileName = ""
DoCmd.TransferText acImportDelim, "1 Spec", "table1", " Path to the folder where the files are stored " & FileName, False, ""
End If
FileName = Dir()

End Function

"My God! It's full of stars...

 

[eao]

Function importfiles()

Dim FileName As String
    FileName = ""
        FileName = Dir("Path to the folder where the files are stored" & "*extension of the files i.e .xls")
            If FileName <> "" Then
                Do Until FileName = ""
                DoCmd.TransferText acImportDelim, "1 Spec", "table1", " Path to the folder where the files are stored " & FileName, False, ""
            End If
    FileName = Dir()

End Function

This returns the error: End If without block If

 

[eao]

I got this to work successfully. I had to change this:

tdf.Connect = "Text;DATABASE=" & Folder & ";TABLE=" & strFile

To this:

tdf.Connect = "EXCEL 8.0;DATABASE=" & Folder & "\" & strFilePath