Import multiple txt files and filename?

[mbc321]

Hi,

I have a question regarding import of multiple Text files.

These files all have different names but will always be found in the same location.

C:\Documents and Settings\computer\Desktop\F FILES

I have little VBA knowledge and am wondering if I can import ALL these files into an existing Access table tbl_Table via a macro?

A number of problems:

1. It does not import directly into the existing table, instead it creates a new table tbl_Table , I'd like to import directly into the existing table without using append or similar.

2. I cannot get it to import ALL files in the folder.

3. I'd like to import the first 7 characters of the filename into a field (KEY).

I've already read the Knowledgebase and found the Batch Import, but this does not help as you have to manually enter the name of each file and I have over 3,700 individual txt files.

I found code but I want to work with a text file.

Private Sub cmdImport_Click()
On Error GoTo ErrHandler
 
Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFolderPath As String
Dim SQL As String
Dim i As Integer
 
sFolderPath = "C:\My Documents\"
 
Set oFSystem = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSystem.GetFolder(sFolderPath)
 
For Each oFile In oFolder.files
If Right(oFile.Name, 4) = ".dbf" Then
SQL = "Insert into [tblFORMGUIDE]" _
& " Select """ & Left(oFile.Name, 7) & """ as [Key],*" _
& " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
& " IN """ & sFolderPath & """ ""dBASE 5.0;"""
 
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
i = i + 1
End If
Next
 
MsgBox i & " dbf files were imported."
Exit Sub
 
ErrHandler:
MsgBox Err.Description
End Sub
 
 

Any help or examples would be greatly appreciated.



Thanks.

[twinnyfo]

mbc321,

The code below should get you started, but your real challenge is going to be transferring the KEY to the table you want to import to.

It might be possible to keep that field blank during the import, then create a recordset which queries the table, retrieving only those records that have a blank KEY, then updating that field for every record. I am not comfortable that would be the best way, but it might work.

Option Compare Database
Option Explicit
 
Private Sub ImportTextFiles()
On Error GoTo EH:
    Dim strFileName As String
    Dim strKey
    strFileName = Dir(DirectoryName)
    Do While strFileName <> ""
        strKey = Left(strFileName,7)
        DoCmd.TransferText acImportDelim, , tblTableName, DirectoryName & "/" & strFileName, False
        strFileName = Dir()
    Loop
    'Here is where you could create your recordset to update KEY
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub