Importing text files to Access..to include FN in a field.

[arsenal042004]

I currently use this small bit of code to import text files into a table as batch. I'd like to know if there is a way to also import in a field, the file name. Since I have many files and I need to know where certain records come from.

Thanks in advance for your assistance!
Todd
Private Sub Command0_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

' ChDir ("D:\New")
strfile = Dir("D:\CNew\*.csv")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "EVAL", "EVAL", "D:\CNew\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "D:\CNew\" & strfile
strfile = Dir
Loop

End Sub

 

[Beeps]

Sure! You can get the file name by using a dialog box to pick your file:

Dim dia As Object

Set dia = Application.FileDialog(msoFileDialogFolderPicker)


If dia.Show = 0 Then 'user pressed cancel
Exit Sub
Else

strFileName = dia.SelectedItems(1) 'user selected a filepath

End Sub


Once you have the file name in the strFileName variable, loop through the file and add the rows with ADO:


Sub ImportText(strFileName as String)
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
Dim counter as integer

Dim fileobj As Object




Dim textfile As TextStream
Dim txt As String


Set fileobj = New FileSystemObject
Set textfile = fileobj.OpenTextFile(strFileName)
rs.Open "SELECT * FROM YourTable", CurrentProject.Connection, adOpenStatic, adLockPessimistic

Do Until textfile.AtEndOfStream



'here's where you read the file and move you records to variables
'
'
'


'add the record from your variables to the database table for processing
rs.AddNew
rs!YourField = mydate


rs!FileName = txtFile.Name
rs.UpdateBatch



End If

Loop

'Set txtfile = Nothing
rs.Close
Set rs = Nothing
End Sub

You'll need to modify the above code to suit you situation. I'm not sure if there's a way to accomplish this using the DoCmd.TransferText method.