Add .csv File Name When Importing Multiple Files Into a Table


Joined
Oct 3, 2011
Messages
2
Reaction score
0
Good Afternoon. I am hoping someone might be able to assist me in completing the VBA code I have stored in a Module in Access.

I am using Windows XP and Access 2003.

The code I am using is listed below. The import function works flawlessly and I can highlight as many files as I wish and it adds it all to one table. The code highlighted in blue below is the code that should run an update query to add the .csv file name to table as well. But it is not working. I have tried doing a single file import and multiple file import and neither will load the file name.

Any ideas?

_______________________________________________________________________

Option Compare Database

Function ImportARData()
Dim fDialog As Office.FileDialog
Dim varFile As Variant

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog

' Set .AllowMultiSelect to False to only import one file at a time or True to do Multiple.
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Choose Files to Import"

' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Text Files", "*.csv"

If .Show = True Then
For Each varFile In .SelectedItems

'DoCmd.TransferText acImportDelim, "YOUR IMPORT SPECS HERE", "TABLE YOU WANT THEM ADDED TO", varFile
DoCmd.TransferText acImportDelim, "AROPSImport", "tbl_AROPSImport", varFile

'START Code to add file name to table
CurrentDb.Execute "UPDATE tbl_AROPSImport SET FileName = '" & _
strfile & "' WHERE FileName IS NULL", dbFailOnError

'END Code to add file name to table

Next
Else
MsgBox "You have cancelled the import."
End If
End With
End Function
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top