A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Appending Excel data into an Access table while inserting the file name



 
 
Thread Tools Display Modes
  #1  
Old December 17th, 2009, 05:36 PM posted to microsoft.public.access.tablesdbdesign
Greg Mathes
external usenet poster
 
Posts: 1
Default Appending Excel data into an Access table while inserting the file name

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
Ads
  #2  
Old December 17th, 2009, 05:52 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Appending Excel data into an Access table while inserting the file name

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 ...
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



  #3  
Old December 18th, 2009, 06:26 PM posted to microsoft.public.access.tablesdbdesign
Ken Snell
external usenet poster
 
Posts: 177
Default Appending Excel data into an Access table while inserting the file name

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 ...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.