I have a loop in an Access 2003 database that appends records from multiple Excel files within a given directory. The name of each Excel file cycles through one variable (ImpFileName) as its records are imported, but I also need to add the Excel file name to each record. I wanted to assign the value of ImpFileName to the DefaultValue property of the FileName field in the table as part of the DoWhile loop, but something is wrong with my syntax.

My code is below, but only works if I comment out the line that assigns the DefaultValue property. Any help would be much appreciated!

Dim tdfImportedFiles As TableDef
Dim ImpFileName As String

Set tdfImportedFiles = CurrentDb.TableDefs!ImportedFiles

ImpPath = "C:\Temp\Course Evaluations\Working\Source\"
ImpFileName = Dir("C:\Temp\Course Evaluations\Working\Source\*.xls")
' sets a dynamic variable that scrolls through the list of Excel source files

Do While Len(ImpFileName) 0
' cycle through the list until the list is empty

'tdfImportedFiles.Fields!FileName.DefaultValue = "ImpFileName"
'set the DefaultValue property for the FileName field within the ImportedFiles table to the current ImpFileName

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "ImportedFiles", "C:\Temp\Course Evaluations\Working\Source\" & ImpFileName, True
'imports all populated rows from the spreadsheet into the ImportedFiles Table

Kill "C:\Temp\Course Evaluations\Working\Source\" & ImpFileName
'deletes the source spreadsheet once it's been imported

ImpFileName = Dir
'advances to the next file in the list

Loop


Submitted via EggHeadCafe - Software Developer Portal of Choice
Quick and easy SHA1 Hash of a String
http://www.eggheadcafe.com/tutorials...sha1-hash.aspx