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
|
