I'd run an append query after each import to put the value into the
ExcelFile field for the just-imported file's records:
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
'update FileName field with just-imported filename, where the FileName
field contains a NULl value (which will be true for the just-imported
records only)
CurrentDB.Execute "UPDATE ImportedFiles SET FileName = '" & ImpFileName &
"' WHERE FileName IS NULL"
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
--
Ken Snell
http://www.accessmvp.com/KDSnell/
<Greg Mathes> wrote in message news:(E-Mail Removed)...
>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
Similar Threads
