Appending Excel data into an Access table while inserting the file name
in [Tables]
|
Prev: ACCESS version migrating from 2003 to 2007
Next: Looking for a template for all the websites I manage
From: Greg on 17 Dec 2009 12:36 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/aspnet/fae8f621-8937-44c6-8f9e-9eef3f2bbad1/quick-and-easy-sha1-hash.aspx
From: Jeff Boyce on 17 Dec 2009 12:52 Greg I suspect that you don't need/want to try to assign the Default Value. Instead, just assign the value to the field. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. <Greg Mathes> wrote in message news:20091217123648gmathes(a)pacific.edu... >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/aspnet/fae8f621-8937-44c6-8f9e-9eef3f2bbad1/quick-and-easy-sha1-hash.aspx
From: Ken Snell on 18 Dec 2009 13:26
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:20091217123648gmathes(a)pacific.edu... >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/aspnet/fae8f621-8937-44c6-8f9e-9eef3f2bbad1/quick-and-easy-sha1-hash.aspx |